eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_TTY_GEN_TERR_PVT dependencies on JTF_TTY_TERR_GRP_ROLES

Line 999: , jtf_tty_terr_grp_roles tgr

995: SELECT ra.access_type
996: FROM
997: JTF_TTY_GEO_TERR_RSC grsc
998: , jtf_tty_geo_terr gtr
999: , jtf_tty_terr_grp_roles tgr
1000: , jtf_tty_role_access ra
1001: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
1002: AND gtr.geo_territory_id = grsc.geo_territory_id
1003: AND grsc.rsc_role_code = tgr.role_code

Line 1012: , jtf_tty_terr_grp_roles tgr

1008: SELECT ra.access_type
1009: FROM
1010: JTF_TTY_GEO_TERR_RSC grsc
1011: , jtf_tty_geo_terr gtr
1012: , jtf_tty_terr_grp_roles tgr
1013: , jtf_tty_role_access ra
1014: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
1015: AND gtr.geo_territory_id = grsc.geo_territory_id
1016: AND grsc.rsc_role_code = tgr.role_code

Line 1153: , jtf_tty_terr_grp_roles b

1149: CURSOR NON_OVLY_role_access( lp_terr_group_id NUMBER
1150: , lp_role VARCHAR2) IS
1151: SELECT DISTINCT a.access_type
1152: FROM jtf_tty_role_access a
1153: , jtf_tty_terr_grp_roles b
1154: WHERE a.terr_group_role_id = b.terr_group_role_id
1155: AND b.terr_group_id = lp_terr_group_id
1156: AND b.role_code = lp_role
1157: AND NOT EXISTS (

Line 1170: , jtf_tty_terr_grp_roles b

1166: SELECT b.role_code role_code
1167: --,a.interest_type_id
1168: ,b.terr_group_id
1169: FROM jtf_tty_role_prod_int a
1170: , jtf_tty_terr_grp_roles b
1171: WHERE a.terr_group_role_id(+) = b.terr_group_role_id
1172: AND b.terr_group_id = l_terr_group_id
1173: AND a.terr_group_role_id IS NULL
1174: ORDER BY b.role_code;

Line 1235: , jtf_tty_terr_grp_roles tgr

1231: /* Salesperson, with Role that has a Product Interest defined, exists for this Named Account */
1232: SELECT NULL
1233: FROM jtf_tty_geo_terr_rsc grsc
1234: , jtf_tty_role_prod_int rpi
1235: , jtf_tty_terr_grp_roles tgr
1236: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
1237: AND tgr.terr_group_id = gterr.TERR_GROUP_ID
1238: AND tgr.role_code = grsc.rsc_role_code
1239: AND grsc.geo_territory_id = gterr.geo_territory_id );

Line 1250: , jtf_tty_terr_grp_roles b

