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_RS_REP_MGR_DENORM_PVT dependencies on JTF_RS_ROLE_RELATIONS

Line 5: -- Purpose : Inserts/Update the JTF_RS_REPORTING_MANAGERS table based on changes in jtf_rs_role_relations,

1: PACKAGE BODY JTF_RS_REP_MGR_DENORM_PVT AS
2: /* $Header: jtfrsvpb.pls 120.0 2005/05/11 08:23:12 appldev ship $ */
3: -- API Name : JTF_RS_REP_MGR_DENORM_PVT
4: -- Type : Private
5: -- Purpose : Inserts/Update the JTF_RS_REPORTING_MANAGERS table based on changes in jtf_rs_role_relations,
6: -- jtf_rs_grp_relations
7: -- Modification History
8: -- DATE NAME PURPOSE
9: -- 7 Oct 1999 S Roy Choudhury Created

Line 18: /FOR INSERT IN JTF_RS_ROLE_RELATIONS /

14: --
15:
16: G_PKG_NAME VARCHAR2(30) := 'JTF_RS_REP_MGR_DENORM_PVT';
17:
18: /*FOR INSERT IN JTF_RS_ROLE_RELATIONS */
19: PROCEDURE INSERT_REP_MANAGER(
20: P_API_VERSION IN NUMBER,
21: P_INIT_MSG_LIST IN VARCHAR2,
22: P_COMMIT IN VARCHAR2,

Line 23: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

19: PROCEDURE INSERT_REP_MANAGER(
20: P_API_VERSION IN NUMBER,
21: P_INIT_MSG_LIST IN VARCHAR2,
22: P_COMMIT IN VARCHAR2,
23: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
24: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
25: X_MSG_COUNT OUT NOCOPY NUMBER,
26: X_MSG_DATA OUT NOCOPY VARCHAR2 )
27: IS

Line 34: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

30: SELECT jtf_rs_rep_managers_s.nextval
31: FROM dual;
32:
33:
34: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
35: IS
36: SELECT mem.resource_id,
37: mem.person_id,
38: mem.group_id,

Line 48: FROM jtf_rs_role_relations rlt,

44: rol.lead_flag ,
45: rol.manager_flag,
46: rsc.category,
47: rlt.role_relate_id
48: FROM jtf_rs_role_relations rlt,
49: jtf_rs_group_members mem,
50: jtf_rs_roles_B rol,
51: jtf_rs_resource_extns rsc
52: WHERE rlt.role_relate_id = l_role_relate_id

Line 68: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

64:
65: CURSOR other_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
66: l_start_date_active DATE,
67: l_end_date_active DATE,
68: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
69: IS
70: SELECT mem.resource_id,
71: mem.person_id,
72: mem.group_id,

Line 82: FROM jtf_rs_role_relations rlt,

78: rol.lead_flag ,
79: rol.manager_flag,
80: rsc.category,
81: rlt.role_relate_id
82: FROM jtf_rs_role_relations rlt,
83: jtf_rs_group_members mem,
84: jtf_rs_roles_B rol,
85: jtf_rs_resource_extns rsc
86: WHERE mem.group_id = l_group_id

Line 131: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

127: AND start_date_active = l_start_date_active
128: AND (end_date_active = l_end_date_active
129: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
130:
131: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
132: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
133: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
134: l_start_date_active date,
135: l_end_date_active date)

Line 132: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

128: AND (end_date_active = l_end_date_active
129: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
130:
131: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
132: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
133: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
134: l_start_date_active date,
135: l_end_date_active date)
136: IS

Line 158: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

154: --cursor for same group manager
155: CURSOR same_grp_mgr_admin_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
156: l_start_date_active DATE,
157: l_end_date_active DATE,
158: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
159: IS
160: SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
161: mem.resource_id,
162: mem.person_id,

Line 169: jtf_rs_role_relations rlt,

165: rol.admin_flag ,
166: rol.manager_flag,
167: rlt.role_relate_id
168: FROM jtf_rs_group_members mem,
169: jtf_rs_role_relations rlt,
170: jtf_rs_roles_B rol
171: WHERE mem.group_id = l_group_id
172: AND mem.group_member_id = rlt.role_resource_id
173: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 226: jtf_rs_role_relations rlt,

222: rlt.start_date_active,
223: rlt.end_date_active,
224: rlt.role_relate_id
225: FROM jtf_rs_group_members mem,
226: jtf_rs_role_relations rlt,
227: jtf_rs_roles_b rol
228: WHERE mem.group_id = l_group_id
229: AND mem.group_member_id = rlt.role_resource_id
230: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 256: jtf_rs_role_relations rlt,

252: rlt.start_date_active,
253: rlt.end_date_active,
254: rlt.role_relate_id
255: FROM jtf_rs_group_members mem,
256: jtf_rs_role_relations rlt,
257: jtf_rs_roles_b rol
258: WHERE mem.group_id = l_group_id
259: AND mem.group_member_id = rlt.role_resource_id
260: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 312: FROM jtf_rs_role_relations rlt,

308: rol.admin_flag,
309: rol.member_flag,
310: rsc.category,
311: rlt.role_relate_id
312: FROM jtf_rs_role_relations rlt,
313: jtf_rs_group_members mem,
314: jtf_rs_roles_b rol,
315: jtf_rs_resource_extns rsc
316: WHERE mem.group_id = l_group_id

Line 338: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;

334: AND mem.resource_id = rsc.resource_id;
335:
336: child_mem_rec child_mem_cur%rowtype;
337:
338: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
339: l_hierarchy_type JTF_RS_REP_MANAGERS.HIERARCHY_TYPE%TYPE;
340: l_reports_to_flag JTF_RS_REP_MANAGERS.REPORTS_TO_FLAG%TYPE;
341: l_denorm_mgr_id JTF_RS_REP_MANAGERS.DENORM_MGR_ID%TYPE;
342: x_row_id VARCHAR2(100);

Line 1148: /FOR INSERT IN JTF_RS_ROLE_RELATIONS /

1144:
1145:
1146: --for migration
1147:
1148: /*FOR INSERT IN JTF_RS_ROLE_RELATIONS */
1149: PROCEDURE INSERT_REP_MANAGER_MIGR(
1150: P_API_VERSION IN NUMBER,
1151: P_INIT_MSG_LIST IN VARCHAR2,
1152: P_COMMIT IN VARCHAR2,

Line 1153: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

1149: PROCEDURE INSERT_REP_MANAGER_MIGR(
1150: P_API_VERSION IN NUMBER,
1151: P_INIT_MSG_LIST IN VARCHAR2,
1152: P_COMMIT IN VARCHAR2,
1153: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1154: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1155: X_MSG_COUNT OUT NOCOPY NUMBER,
1156: X_MSG_DATA OUT NOCOPY VARCHAR2 )
1157: IS

Line 1164: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

1160: SELECT jtf_rs_rep_managers_s.nextval
1161: FROM dual;
1162:
1163:
1164: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
1165: IS
1166: SELECT mem.resource_id,
1167: mem.person_id,
1168: mem.group_id,

Line 1178: FROM jtf_rs_role_relations rlt,

1174: rol.lead_flag ,
1175: rol.manager_flag,
1176: rsc.category,
1177: rlt.role_relate_id
1178: FROM jtf_rs_role_relations rlt,
1179: jtf_rs_group_members mem,
1180: jtf_rs_roles_B rol,
1181: jtf_rs_resource_extns rsc
1182: WHERE rlt.role_relate_id = l_role_relate_id

Line 1198: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

1194:
1195: CURSOR other_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1196: l_start_date_active DATE,
1197: l_end_date_active DATE,
1198: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
1199: IS
1200: SELECT mem.resource_id,
1201: mem.person_id,
1202: mem.group_id,

Line 1212: FROM jtf_rs_role_relations rlt,

1208: rol.lead_flag ,
1209: rol.manager_flag,
1210: rsc.category,
1211: rlt.role_relate_id
1212: FROM jtf_rs_role_relations rlt,
1213: jtf_rs_group_members mem,
1214: jtf_rs_roles_B rol,
1215: jtf_rs_resource_extns rsc
1216: WHERE mem.group_id = l_group_id

Line 1254: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

1250: AND start_date_active = l_start_date_active
1251: AND (end_date_active = l_end_date_active
1252: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
1253:
1254: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1255: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1256: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
1257: IS
1258: SELECT person_id

Line 1255: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

1251: AND (end_date_active = l_end_date_active
1252: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
1253:
1254: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1255: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1256: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
1257: IS
1258: SELECT person_id
1259: FROM jtf_rs_rep_managers

Line 1272: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

1268: --cursor for same group manager and admin
1269: CURSOR same_grp_mgr_admin_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1270: l_start_date_active DATE,
1271: l_end_date_active DATE,
1272: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
1273: IS
1274: SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
1275: mem.resource_id,
1276: mem.person_id,

Line 1283: jtf_rs_role_relations rlt,

1279: rol.admin_flag ,
1280: rol.manager_flag,
1281: rlt.role_relate_id
1282: FROM jtf_rs_group_members mem,
1283: jtf_rs_role_relations rlt,
1284: jtf_rs_roles_B rol
1285: WHERE mem.group_id = l_group_id
1286: AND mem.group_member_id = rlt.role_resource_id
1287: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 1337: jtf_rs_role_relations rlt,

1333: rlt.start_date_active,
1334: rlt.end_date_active,
1335: rlt.role_relate_id
1336: FROM jtf_rs_group_members mem,
1337: jtf_rs_role_relations rlt,
1338: jtf_rs_roles_b rol
1339: WHERE mem.group_id = l_group_id
1340: AND mem.group_member_id = rlt.role_resource_id
1341: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 1367: jtf_rs_role_relations rlt,

1363: rlt.start_date_active,
1364: rlt.end_date_active,
1365: rlt.role_relate_id
1366: FROM jtf_rs_group_members mem,
1367: jtf_rs_role_relations rlt,
1368: jtf_rs_roles_b rol
1369: WHERE mem.group_id = l_group_id
1370: AND mem.group_member_id = rlt.role_resource_id
1371: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 1420: FROM jtf_rs_role_relations rlt,

1416: rol.admin_flag,
1417: rol.member_flag,
1418: rsc.category,
1419: rlt.role_relate_id
1420: FROM jtf_rs_role_relations rlt,
1421: jtf_rs_group_members mem,
1422: jtf_rs_roles_b rol,
1423: jtf_rs_resource_extns rsc
1424: WHERE mem.group_id = l_group_id

Line 1441: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;

1437: AND mem.resource_id = rsc.resource_id;
1438:
1439: child_mem_rec child_mem_cur%rowtype;
1440:
1441: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
1442: l_hierarchy_type JTF_RS_REP_MANAGERS.HIERARCHY_TYPE%TYPE;
1443: l_reports_to_flag JTF_RS_REP_MANAGERS.REPORTS_TO_FLAG%TYPE;
1444: l_denorm_mgr_id JTF_RS_REP_MANAGERS.DENORM_MGR_ID%TYPE;
1445: x_row_id VARCHAR2(100);

Line 2545: --FOR UPDATE on jtf_rs_role_relations

2541:
2542:
2543:
2544:
2545: --FOR UPDATE on jtf_rs_role_relations
2546:
2547: PROCEDURE UPDATE_REP_MANAGER(
2548: P_API_VERSION IN NUMBER,
2549: P_INIT_MSG_LIST IN VARCHAR2,

Line 2551: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

2547: PROCEDURE UPDATE_REP_MANAGER(
2548: P_API_VERSION IN NUMBER,
2549: P_INIT_MSG_LIST IN VARCHAR2,
2550: P_COMMIT IN VARCHAR2,
2551: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2552: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2553: X_MSG_COUNT OUT NOCOPY NUMBER,
2554: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2555: IS

Line 2556: CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

2552: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2553: X_MSG_COUNT OUT NOCOPY NUMBER,
2554: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2555: IS
2556: CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2557: IS
2558: SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
2559: FROM jtf_rs_rep_managers den
2560: WHERE par_role_relate_id = l_role_relate_id

Line 2565: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;

2561: OR child_role_relate_id = l_role_relate_id;
2562:
2563: denorm_rec denorm_cur%rowtype;
2564:
2565: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
2566:
2567: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
2568: l_api_version CONSTANT NUMBER :=1.0;
2569: l_date Date;

Line 2685: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

2681: PROCEDURE DELETE_MEMBERS(
2682: P_API_VERSION IN NUMBER,
2683: P_INIT_MSG_LIST IN VARCHAR2,
2684: P_COMMIT IN VARCHAR2,
2685: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2686: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2687: X_MSG_COUNT OUT NOCOPY NUMBER,
2688: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2689: IS

Line 2690: CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

2686: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2687: X_MSG_COUNT OUT NOCOPY NUMBER,
2688: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2689: IS
2690: CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2691: IS
2692: SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
2693: FROM jtf_rs_rep_managers den
2694: WHERE par_role_relate_id = l_role_relate_id

Line 2699: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;

2695: OR child_role_relate_id = l_role_relate_id;
2696:
2697: denorm_rec denorm_cur%rowtype;
2698:
2699: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
2700:
2701: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
2702: l_api_version CONSTANT NUMBER :=1.0;
2703: l_date Date;

Line 2831: FROM JTF_RS_ROLE_RELATIONS rlt,

2827:
2828: CURSOR par_role_relate_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
2829: IS
2830: SELECT role_relate_id
2831: FROM JTF_RS_ROLE_RELATIONS rlt,
2832: jtf_rs_group_members mem
2833: WHERE mem.group_id = l_group_id
2834: AND mem.group_member_id = rlt.role_resource_id
2835: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 2844: FROM JTF_RS_ROLE_RELATIONS rlt,

2840:
2841: CURSOR child_role_relate_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
2842: IS
2843: SELECT role_relate_id
2844: FROM JTF_RS_ROLE_RELATIONS rlt,
2845: jtf_rs_group_members mem
2846: WHERE mem.group_id = l_group_id
2847: AND mem.group_member_id = rlt.role_resource_id
2848: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 2855: CURSOR rep_denorm_cur(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

2851:
2852: child_role_relate_rec child_role_relate_cur%rowtype;
2853:
2854:
2855: CURSOR rep_denorm_cur(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2856: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2857: IS
2858: SELECT denorm_mgr_id
2859: FROM jtf_rs_rep_managers

Line 2856: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

2852: child_role_relate_rec child_role_relate_cur%rowtype;
2853:
2854:
2855: CURSOR rep_denorm_cur(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2856: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2857: IS
2858: SELECT denorm_mgr_id
2859: FROM jtf_rs_rep_managers
2860: WHERE par_role_relate_id = l_par_role_relate_id

Line 3009: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

3005: PROCEDURE INSERT_REP_MGR_PARENT(
3006: P_API_VERSION IN NUMBER,
3007: P_INIT_MSG_LIST IN VARCHAR2,
3008: P_COMMIT IN VARCHAR2,
3009: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3010: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3011: X_MSG_COUNT OUT NOCOPY NUMBER,
3012: X_MSG_DATA OUT NOCOPY VARCHAR2 )
3013: IS

Line 3020: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

3016: SELECT jtf_rs_rep_managers_s.nextval
3017: FROM dual;
3018:
3019:
3020: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
3021: IS
3022: SELECT mem.resource_id,
3023: mem.person_id,
3024: mem.group_id,

Line 3034: FROM jtf_rs_role_relations rlt,

3030: rol.lead_flag ,
3031: rol.manager_flag,
3032: rsc.category,
3033: rlt.role_relate_id
3034: FROM jtf_rs_role_relations rlt,
3035: jtf_rs_group_members mem,
3036: jtf_rs_roles_B rol,
3037: jtf_rs_resource_extns rsc
3038: WHERE rlt.role_relate_id = l_role_relate_id

Line 3069: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

3065: AND start_date_active = l_start_date_active
3066: AND (end_date_active = l_end_date_active
3067: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
3068:
3069: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3070: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3071: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
3072: IS
3073: SELECT person_id

Line 3070: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

3066: AND (end_date_active = l_end_date_active
3067: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
3068:
3069: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3070: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3071: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
3072: IS
3073: SELECT person_id
3074: FROM jtf_rs_rep_managers

Line 3115: jtf_rs_role_relations rlt,

3111: rlt.start_date_active,
3112: rlt.end_date_active,
3113: rlt.role_relate_id
3114: FROM jtf_rs_group_members mem,
3115: jtf_rs_role_relations rlt,
3116: jtf_rs_roles_b rol
3117: WHERE mem.group_id = l_group_id
3118: AND mem.group_member_id = rlt.role_resource_id
3119: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 3145: jtf_rs_role_relations rlt,

3141: rlt.start_date_active,
3142: rlt.end_date_active,
3143: rlt.role_relate_id
3144: FROM jtf_rs_group_members mem,
3145: jtf_rs_role_relations rlt,
3146: jtf_rs_roles_b rol
3147: WHERE mem.group_id = l_group_id
3148: AND mem.group_member_id = rlt.role_resource_id
3149: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 3166: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;

3162: mgr_rec mgr_cur%rowtype;
3163:
3164:
3165:
3166: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
3167: l_hierarchy_type JTF_RS_REP_MANAGERS.HIERARCHY_TYPE%TYPE;
3168: l_reports_to_flag JTF_RS_REP_MANAGERS.REPORTS_TO_FLAG%TYPE;
3169: l_denorm_mgr_id JTF_RS_REP_MANAGERS.DENORM_MGR_ID%TYPE;
3170: x_row_id VARCHAR2(100);

Line 3540: FROM jtf_rs_role_relations rlt,

3536: DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
3537: nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
3538: rsc.category,
3539: rlt.role_relate_id
3540: FROM jtf_rs_role_relations rlt,
3541: jtf_rs_group_members mem,
3542: jtf_rs_roles_B rol,
3543: jtf_rs_resource_extns rsc
3544: WHERE mem.group_id = p_group_id

Line 3568: FROM jtf_rs_role_relations rlt,

3564: DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
3565: nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
3566: rsc.category,
3567: rlt.role_relate_id
3568: FROM jtf_rs_role_relations rlt,
3569: jtf_rs_group_members mem,
3570: jtf_rs_roles_B rol,
3571: jtf_rs_resource_extns rsc
3572: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 3870: FROM jtf_rs_role_relations rlt,

3866: X_MSG_DATA OUT NOCOPY VARCHAR2)
3867: IS
3868: CURSOR c_child_role_relate_cur(l_group_id IN NUMBER) IS
3869: SELECT rlt.role_relate_id
3870: FROM jtf_rs_role_relations rlt,
3871: jtf_rs_group_members mem
3872: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
3873: AND rlt.role_resource_id = mem.group_member_id
3874: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 3882: FROM jtf_rs_role_relations rlt,

3878: r_child_role_relate_rec c_child_role_relate_cur%rowtype;
3879:
3880: CURSOR c_parent_role_relate_cur(l_parent_group_id IN NUMBER) IS
3881: SELECT rlt.role_relate_id
3882: FROM jtf_rs_role_relations rlt,
3883: jtf_rs_group_members mem
3884: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
3885: AND rlt.role_resource_id = mem.group_member_id
3886: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 4026: jtf_rs_role_relations rlt,

4022: rlt.end_date_active,
4023: rol.admin_flag ,
4024: rol.manager_flag
4025: FROM jtf_rs_group_members mem,
4026: jtf_rs_role_relations rlt,
4027: jtf_rs_roles_B rol
4028: WHERE mem.group_id IN ( select distinct(parent_group_id)
4029: from jtf_rs_groups_denorm
4030: where group_id = l_group_id)

Line 4052: p_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

4048: TYPE MGR_TYPE IS RECORD
4049: ( p_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
4050: p_person_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
4051: p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
4052: p_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4053: p_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
4054: p_start_date_active DATE,
4055: p_end_date_active DATE,
4056: p_admin_flag JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,

Line 4095: jtf_rs_role_relations rlt,

4091: rol.lead_flag ,
4092: rol.manager_flag,
4093: rsc.category
4094: FROM jtf_rs_group_members mem,
4095: jtf_rs_role_relations rlt,
4096: jtf_rs_roles_B rol,
4097: jtf_rs_resource_extns rsc
4098: WHERE mem.group_id = l_group_id
4099: AND mem.group_member_id = rlt.role_resource_id

Line 4126: jtf_rs_role_relations rlt,

4122: rol.lead_flag ,
4123: rol.manager_flag,
4124: rsc.category
4125: FROM jtf_rs_group_members mem,
4126: jtf_rs_role_relations rlt,
4127: jtf_rs_roles_B rol,
4128: jtf_rs_resource_extns rsc
4129: WHERE mem.group_id = l_group_id
4130: AND mem.group_member_id = rlt.role_resource_id

Line 4156: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

4152: FROM dual;
4153:
4154: --dupliacte check cursor to be added
4155:
4156: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4157: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4158: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
4159: IS
4160: SELECT person_id

Line 4157: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

4153:
4154: --dupliacte check cursor to be added
4155:
4156: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4157: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4158: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
4159: IS
4160: SELECT person_id
4161: FROM jtf_rs_rep_managers