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.12010000.2 2009/02/17 06:36:08 rgokavar 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 19: /FOR INSERT IN JTF_RS_ROLE_RELATIONS /

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

Line 24: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

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

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

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

Line 49: FROM jtf_rs_role_relations rlt,

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

Line 69: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

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

Line 83: FROM jtf_rs_role_relations rlt,

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

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

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

Line 133: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

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

Line 159: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

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

Line 170: jtf_rs_role_relations rlt,

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

Line 227: jtf_rs_role_relations rlt,

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

Line 257: jtf_rs_role_relations rlt,

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

Line 313: FROM jtf_rs_role_relations rlt,

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

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

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

Line 1149: /FOR INSERT IN JTF_RS_ROLE_RELATIONS /

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

Line 1154: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

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

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

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

Line 1179: FROM jtf_rs_role_relations rlt,

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

Line 1199: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

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

Line 1213: FROM jtf_rs_role_relations rlt,

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

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

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

Line 1256: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

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

Line 1273: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

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

Line 1284: jtf_rs_role_relations rlt,

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

Line 1338: jtf_rs_role_relations rlt,

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

Line 1368: jtf_rs_role_relations rlt,

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

Line 1421: FROM jtf_rs_role_relations rlt,

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

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

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

Line 2546: --FOR UPDATE on jtf_rs_role_relations

2542:
2543:
2544:
2545:
2546: --FOR UPDATE on jtf_rs_role_relations
2547: /*********************/
2548: --Bug8261683
2549: --Deletion logic changed from Row by row Delete to Bulk Delete(Single delete statement).
2550: --Commented corresponding Cursor denorm_cur and loop in the body for deletion.

Line 2556: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