1246: b.role_code role_code
1247: , r.role_name role_name
1248: FROM jtf_rs_roles_vl r
1249: , jtf_tty_role_prod_int a
1250: , jtf_tty_terr_grp_roles b
1251: WHERE r.role_code = b.role_code
1252: AND a.terr_group_role_id = b.terr_group_role_id
1253: AND b.terr_group_id = lp_terr_group_id
1254: AND EXISTS (

Line 1268: , jtf_tty_terr_grp_roles b

1264: /* Access Types for a particular Role within a Territory Group */
1265: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
1266: SELECT DISTINCT a.access_type
1267: FROM jtf_tty_role_access a
1268: , jtf_tty_terr_grp_roles b
1269: WHERE a.terr_group_role_id = b.terr_group_role_id
1270: AND b.terr_group_id = l_terr_group_id
1271: AND b.role_code = l_role
1272: ORDER BY a.access_type ;

Line 1280: , jtf_tty_terr_grp_roles b

1276: SELECT a.interest_type_id
1277: ,a.product_category_id
1278: ,a.product_category_set_id
1279: FROM jtf_tty_role_prod_int a
1280: , jtf_tty_terr_grp_roles b
1281: WHERE a.terr_group_role_id = b.terr_group_role_id
1282: AND b.terr_group_id = l_terr_group_id
1283: AND b.role_code = l_role;
1284:

Line 1290: , jtf_tty_terr_grp_roles b

1286: ** do not have Product Interest defined */
1287: CURSOR role_no_pi(l_terr_group_id NUMBER) IS
1288: SELECT DISTINCT b.role_code
1289: FROM jtf_tty_role_access a
1290: , jtf_tty_terr_grp_roles b
1291: , jtf_tty_role_prod_int c
1292: WHERE a.terr_group_role_id = b.terr_group_role_id
1293: AND b.terr_group_id = l_terr_group_id
1294: AND a.access_type = 'ACCOUNT'

Line 1298: , jtf_tty_terr_grp_roles d

1294: AND a.access_type = 'ACCOUNT'
1295: AND c.terr_group_role_id = b.terr_group_role_id
1296: AND NOT EXISTS ( SELECT 1
1297: FROM jtf_tty_role_prod_int e
1298: , jtf_tty_terr_grp_roles d
1299: WHERE e.terr_group_role_id (+) = d.terr_group_role_id
1300: AND d.terr_group_id = b.terr_group_id
1301: AND d.role_code = b.role_code
1302: AND e.interest_type_id IS NULL);

Line 1328: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL

1324:
1325: l_ovnon_flag:='Y';
1326: i :=i +1;
1327:
1328: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL
1329: INTO l_id
1330: FROM DUAL;
1331:
1332: l_overnon_role_tbl(i).grp_role_id:= l_id;

Line 1334: INSERT INTO JTF_TTY_TERR_GRP_ROLES(

1330: FROM DUAL;
1331:
1332: l_overnon_role_tbl(i).grp_role_id:= l_id;
1333:
1334: INSERT INTO JTF_TTY_TERR_GRP_ROLES(
1335: TERR_GROUP_ROLE_ID
1336: , OBJECT_VERSION_NUMBER
1337: , TERR_GROUP_ID
1338: , ROLE_CODE

Line 1751: DELETE FROM jtf_tty_terr_grp_roles

1747: /* delete the role and access */
1748: /********************************************************/
1749: IF l_ovnon_flag = 'Y' THEN
1750: FOR i IN l_overnon_role_tbl.first.. l_overnon_role_tbl.last LOOP
1751: DELETE FROM jtf_tty_terr_grp_roles
1752: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
1753:
1754: DELETE FROM jtf_tty_role_access
1755: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;

Line 1775: , jtf_tty_terr_grp_roles b

1771: SELECT COUNT( DISTINCT b.role_code )
1772: INTO l_pi_count
1773: FROM jtf_rs_roles_vl r
1774: , jtf_tty_role_prod_int a
1775: , jtf_tty_terr_grp_roles b
1776: WHERE r.role_code = b.role_code
1777: AND a.terr_group_role_id = b.terr_group_role_id
1778: AND b.terr_group_id = p_TERR_GROUP_ID(x)
1779: AND EXISTS (

Line 2500: , jtf_tty_terr_grp_roles tgr

2496: SELECT ra.access_type
2497: FROM
2498: JTF_TTY_GEO_TERR_RSC grsc
2499: , jtf_tty_geo_terr gtr
2500: , jtf_tty_terr_grp_roles tgr
2501: , jtf_tty_role_access ra
2502: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
2503: AND gtr.geo_territory_id = grsc.geo_territory_id
2504: AND grsc.rsc_role_code = tgr.role_code

Line 2513: , jtf_tty_terr_grp_roles tgr

2509: SELECT ra.access_type
2510: FROM
2511: JTF_TTY_GEO_TERR_RSC grsc
2512: , jtf_tty_geo_terr gtr
2513: , jtf_tty_terr_grp_roles tgr
2514: , jtf_tty_role_access ra
2515: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
2516: AND gtr.geo_territory_id = grsc.geo_territory_id
2517: AND grsc.rsc_role_code = tgr.role_code

Line 2654: , jtf_tty_terr_grp_roles b

2650: CURSOR NON_OVLY_role_access( lp_terr_group_id NUMBER
2651: , lp_role VARCHAR2) IS
2652: SELECT DISTINCT a.access_type
2653: FROM jtf_tty_role_access a
2654: , jtf_tty_terr_grp_roles b
2655: WHERE a.terr_group_role_id = b.terr_group_role_id
2656: AND b.terr_group_id = lp_terr_group_id
2657: AND b.role_code = lp_role
2658: AND NOT EXISTS (

Line 2671: , jtf_tty_terr_grp_roles b

2667: SELECT b.role_code role_code
2668: --,a.interest_type_id
2669: ,b.terr_group_id
2670: FROM jtf_tty_role_prod_int a
2671: , jtf_tty_terr_grp_roles b
2672: WHERE a.terr_group_role_id(+) = b.terr_group_role_id
2673: AND b.terr_group_id = l_terr_group_id
2674: AND a.terr_group_role_id IS NULL
2675: ORDER BY b.role_code;

Line 2736: , jtf_tty_terr_grp_roles tgr

2732: /* Salesperson, with Role that has a Product Interest defined, exists for this Named Account */
2733: SELECT NULL
2734: FROM jtf_tty_geo_terr_rsc grsc
2735: , jtf_tty_role_prod_int rpi
2736: , jtf_tty_terr_grp_roles tgr
2737: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
2738: AND tgr.terr_group_id = gterr.TERR_GROUP_ID
2739: AND tgr.role_code = grsc.rsc_role_code
2740: AND grsc.geo_territory_id = gterr.geo_territory_id );

Line 2751: , jtf_tty_terr_grp_roles b

2747: b.role_code role_code
2748: , r.role_name role_name
2749: FROM jtf_rs_roles_vl r
2750: , jtf_tty_role_prod_int a
2751: , jtf_tty_terr_grp_roles b
2752: WHERE r.role_code = b.role_code
2753: AND a.terr_group_role_id = b.terr_group_role_id
2754: AND b.terr_group_id = lp_terr_group_id
2755: AND EXISTS (

Line 2769: , jtf_tty_terr_grp_roles b

2765: /* Access Types for a particular Role within a Territory Group */
2766: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
2767: SELECT DISTINCT a.access_type
2768: FROM jtf_tty_role_access a
2769: , jtf_tty_terr_grp_roles b
2770: WHERE a.terr_group_role_id = b.terr_group_role_id
2771: AND b.terr_group_id = l_terr_group_id
2772: AND b.role_code = l_role
2773: ORDER BY a.access_type ;

Line 2781: , jtf_tty_terr_grp_roles b

2777: SELECT a.interest_type_id
2778: ,a.product_category_id
2779: ,a.product_category_set_id
2780: FROM jtf_tty_role_prod_int a
2781: , jtf_tty_terr_grp_roles b
2782: WHERE a.terr_group_role_id = b.terr_group_role_id
2783: AND b.terr_group_id = l_terr_group_id
2784: AND b.role_code = l_role;
2785:

Line 2791: , jtf_tty_terr_grp_roles b

2787: ** do not have Product Interest defined */
2788: CURSOR role_no_pi(l_terr_group_id NUMBER) IS
2789: SELECT DISTINCT b.role_code
2790: FROM jtf_tty_role_access a
2791: , jtf_tty_terr_grp_roles b
2792: , jtf_tty_role_prod_int c
2793: WHERE a.terr_group_role_id = b.terr_group_role_id
2794: AND b.terr_group_id = l_terr_group_id
2795: AND a.access_type = 'ACCOUNT'

Line 2799: , jtf_tty_terr_grp_roles d

2795: AND a.access_type = 'ACCOUNT'
2796: AND c.terr_group_role_id = b.terr_group_role_id
2797: AND NOT EXISTS ( SELECT 1
2798: FROM jtf_tty_role_prod_int e
2799: , jtf_tty_terr_grp_roles d
2800: WHERE e.terr_group_role_id (+) = d.terr_group_role_id
2801: AND d.terr_group_id = b.terr_group_id
2802: AND d.role_code = b.role_code
2803: AND e.interest_type_id IS NULL);

Line 2843: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL

2839:
2840: l_ovnon_flag:='Y';
2841: i :=i +1;
2842:
2843: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL
2844: INTO l_id
2845: FROM DUAL;
2846:
2847: l_overnon_role_tbl(i).grp_role_id:= l_id;

Line 2849: INSERT INTO JTF_TTY_TERR_GRP_ROLES(

2845: FROM DUAL;
2846:
2847: l_overnon_role_tbl(i).grp_role_id:= l_id;
2848:
2849: INSERT INTO JTF_TTY_TERR_GRP_ROLES(
2850: TERR_GROUP_ROLE_ID
2851: , OBJECT_VERSION_NUMBER
2852: , TERR_GROUP_ID
2853: , ROLE_CODE

Line 3417: DELETE FROM jtf_tty_terr_grp_roles

3413: /********************************************************/
3414:
3415: IF l_ovnon_flag = 'Y' THEN
3416: FOR i IN l_overnon_role_tbl.first.. l_overnon_role_tbl.last LOOP
3417: DELETE FROM jtf_tty_terr_grp_roles
3418: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
3419:
3420: DELETE FROM jtf_tty_role_access
3421: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;

Line 3441: , jtf_tty_terr_grp_roles b

3437: SELECT COUNT( DISTINCT b.role_code )
3438: INTO l_pi_count
3439: FROM jtf_rs_roles_vl r
3440: , jtf_tty_role_prod_int a
3441: , jtf_tty_terr_grp_roles b
3442: WHERE r.role_code = b.role_code
3443: AND a.terr_group_role_id = b.terr_group_role_id
3444: AND b.terr_group_id = p_TERR_GROUP_ID(x)
3445: AND EXISTS (

Line 4372: , jtf_tty_terr_grp_roles tgr

4368: SELECT ra.access_type
4369: FROM
4370: jtf_tty_named_acct_rsc nar
4371: , jtf_tty_terr_grp_accts tga
4372: , jtf_tty_terr_grp_roles tgr
4373: , jtf_tty_role_access ra
4374: WHERE tga.terr_group_account_id = nar.terr_group_account_id
4375: AND nar.terr_group_account_id = LP_terr_group_account_id
4376: AND tga.terr_group_id = tgr.terr_group_id

Line 4385: , jtf_tty_terr_grp_roles tgr

4381: SELECT ra.access_type
4382: FROM
4383: jtf_tty_named_acct_rsc nar
4384: , jtf_tty_terr_grp_accts tga
4385: , jtf_tty_terr_grp_roles tgr
4386: , jtf_tty_role_access ra
4387: WHERE tga.terr_group_account_id = nar.terr_group_account_id
4388: AND nar.terr_group_account_id = LP_terr_group_account_id
4389: AND tga.terr_group_id = tgr.terr_group_id

Line 4402: , jtf_tty_terr_grp_roles b

4398: /* Access Types for a particular Role within a Territory Group */
4399: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
4400: SELECT DISTINCT a.access_type
4401: FROM jtf_tty_role_access a
4402: , jtf_tty_terr_grp_roles b
4403: WHERE a.terr_group_role_id = b.terr_group_role_id
4404: AND b.terr_group_id = l_terr_group_id
4405: AND b.role_code = l_role
4406: ORDER BY a.access_type ;

Line 4413: , jtf_tty_terr_grp_roles b

4409: CURSOR NON_OVLY_role_access( lp_terr_group_id NUMBER
4410: , lp_role VARCHAR2) IS
4411: SELECT DISTINCT a.access_type, a.trans_access_code
4412: FROM jtf_tty_role_access a
4413: , jtf_tty_terr_grp_roles b
4414: WHERE a.terr_group_role_id = b.terr_group_role_id
4415: AND b.terr_group_id = lp_terr_group_id
4416: AND b.role_code = lp_role
4417: AND NOT EXISTS (

Line 4430: , jtf_tty_terr_grp_roles b

4426: SELECT b.role_code role_code
4427: --,a.interest_type_id
4428: ,b.terr_group_id
4429: FROM jtf_tty_role_prod_int a
4430: , jtf_tty_terr_grp_roles b
4431: WHERE a.terr_group_role_id(+) = b.terr_group_role_id
4432: AND b.terr_group_id = l_terr_group_id
4433: AND a.terr_group_role_id IS NULL
4434: ORDER BY b.role_code;

Line 4444: , jtf_tty_terr_grp_roles b

4440: b.role_code role_code
4441: , r.role_name role_name
4442: FROM jtf_rs_roles_vl r
4443: , jtf_tty_role_prod_int a
4444: , jtf_tty_terr_grp_roles b
4445: WHERE r.role_code = b.role_code
4446: AND a.terr_group_role_id = b.terr_group_role_id
4447: AND b.terr_group_id = lp_terr_group_id
4448: AND EXISTS (

Line 4463: , jtf_tty_terr_grp_roles b

4459: SELECT a.interest_type_id
4460: ,a.product_category_id
4461: ,a.product_category_set_id
4462: FROM jtf_tty_role_prod_int a
4463: , jtf_tty_terr_grp_roles b
4464: WHERE a.terr_group_role_id = b.terr_group_role_id
4465: AND b.terr_group_id = l_terr_group_id
4466: AND b.role_code = l_role;
4467:

Line 4570: , jtf_tty_terr_grp_roles tgr

4566: ** Interest defined, exists for this Named Account */
4567: SELECT NULL
4568: FROM jtf_tty_named_acct_rsc nar
4569: , jtf_tty_role_prod_int rpi
4570: , jtf_tty_terr_grp_roles tgr
4571: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
4572: AND tgr.terr_group_id = C.TERR_GROUP_ID
4573: AND tgr.role_code = nar.rsc_role_code
4574: AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );

Line 4601: , jtf_tty_terr_grp_roles tgr

4597: ** Interest defined, exists for this Named Account */
4598: SELECT NULL
4599: FROM jtf_tty_named_acct_rsc nar
4600: , jtf_tty_role_prod_int rpi
4601: , jtf_tty_terr_grp_roles tgr
4602: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
4603: AND tgr.terr_group_id = C.TERR_GROUP_ID
4604: AND tgr.role_code = nar.rsc_role_code
4605: AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );

Line 4638: , jtf_tty_terr_grp_roles b

4634: /* Access Types for a Territory Group */
4635: CURSOR na_access(l_terr_group_id NUMBER) IS
4636: SELECT DISTINCT a.access_type
4637: FROM jtf_tty_role_access a
4638: , jtf_tty_terr_grp_roles b
4639: WHERE a.terr_group_role_id = b.terr_group_role_id
4640: AND b.terr_group_id = l_terr_group_id;
4641:
4642: /* get those roles for a territory Group that

Line 4647: , jtf_tty_terr_grp_roles b

4643: ** do not have Product Interest defined */
4644: CURSOR role_no_pi(l_terr_group_id NUMBER) IS
4645: SELECT DISTINCT b.role_code
4646: FROM jtf_tty_role_access a
4647: , jtf_tty_terr_grp_roles b
4648: , jtf_tty_role_prod_int c
4649: WHERE a.terr_group_role_id = b.terr_group_role_id
4650: AND b.terr_group_id = l_terr_group_id
4651: AND a.access_type = 'ACCOUNT'

Line 4655: , jtf_tty_terr_grp_roles d

4651: AND a.access_type = 'ACCOUNT'
4652: AND c.terr_group_role_id = b.terr_group_role_id
4653: AND NOT EXISTS ( SELECT 1
4654: FROM jtf_tty_role_prod_int e
4655: , jtf_tty_terr_grp_roles d
4656: WHERE e.terr_group_role_id (+) = d.terr_group_role_id
4657: AND d.terr_group_id = b.terr_group_id
4658: AND d.role_code = b.role_code
4659: AND e.interest_type_id IS NULL);

Line 4701: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL

4697:
4698: l_ovnon_flag := 'Y';
4699: i := i + 1;
4700:
4701: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL
4702: INTO l_id
4703: FROM DUAL;
4704:
4705: l_overnon_role_tbl(i).grp_role_id:= l_id;

Line 4707: INSERT INTO JTF_TTY_TERR_GRP_ROLES(

4703: FROM DUAL;
4704:
4705: l_overnon_role_tbl(i).grp_role_id:= l_id;
4706:
4707: INSERT INTO JTF_TTY_TERR_GRP_ROLES(
4708: TERR_GROUP_ROLE_ID
4709: , OBJECT_VERSION_NUMBER
4710: , TERR_GROUP_ID
4711: , ROLE_CODE

Line 5650: DELETE FROM jtf_tty_terr_grp_roles

5646: IF l_ovnon_flag = 'Y' THEN
5647:
5648: FOR i IN l_overnon_role_tbl.first.. l_overnon_role_tbl.last
5649: LOOP
5650: DELETE FROM jtf_tty_terr_grp_roles
5651: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
5652: --dbms_output.put_line('deleted');
5653: DELETE FROM jtf_tty_role_access
5654: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;

Line 5679: , jtf_tty_terr_grp_roles b

5675: SELECT COUNT( DISTINCT b.role_code )
5676: INTO l_pi_count
5677: FROM jtf_rs_roles_vl r
5678: , jtf_tty_role_prod_int a
5679: , jtf_tty_terr_grp_roles b
5680: WHERE r.role_code = b.role_code
5681: AND a.terr_group_role_id = b.terr_group_role_id
5682: AND b.terr_group_id = p_terr_group_id(x)
5683: AND EXISTS (

Line 7078: , jtf_tty_terr_grp_roles tgr

7074: SELECT ra.access_type
7075: FROM
7076: jtf_tty_named_acct_rsc nar
7077: , jtf_tty_terr_grp_accts tga
7078: , jtf_tty_terr_grp_roles tgr
7079: , jtf_tty_role_access ra
7080: WHERE tga.terr_group_account_id = nar.terr_group_account_id
7081: AND nar.terr_group_account_id = LP_terr_group_account_id
7082: AND tga.terr_group_id = tgr.terr_group_id

Line 7091: , jtf_tty_terr_grp_roles tgr

7087: SELECT ra.access_type
7088: FROM
7089: jtf_tty_named_acct_rsc nar
7090: , jtf_tty_terr_grp_accts tga
7091: , jtf_tty_terr_grp_roles tgr
7092: , jtf_tty_role_access ra
7093: WHERE tga.terr_group_account_id = nar.terr_group_account_id
7094: AND nar.terr_group_account_id = LP_terr_group_account_id
7095: AND tga.terr_group_id = tgr.terr_group_id

Line 7108: , jtf_tty_terr_grp_roles b

7104: /* Access Types for a particular Role within a Territory Group */
7105: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
7106: SELECT DISTINCT a.access_type
7107: FROM jtf_tty_role_access a
7108: , jtf_tty_terr_grp_roles b
7109: WHERE a.terr_group_role_id = b.terr_group_role_id
7110: AND b.terr_group_id = l_terr_group_id
7111: AND b.role_code = l_role
7112: ORDER BY a.access_type ;

Line 7119: , jtf_tty_terr_grp_roles b

7115: CURSOR NON_OVLY_role_access( lp_terr_group_id NUMBER
7116: , lp_role VARCHAR2) IS
7117: SELECT DISTINCT a.access_type, a.trans_access_code
7118: FROM jtf_tty_role_access a
7119: , jtf_tty_terr_grp_roles b
7120: WHERE a.terr_group_role_id = b.terr_group_role_id
7121: AND b.terr_group_id = lp_terr_group_id
7122: AND b.role_code = lp_role
7123: AND NOT EXISTS (

Line 7136: , jtf_tty_terr_grp_roles b

7132: SELECT b.role_code role_code
7133: --,a.interest_type_id
7134: ,b.terr_group_id
7135: FROM jtf_tty_role_prod_int a
7136: , jtf_tty_terr_grp_roles b
7137: WHERE a.terr_group_role_id(+) = b.terr_group_role_id
7138: AND b.terr_group_id = l_terr_group_id
7139: AND a.terr_group_role_id IS NULL
7140: ORDER BY b.role_code;

Line 7150: , jtf_tty_terr_grp_roles b

7146: b.role_code role_code
7147: , r.role_name role_name
7148: FROM jtf_rs_roles_vl r
7149: , jtf_tty_role_prod_int a
7150: , jtf_tty_terr_grp_roles b
7151: WHERE r.role_code = b.role_code
7152: AND a.terr_group_role_id = b.terr_group_role_id
7153: AND b.terr_group_id = lp_terr_group_id
7154: AND EXISTS (

Line 7169: , jtf_tty_terr_grp_roles b

7165: SELECT a.interest_type_id
7166: ,a.product_category_id
7167: ,a.product_category_set_id
7168: FROM jtf_tty_role_prod_int a
7169: , jtf_tty_terr_grp_roles b
7170: WHERE a.terr_group_role_id = b.terr_group_role_id
7171: AND b.terr_group_id = l_terr_group_id
7172: AND b.role_code = l_role;
7173:

Line 7317: , jtf_tty_terr_grp_roles tgr

7313: ** Interest defined, exists for this Named Account */
7314: SELECT NULL
7315: FROM jtf_tty_named_acct_rsc nar
7316: , jtf_tty_role_prod_int rpi
7317: , jtf_tty_terr_grp_roles tgr
7318: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
7319: AND tgr.terr_group_id = C.TERR_GROUP_ID
7320: AND tgr.role_code = nar.rsc_role_code
7321: AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );

Line 7364: , jtf_tty_terr_grp_roles tgr

7360: ** Interest defined, exists for this Named Account */
7361: SELECT NULL
7362: FROM jtf_tty_named_acct_rsc nar
7363: , jtf_tty_role_prod_int rpi
7364: , jtf_tty_terr_grp_roles tgr
7365: WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
7366: AND tgr.terr_group_id = C.TERR_GROUP_ID
7367: AND tgr.role_code = nar.rsc_role_code
7368: AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );

Line 7403: , jtf_tty_terr_grp_roles b

7399: /* Access Types for a Territory Group */
7400: CURSOR na_access(l_terr_group_id NUMBER) IS
7401: SELECT DISTINCT a.access_type
7402: FROM jtf_tty_role_access a
7403: , jtf_tty_terr_grp_roles b
7404: WHERE a.terr_group_role_id = b.terr_group_role_id
7405: AND b.terr_group_id = l_terr_group_id;
7406:
7407: /* Named Account Catch-All Customer Keyname values */

Line 7460: , jtf_tty_terr_grp_roles b

7456: ** do not have Product Interest defined */
7457: CURSOR role_no_pi(l_terr_group_id NUMBER) IS
7458: SELECT DISTINCT b.role_code
7459: FROM jtf_tty_role_access a
7460: , jtf_tty_terr_grp_roles b
7461: , jtf_tty_role_prod_int c
7462: WHERE a.terr_group_role_id = b.terr_group_role_id
7463: AND b.terr_group_id = l_terr_group_id
7464: AND a.access_type = 'ACCOUNT'

Line 7468: , jtf_tty_terr_grp_roles d

7464: AND a.access_type = 'ACCOUNT'
7465: AND c.terr_group_role_id = b.terr_group_role_id
7466: AND NOT EXISTS ( SELECT 1
7467: FROM jtf_tty_role_prod_int e
7468: , jtf_tty_terr_grp_roles d
7469: WHERE e.terr_group_role_id (+) = d.terr_group_role_id
7470: AND d.terr_group_id = b.terr_group_id
7471: AND d.role_code = b.role_code
7472: AND e.interest_type_id IS NULL);

Line 7516: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL

7512:
7513: l_ovnon_flag := 'Y';
7514: i := i + 1;
7515:
7516: SELECT JTF_TTY_TERR_GRP_ROLES_S.NEXTVAL
7517: INTO l_id
7518: FROM DUAL;
7519:
7520: l_overnon_role_tbl(i).grp_role_id:= l_id;

Line 7522: INSERT INTO JTF_TTY_TERR_GRP_ROLES(

7518: FROM DUAL;
7519:
7520: l_overnon_role_tbl(i).grp_role_id:= l_id;
7521:
7522: INSERT INTO JTF_TTY_TERR_GRP_ROLES(
7523: TERR_GROUP_ROLE_ID
7524: , OBJECT_VERSION_NUMBER
7525: , TERR_GROUP_ID
7526: , ROLE_CODE

Line 8865: DELETE FROM jtf_tty_terr_grp_roles

8861: IF l_ovnon_flag = 'Y' THEN
8862:
8863: FOR i IN l_overnon_role_tbl.first.. l_overnon_role_tbl.last
8864: LOOP
8865: DELETE FROM jtf_tty_terr_grp_roles
8866: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
8867: --dbms_output.put_line('deleted');
8868: DELETE FROM jtf_tty_role_access
8869: WHERE TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;

Line 8895: , jtf_tty_terr_grp_roles b

8891: SELECT COUNT( DISTINCT b.role_code )
8892: INTO l_pi_count
8893: FROM jtf_rs_roles_vl r
8894: , jtf_tty_role_prod_int a
8895: , jtf_tty_terr_grp_roles b
8896: WHERE r.role_code = b.role_code
8897: AND a.terr_group_role_id = b.terr_group_role_id
8898: AND b.terr_group_id = p_terr_group_id(x)
8899: AND EXISTS (

Line 12684: , jtf_tty_terr_grp_roles b

12680: /* Access Types for a particular Role within a Territory Group */
12681: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
12682: SELECT DISTINCT a.access_type, a.trans_access_code
12683: FROM jtf_tty_role_access a
12684: , jtf_tty_terr_grp_roles b
12685: WHERE a.terr_group_role_id = b.terr_group_role_id
12686: AND b.terr_group_id = l_terr_group_id
12687: AND b.role_code = l_role
12688: ORDER BY a.access_type ;

Line 12881: FROM jtf_tty_terr_grp_roles b

12877: /* Roles defined for the TG */
12878: CURSOR roles_for_TG(l_terr_group_id NUMBER) IS
12879: SELECT b.role_code role_code
12880: ,b.terr_group_id
12881: FROM jtf_tty_terr_grp_roles b
12882: WHERE b.terr_group_id = l_terr_group_id
12883: ORDER BY b.role_code;
12884:
12885: CURSOR resource_grp(l_terr_group_acct_id NUMBER, l_role VARCHAR2) IS

Line 12923: , jtf_tty_terr_grp_roles b

12919: /* Access Types for a particular Role within a Territory Group */
12920: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS
12921: SELECT DISTINCT a.access_type, a.trans_access_code
12922: FROM jtf_tty_role_access a
12923: , jtf_tty_terr_grp_roles b
12924: WHERE a.terr_group_role_id = b.terr_group_role_id
12925: AND b.terr_group_id = l_terr_group_id
12926: AND b.role_code = l_role
12927: ORDER BY a.access_type ;