2552: PROCEDURE UPDATE_REP_MANAGER(
2553: P_API_VERSION IN NUMBER,
2554: P_INIT_MSG_LIST IN VARCHAR2,
2555: P_COMMIT IN VARCHAR2,
2556: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2557: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2558: X_MSG_COUNT OUT NOCOPY NUMBER,
2559: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2560: IS

Line 2561: /* CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

2557: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2558: X_MSG_COUNT OUT NOCOPY NUMBER,
2559: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2560: IS
2561: /* CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2562: IS
2563: SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
2564: FROM jtf_rs_rep_managers den
2565: WHERE par_role_relate_id = l_role_relate_id

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

2566: OR child_role_relate_id = l_role_relate_id;
2567:
2568: denorm_rec denorm_cur%rowtype;
2569: */
2570: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
2571:
2572: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
2573: l_api_version CONSTANT NUMBER :=1.0;
2574: l_date Date;

Line 2698: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

2694: PROCEDURE DELETE_MEMBERS(
2695: P_API_VERSION IN NUMBER,
2696: P_INIT_MSG_LIST IN VARCHAR2,
2697: P_COMMIT IN VARCHAR2,
2698: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2699: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2700: X_MSG_COUNT OUT NOCOPY NUMBER,
2701: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2702: IS

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

2699: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2700: X_MSG_COUNT OUT NOCOPY NUMBER,
2701: X_MSG_DATA OUT NOCOPY VARCHAR2 )
2702: IS
2703: CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2704: IS
2705: SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
2706: FROM jtf_rs_rep_managers den
2707: WHERE par_role_relate_id = l_role_relate_id

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

2708: OR child_role_relate_id = l_role_relate_id;
2709:
2710: denorm_rec denorm_cur%rowtype;
2711:
2712: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
2713:
2714: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
2715: l_api_version CONSTANT NUMBER :=1.0;
2716: l_date Date;

Line 2844: FROM JTF_RS_ROLE_RELATIONS rlt,

2840:
2841: CURSOR par_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 2857: FROM JTF_RS_ROLE_RELATIONS rlt,

2853:
2854: CURSOR child_role_relate_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
2855: IS
2856: SELECT role_relate_id
2857: FROM JTF_RS_ROLE_RELATIONS rlt,
2858: jtf_rs_group_members mem
2859: WHERE mem.group_id = l_group_id
2860: AND mem.group_member_id = rlt.role_resource_id
2861: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

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

2864:
2865: child_role_relate_rec child_role_relate_cur%rowtype;
2866:
2867:
2868: CURSOR rep_denorm_cur(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2869: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2870: IS
2871: SELECT denorm_mgr_id
2872: FROM jtf_rs_rep_managers

Line 2869: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)

2865: child_role_relate_rec child_role_relate_cur%rowtype;
2866:
2867:
2868: CURSOR rep_denorm_cur(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2869: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2870: IS
2871: SELECT denorm_mgr_id
2872: FROM jtf_rs_rep_managers
2873: WHERE par_role_relate_id = l_par_role_relate_id

Line 3022: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

3018: PROCEDURE INSERT_REP_MGR_PARENT(
3019: P_API_VERSION IN NUMBER,
3020: P_INIT_MSG_LIST IN VARCHAR2,
3021: P_COMMIT IN VARCHAR2,
3022: P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3023: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3024: X_MSG_COUNT OUT NOCOPY NUMBER,
3025: X_MSG_DATA OUT NOCOPY VARCHAR2 )
3026: IS

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

3029: SELECT jtf_rs_rep_managers_s.nextval
3030: FROM dual;
3031:
3032:
3033: CURSOR mem_dtls_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
3034: IS
3035: SELECT mem.resource_id,
3036: mem.person_id,
3037: mem.group_id,

Line 3047: FROM jtf_rs_role_relations rlt,

3043: rol.lead_flag ,
3044: rol.manager_flag,
3045: rsc.category,
3046: rlt.role_relate_id
3047: FROM jtf_rs_role_relations rlt,
3048: jtf_rs_group_members mem,
3049: jtf_rs_roles_B rol,
3050: jtf_rs_resource_extns rsc
3051: WHERE rlt.role_relate_id = l_role_relate_id

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

3078: AND start_date_active = l_start_date_active
3079: AND (end_date_active = l_end_date_active
3080: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
3081:
3082: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3083: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3084: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
3085: IS
3086: SELECT person_id

Line 3083: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

3079: AND (end_date_active = l_end_date_active
3080: OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
3081:
3082: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3083: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
3084: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
3085: IS
3086: SELECT person_id
3087: FROM jtf_rs_rep_managers

Line 3128: jtf_rs_role_relations rlt,

3124: rlt.start_date_active,
3125: rlt.end_date_active,
3126: rlt.role_relate_id
3127: FROM jtf_rs_group_members mem,
3128: jtf_rs_role_relations rlt,
3129: jtf_rs_roles_b rol
3130: WHERE mem.group_id = l_group_id
3131: AND mem.group_member_id = rlt.role_resource_id
3132: AND rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 3158: jtf_rs_role_relations rlt,

3154: rlt.start_date_active,
3155: rlt.end_date_active,
3156: rlt.role_relate_id
3157: FROM jtf_rs_group_members mem,
3158: jtf_rs_role_relations rlt,
3159: jtf_rs_roles_b rol
3160: WHERE mem.group_id = l_group_id
3161: AND mem.group_member_id = rlt.role_resource_id
3162: AND nvl(rlt.delete_flag,'N') <> 'Y'

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

3175: mgr_rec mgr_cur%rowtype;
3176:
3177:
3178:
3179: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
3180: l_hierarchy_type JTF_RS_REP_MANAGERS.HIERARCHY_TYPE%TYPE;
3181: l_reports_to_flag JTF_RS_REP_MANAGERS.REPORTS_TO_FLAG%TYPE;
3182: l_denorm_mgr_id JTF_RS_REP_MANAGERS.DENORM_MGR_ID%TYPE;
3183: x_row_id VARCHAR2(100);

Line 3553: FROM jtf_rs_role_relations rlt,

3549: DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
3550: nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
3551: rsc.category,
3552: rlt.role_relate_id
3553: FROM jtf_rs_role_relations rlt,
3554: jtf_rs_group_members mem,
3555: jtf_rs_roles_B rol,
3556: jtf_rs_resource_extns rsc
3557: WHERE mem.group_id = p_group_id

Line 3581: FROM jtf_rs_role_relations rlt,

3577: DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
3578: nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
3579: rsc.category,
3580: rlt.role_relate_id
3581: FROM jtf_rs_role_relations rlt,
3582: jtf_rs_group_members mem,
3583: jtf_rs_roles_B rol,
3584: jtf_rs_resource_extns rsc
3585: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'

Line 3883: FROM jtf_rs_role_relations rlt,

3879: X_MSG_DATA OUT NOCOPY VARCHAR2)
3880: IS
3881: CURSOR c_child_role_relate_cur(l_group_id IN NUMBER) IS
3882: SELECT rlt.role_relate_id
3883: FROM jtf_rs_role_relations rlt,
3884: jtf_rs_group_members mem
3885: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
3886: AND rlt.role_resource_id = mem.group_member_id
3887: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 3895: FROM jtf_rs_role_relations rlt,

3891: r_child_role_relate_rec c_child_role_relate_cur%rowtype;
3892:
3893: CURSOR c_parent_role_relate_cur(l_parent_group_id IN NUMBER) IS
3894: SELECT rlt.role_relate_id
3895: FROM jtf_rs_role_relations rlt,
3896: jtf_rs_group_members mem
3897: WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
3898: AND rlt.role_resource_id = mem.group_member_id
3899: AND nvl(rlt.delete_flag,'N') <> 'Y'

Line 4039: jtf_rs_role_relations rlt,

4035: rlt.end_date_active,
4036: rol.admin_flag ,
4037: rol.manager_flag
4038: FROM jtf_rs_group_members mem,
4039: jtf_rs_role_relations rlt,
4040: jtf_rs_roles_B rol
4041: WHERE mem.group_id IN ( select distinct(parent_group_id)
4042: from jtf_rs_groups_denorm
4043: where group_id = l_group_id)

Line 4065: p_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

4061: TYPE MGR_TYPE IS RECORD
4062: ( p_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
4063: p_person_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
4064: p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
4065: p_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4066: p_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
4067: p_start_date_active DATE,
4068: p_end_date_active DATE,
4069: p_admin_flag JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,

Line 4108: jtf_rs_role_relations rlt,

4104: rol.lead_flag ,
4105: rol.manager_flag,
4106: rsc.category
4107: FROM jtf_rs_group_members mem,
4108: jtf_rs_role_relations rlt,
4109: jtf_rs_roles_B rol,
4110: jtf_rs_resource_extns rsc
4111: WHERE mem.group_id = l_group_id
4112: AND mem.group_member_id = rlt.role_resource_id

Line 4139: jtf_rs_role_relations rlt,

4135: rol.lead_flag ,
4136: rol.manager_flag,
4137: rsc.category
4138: FROM jtf_rs_group_members mem,
4139: jtf_rs_role_relations rlt,
4140: jtf_rs_roles_B rol,
4141: jtf_rs_resource_extns rsc
4142: WHERE mem.group_id = l_group_id
4143: AND mem.group_member_id = rlt.role_resource_id

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

4165: FROM dual;
4166:
4167: --dupliacte check cursor to be added
4168:
4169: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4170: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4171: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
4172: IS
4173: SELECT person_id

Line 4170: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,

4166:
4167: --dupliacte check cursor to be added
4168:
4169: CURSOR dup_cur2(l_par_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4170: l_child_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
4171: l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
4172: IS
4173: SELECT person_id
4174: FROM jtf_rs_rep_managers