[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_REP_MGR_DENORM_PVT
Source
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
10 -- 3 Jul 2001 S Roy Choudhury Modified the cursor for selecting members in procedure INSERT_GRP_RELATIONS
11 -- to fix the dates. Also added the posting of reverse records for MGR_TO_MGR
12 -- hierarchy type.
13 -- Notes:
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,
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
28 CURSOR rep_mgr_seq_cur
29 IS
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,
39 rlt.role_id,
40 rlt.start_date_active,
41 rlt.end_date_active,
42 rol.member_flag ,
43 rol.admin_flag ,
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
53 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
54 AND rlt.role_resource_id = mem.group_member_id
55 AND rlt.role_id = rol.role_id
56 AND nvl(rlt.delete_flag,'N') <> 'Y'
57 AND nvl(mem.delete_flag,'N') <> 'Y'
58 AND mem.resource_id = rsc.resource_id;
59
60
61 mem_dtls_rec mem_dtls_cur%rowtype;
62
63 --CURSOR for other members in same group
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,
73 rlt.role_id,
74 rlt.start_date_active,
75 rlt.end_date_active,
76 rol.member_flag ,
77 rol.admin_flag ,
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
87 AND mem.group_member_id = rlt.role_resource_id
88 AND nvl(rlt.delete_flag,'N') <> 'Y'
89 AND nvl(mem.delete_flag,'N') <> 'Y'
90 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
91 AND rlt.role_relate_id <> l_role_relate_id
92 /* AND ((rlt.start_date_active between l_start_date_active and
93 nvl(l_end_date_active,rlt.start_date_active+1))
94 OR (rlt.end_date_active between l_start_date_active
95 and nvl(l_end_date_active,rlt.end_date_active+1))
96 OR ((rlt.start_date_active <= l_start_date_active)
97 AND (rlt.end_date_active >= l_end_date_active
98 OR l_end_date_active IS NULL))) */
99 AND rlt.role_id = rol.role_id
100 --added to eliminate managers
101 AND nvl(rol.manager_flag , 'N') <> 'Y'
102 AND (
103 nvl(rol.admin_flag, 'N') = 'Y'
104 OR
105 nvl(rol.member_flag, 'N') = 'Y'
106 )
107 AND mem.resource_id = rsc.resource_id;
108
109
110 other_rec other_cur%rowtype;
111
112 --cursor for duplicate check
113 CURSOR dup_cur(l_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
114 l_manager_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
115 l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
116 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
117 l_start_date_active DATE,
118 l_end_date_active DATE)
119 IS
120 SELECT person_id
121 FROM jtf_rs_rep_managers
122 WHERE group_id = l_group_id
123 AND ( person_id = l_person_id
124 OR (l_person_id IS NULL AND person_id IS NULL))
125 AND manager_person_id = l_manager_person_id
126 AND resource_id = l_resource_id
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)
136 IS
137 SELECT person_id
138 FROM jtf_rs_rep_managers
139 WHERE par_role_relate_id = l_par_role_relate_id
140 AND child_role_relate_id = l_child_role_relate_id
141 AND group_id = l_group_id
142 AND ((l_start_date_active between start_date_active and
143 nvl(end_date_active,l_start_date_active+1))
144 OR (l_end_date_active between start_date_active
145 and nvl(end_date_active,l_end_date_active+1))
146 OR ((l_start_date_active <= start_date_active)
147 AND (l_end_date_active >= end_date_active
148 OR l_end_date_active IS NULL)));
149
150
151
152 dup NUMBER := 0;
153
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,
163 rlt.start_date_active,
164 rlt.end_date_active,
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'
174 AND rlt.role_relate_id <> l_role_relate_id
175 AND nvl(rlt.delete_flag,'N') <> 'Y'
176 AND nvl(mem.delete_flag,'N') <> 'Y'
177 --AND rlt.role_relate_id <> l_role_relate_id
178 /* AND ((l_start_date_active between rlt.start_date_active
179 and nvl(rlt.end_date_active , l_start_date_active +1))
180 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
181 between rlt.start_date_active and
182 nvl(rlt.end_date_active, l_end_date_active + 1))
183 or (l_end_date_active is null and rlt.end_date_active is null))) */
184 AND rlt.role_id = rol.role_id
185 AND nvl(rol.manager_flag , 'N') = 'Y';
186
187 same_grp_mgr_admin_rec same_grp_mgr_admin_cur%ROWTYPE;
188
189
190 --cursor for parent groups
191 CURSOR par_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
192 l_start_date_active DATE,
193 l_end_date_active DATE)
194 IS
195 SELECT parent_group_id,
196 immediate_parent_flag,
197 start_date_active,
198 end_date_active,
199 denorm_level
200 FROM jtf_rs_groups_denorm
201 WHERE group_id = l_group_id
202 AND parent_group_id <> l_group_id
203 /* AND ((l_start_date_active between start_date_active
204 and nvl(end_date_active , l_start_date_active +1))
205 OR ((nvl(l_end_date_active, start_date_active +1)
206 between start_date_active and
207 nvl(end_date_active, l_end_date_active + 1))
208 or (l_end_date_active is null and end_date_active is null)))*/
209 ;
210
211 par_grp_rec par_grp_cur%ROWTYPE;
212
213
214 --cursor to fetch admin for a group
215 CURSOR admin_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
216 l_start_date_active DATE,
217 l_end_date_active DATE)
218 IS
219 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
220 mem.resource_id,
221 mem.person_id,
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'
231 AND nvl(rlt.delete_flag,'N') <> 'Y'
232 AND nvl(mem.delete_flag,'N') <> 'Y'
233 AND rlt.role_id = rol.role_id
234 AND rol.admin_flag = 'Y'
235 AND ((l_start_date_active between rlt.start_date_active
236 and nvl(rlt.end_date_active , l_start_date_active +1))
237 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
238 between rlt.start_date_active and
239 nvl(rlt.end_date_active, l_end_date_active + 1))
240 or (l_end_date_active is null and rlt.end_date_active is null)));
241
242 admin_rec admin_cur%rowtype;
243
244 --cursor to fetch managers for a group
245 CURSOR mgr_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
246 l_start_date_active DATE,
247 l_end_date_active DATE)
248 IS
249 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
250 mem.resource_id,
251 mem.person_id,
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'
261 AND nvl(mem.delete_flag,'N') <> 'Y'
262 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
263 AND rlt.role_id = rol.role_id
264 AND rol.manager_flag = 'Y' ;
265 /* AND ((l_start_date_active between rlt.start_date_active
266 and nvl(rlt.end_date_active , l_start_date_active +1))
267 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
268 between rlt.start_date_active and
269 nvl(rlt.end_date_active, l_end_date_active + 1))
270 or (l_end_date_active is null and rlt.end_date_active is null))); */
271
272
273 mgr_rec mgr_cur%rowtype;
274
275 --cursor for child groups
276 CURSOR child_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
277 l_start_date_active DATE,
278 l_end_date_active DATE)
279 IS
280 SELECT group_id,
281 immediate_parent_flag,
282 start_date_active,
283 end_date_active,
284 denorm_level
285 FROM jtf_rs_groups_denorm
286 WHERE parent_group_id = l_group_id
287 AND group_id <> l_group_id;
288 /* AND ((l_start_date_active between start_date_active
289 and nvl(end_date_active , l_start_date_active +1))
290 OR ((nvl(l_end_date_active, start_date_active +1)
291 between start_date_active and
292 nvl(end_date_active, l_end_date_active + 1))
293 or (l_end_date_active is null and end_date_active is null))); */
294
295 child_grp_rec child_grp_cur%rowtype;
296
297
298 --cursor for child group members
299 CURSOR child_mem_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
300 l_start_date_active DATE,
301 l_end_date_active DATE)
302 IS
303 SELECT mem.resource_id,
304 mem.person_id,
305 rlt.start_date_active,
306 rlt.end_date_active,
307 rol.manager_flag,
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
317 AND mem.group_member_id = rlt.role_resource_id
318 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
319 AND rlt.role_id = rol.role_id
320 AND ( nvl(rol.manager_flag,'N') = 'Y'
321 OR
322 nvl(rol.admin_flag, 'N') = 'Y'
323 OR
324 nvl(rol.member_flag, 'N') = 'Y')
325 --AND rlt.start_date_active <= l_start_date_active
326 /* AND ((l_start_date_active between rlt.start_date_active
327 and nvl(rlt.end_date_active , l_start_date_active +1))
328 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
329 between rlt.start_date_active and
330 nvl(rlt.end_date_active, l_end_date_active + 1))
331 or (l_end_date_active is null and rlt.end_date_active is null))) */
332 AND nvl(rlt.delete_flag,'N') <> 'Y'
333 AND nvl(mem.delete_flag,'N') <> 'Y'
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);
343
344 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MANAGER';
345 l_api_version CONSTANT NUMBER :=1.0;
346 l_date Date;
347 l_fnd_date Date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
348 l_user_id Number;
349 l_login_id Number;
350
351 l_start_date_active DATE;
352 l_end_date_active DATE;
353
354
355 l_count number := 0;
356
357 BEGIN
358 --Standard Start of API SAVEPOINT
359 SAVEPOINT member_denormalize;
360
361 x_return_status := fnd_api.g_ret_sts_success;
362
363 --Standard Call to check API compatibility
364 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
365 THEN
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
370 IF FND_API.To_boolean(P_INIT_MSG_LIST)
371 THEN
372 FND_MSG_PUB.Initialize;
373 END IF;
374
375
376 l_date := sysdate;
377 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
378 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
379
380 -- if no group id or person id is passed in then return
381 IF p_role_relate_id IS NULL
382 THEN
383 x_return_status := fnd_api.g_ret_sts_error;
384 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_RESOURCE_NULL');
385 FND_MSG_PUB.add;
386 RAISE fnd_api.g_exc_error;
387 END IF;
388
389
390 --fetch the member details
391 OPEN mem_dtls_cur(l_role_relate_id);
392 FETCH mem_dtls_cur INTO mem_dtls_rec;
393 IF((mem_dtls_cur%FOUND) AND
394 (nvl(mem_dtls_rec.manager_flag ,'N')= 'Y'
395 OR nvl(mem_dtls_rec.admin_flag, 'N') = 'Y'
396 OR nvl(mem_dtls_rec.member_flag, 'N') = 'Y'))
397 THEN
398 --duplicate check for the member record
399 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
400 mem_dtls_rec.role_relate_id,
401 mem_dtls_rec.group_id,
402 mem_dtls_rec.start_date_active,
403 mem_dtls_rec.end_date_active);
404
405 FETCH dup_cur2 INTO DUP;
406 IF (dup_cur2%NOTFOUND)
407 THEN
408 --set the hierarchy type for the record
409 IF mem_dtls_rec.manager_flag = 'Y'
410 THEN
411 l_hierarchy_type := 'MGR_TO_MGR';
412 ELSIF mem_dtls_rec.admin_flag = 'Y'
413 THEN
414 l_hierarchy_type := 'ADMIN_TO_ADMIN';
415 ELSE
416 l_hierarchy_type := 'REP_TO_REP';
417 END IF;
418
419 --call table handler to insert record in rep manager
420 l_reports_to_flag := 'N';
421
422 OPEN rep_mgr_seq_cur;
423 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
424 CLOSE rep_mgr_seq_cur;
425 jtf_rs_rep_managers_pkg.insert_row(
426 X_ROWID => x_row_id,
427 X_DENORM_MGR_ID => l_denorm_mgr_id,
428 X_RESOURCE_ID => mem_dtls_rec.resource_id,
429 X_PERSON_ID => mem_dtls_rec.person_id,
430 X_CATEGORY => mem_dtls_rec.category,
431 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
432 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
433 X_GROUP_ID => mem_dtls_rec.group_id,
434 X_REPORTS_TO_FLAG => l_reports_to_flag,
435 X_HIERARCHY_TYPE => l_hierarchy_type,
436 X_START_DATE_ACTIVE => trunc(mem_dtls_rec.start_date_active),
437 X_END_DATE_ACTIVE => trunc(mem_dtls_rec.end_date_active),
438 X_ATTRIBUTE2 => null,
439 X_ATTRIBUTE3 => null,
440 X_ATTRIBUTE4 => null,
441 X_ATTRIBUTE5 => null,
442 X_ATTRIBUTE6 => null,
443 X_ATTRIBUTE7 => null,
444 X_ATTRIBUTE8 => null,
445 X_ATTRIBUTE9 => null,
446 X_ATTRIBUTE10 => null,
447 X_ATTRIBUTE11 => null,
448 X_ATTRIBUTE12 => null,
449 X_ATTRIBUTE13 => null,
450 X_ATTRIBUTE14 => null,
451 X_ATTRIBUTE15 => null,
452 X_ATTRIBUTE_CATEGORY => null,
453 X_ATTRIBUTE1 => null,
454 X_CREATION_DATE => l_date,
455 X_CREATED_BY => l_user_id,
456 X_LAST_UPDATE_DATE => l_date,
457 X_LAST_UPDATED_BY => l_user_id,
458 X_LAST_UPDATE_LOGIN => l_login_id,
459 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
460 X_CHILD_ROLE_RELATE_ID => l_role_relate_id,
461 X_DENORM_LEVEL => 0);
462
463
464
465
466 IF fnd_api.to_boolean (p_commit)
467 THEN
468 l_count := l_count + 1;
469 if (l_count > 1000)
470 then
471 COMMIT WORK;
472 l_count := 0;
473 end if;
474 END IF;
475
476 END IF; --close of dup check
477 CLOSE dup_cur2;
478
479 --fetch managers in the same group
480 -- fetch this only if member is not manager
481 if(nvl(mem_dtls_rec.manager_flag , 'N')<> 'Y')
482 THEN
483 OPEN same_grp_mgr_admin_cur(mem_dtls_rec.group_id,
484 mem_dtls_rec.start_date_active,
485 mem_dtls_rec.end_date_active,
486 mem_dtls_rec.role_relate_id);
487
488 FETCH same_grp_mgr_admin_cur INTO same_grp_mgr_admin_rec;
489 l_reports_to_flag := 'Y';
490
491 WHILE(same_grp_mgr_admin_cur%FOUND)
492 LOOP
493
494 --assign start date and end date for which this relation is valid
495 IF(mem_dtls_rec.start_date_active < same_grp_mgr_admin_rec.start_date_active)
496 THEN
497 l_start_date_active := same_grp_mgr_admin_rec.start_date_active;
498 ELSE
499 l_start_date_active := mem_dtls_rec.start_date_active;
500 END IF;
501
502 l_end_date_active := least(nvl(to_date(to_char(mem_dtls_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date),
503 nvl(to_date(to_char(same_grp_mgr_admin_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date));
504 if(l_end_date_active = l_fnd_date)
505 then
506 l_end_date_active := null;
507 end if;
508 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
509 then
510 OPEN dup_cur2(same_grp_mgr_admin_rec.role_relate_id,
511 mem_dtls_rec.role_relate_id,
512 mem_dtls_rec.group_id,
513 l_start_date_active,
514 l_end_date_active);
515
516 FETCH dup_cur2 INTO DUP;
517 IF (dup_cur2%notfound)
518 THEN
519
520
521 --set the hierarchy type if of type manager
522 IF mem_dtls_rec.manager_flag = 'Y'
523 THEN
524 l_hierarchy_type := 'MGR_TO_MGR';
525 ELSIF mem_dtls_rec.admin_flag = 'Y'
526 THEN
527 l_hierarchy_type := 'MGR_TO_ADMIN';
528 ELSE
529 l_hierarchy_type := 'MGR_TO_REP';
530 END IF;
531
532 --INSERT INTO TABLE
533 OPEN rep_mgr_seq_cur;
534 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
535 CLOSE rep_mgr_seq_cur;
536 jtf_rs_rep_managers_pkg.insert_row(
537 X_ROWID => x_row_id,
538 X_DENORM_MGR_ID => l_denorm_mgr_id,
539 X_RESOURCE_ID => mem_dtls_rec.resource_id,
540 X_PERSON_ID => mem_dtls_rec.person_id,
541 X_CATEGORY => mem_dtls_rec.category,
542 X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
543 X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
544 X_GROUP_ID => mem_dtls_rec.group_id,
545 X_REPORTS_TO_FLAG => l_reports_to_flag,
546 X_HIERARCHY_TYPE => l_hierarchy_type,
547 X_START_DATE_ACTIVE => trunc(l_start_date_active),
548 X_END_DATE_ACTIVE => trunc(l_end_date_active),
549 X_ATTRIBUTE2 => null,
550 X_ATTRIBUTE3 => null,
551 X_ATTRIBUTE4 => null,
552 X_ATTRIBUTE5 => null,
553 X_ATTRIBUTE6 => null,
554 X_ATTRIBUTE7 => null,
555 X_ATTRIBUTE8 => null,
556 X_ATTRIBUTE9 => null,
557 X_ATTRIBUTE10 => null,
558 X_ATTRIBUTE11 => null,
559 X_ATTRIBUTE12 => null,
560 X_ATTRIBUTE13 => null,
561 X_ATTRIBUTE14 => null,
562 X_ATTRIBUTE15 => null,
563 X_ATTRIBUTE_CATEGORY => null,
564 X_ATTRIBUTE1 => null,
565 X_CREATION_DATE => l_date,
566 X_CREATED_BY => l_user_id,
567 X_LAST_UPDATE_DATE => l_date,
568 X_LAST_UPDATED_BY => l_user_id,
569 X_LAST_UPDATE_LOGIN => l_login_id,
570 X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
571 X_CHILD_ROLE_RELATE_ID => l_role_relate_id,
572 X_DENORM_LEVEL => 0);
573
574 IF fnd_api.to_boolean (p_commit)
575 THEN
576 l_count := l_count + 1;
577 if (l_count > 1000)
578 then
579 COMMIT WORK;
580 l_count := 0;
581 end if;
582 END IF;
583
584 end if; -- end of dup check
585 close dup_cur2;
586 END IF; -- end of st dt < end dt check
587 FETCH same_grp_mgr_admin_cur INTO same_grp_mgr_admin_rec;
588 END LOOP; -- end of same_grp_mgr_admin_cur
589 close same_grp_mgr_admin_cur;
590 END IF; -- end of manager flag check for member
591 --IF MEMBER IS OF TYPE MANAGER THEN INSERT RECORDS FOR THE OTHER MEMBERS OF THE GROUP
592 IF(mem_dtls_rec.manager_flag = 'Y' )
593 THEN
594 OPEN other_cur(mem_dtls_rec.group_id,
595 mem_dtls_rec.start_date_active,
596 mem_dtls_rec.end_date_active,
597 mem_dtls_rec.role_relate_id);
598
599 FETCH other_cur INTO other_rec;
600 WHILE (other_cur%FOUND)
601 LOOP
602
603 --assign start date and end date for which this relation is valid
604 IF(mem_dtls_rec.start_date_active < other_rec.start_date_active)
605 THEN
606 l_start_date_active := other_rec.start_date_active;
607 ELSE
608 l_start_date_active := mem_dtls_rec.start_date_active;
609 END IF;
610
611 l_end_date_active := least(nvl(to_date(to_char(mem_dtls_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR'), l_fnd_date),
612 nvl(to_date(to_char(other_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR'), l_fnd_date));
613 if(l_end_date_active = l_fnd_date)
614 then
615 l_end_date_active := null;
616 end if;
617 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
618 then
619 --duplicate check
620 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
621 other_rec.role_relate_id,
622 mem_dtls_rec.group_id,
623 l_start_date_active,
624 l_end_date_active);
625
626 FETCH dup_cur2 INTO DUP;
627 IF (dup_cur2%NOTFOUND)
628 THEN
629
630 l_reports_to_flag := 'Y';
631 --IF mem_dtls_rec.manager_flag = 'Y'
632 --THEN
633 IF other_rec.manager_flag = 'Y'
634 THEN
635 l_hierarchy_type := 'MGR_TO_MGR';
636 ELSIF other_rec.admin_flag = 'Y'
637 THEN
638 l_hierarchy_type := 'MGR_TO_ADMIN';
639 ELSE
640 l_hierarchy_type := 'MGR_TO_REP';
641 END IF;
642
643 --call table handler
644
645 --INSERT INTO TABLE
646 OPEN rep_mgr_seq_cur;
647 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
648 CLOSE rep_mgr_seq_cur;
649
650 jtf_rs_rep_managers_pkg.insert_row(
651 X_ROWID => x_row_id,
652 X_DENORM_MGR_ID => l_denorm_mgr_id,
653 X_RESOURCE_ID =>other_rec.resource_id,
654 X_PERSON_ID =>other_rec.person_id,
655 X_CATEGORY => other_rec.category,
656 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
657 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
658 X_GROUP_ID => mem_dtls_rec.group_id,
659 X_REPORTS_TO_FLAG => l_reports_to_flag,
660 X_HIERARCHY_TYPE => l_hierarchy_type,
661 X_START_DATE_ACTIVE => trunc(l_start_date_active),
662 X_END_DATE_ACTIVE => trunc(l_end_date_active),
663 X_ATTRIBUTE2 => null,
664 X_ATTRIBUTE3 => null,
665 X_ATTRIBUTE4 => null,
666 X_ATTRIBUTE5 => null,
667 X_ATTRIBUTE6 => null,
668 X_ATTRIBUTE7 => null,
669 X_ATTRIBUTE8 => null,
670 X_ATTRIBUTE9 => null,
671 X_ATTRIBUTE10 => null,
672 X_ATTRIBUTE11 => null,
673 X_ATTRIBUTE12 => null,
674 X_ATTRIBUTE13 => null,
675 X_ATTRIBUTE14 => null,
676 X_ATTRIBUTE15 => null,
677 X_ATTRIBUTE_CATEGORY => null,
678 X_ATTRIBUTE1 => null,
679 X_CREATION_DATE => l_date,
680 X_CREATED_BY => l_user_id,
681 X_LAST_UPDATE_DATE => l_date,
682 X_LAST_UPDATED_BY => l_user_id,
683 X_LAST_UPDATE_LOGIN => l_login_id,
684 X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
685 X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id,
686 X_DENORM_LEVEL => 0);
687
688 IF fnd_api.to_boolean (p_commit)
689 THEN
690 l_count := l_count + 1;
691 if (l_count > 1000)
692 then
693 COMMIT WORK;
694 l_count := 0;
695 end if;
696 END IF;
697
698 end if; --end of dup check
699 close dup_cur2;
700 end if; --end of st dt < end dt check
701
702 FETCH other_cur INTO other_rec;
703 END LOOP; -- END OF OTHER_CUR
704 close other_cur;
705 END IF; -- end of manager flag check
706
707 --fetch all the parent groups for the group
708 OPEN par_grp_cur(mem_dtls_rec.group_id,
709 mem_dtls_rec.start_date_active,
710 mem_dtls_rec.end_date_active);
711
712 FETCH par_grp_cur INTO par_grp_rec;
713 WHILE (par_grp_cur%FOUND)
714 LOOP
715
716 IF((par_grp_rec.immediate_parent_flag = 'Y')
717 AND (nvl(mem_dtls_rec.manager_flag,'N')='Y' ))
718 THEN
719 l_reports_to_flag := 'Y';
720 ELSE
721 l_reports_to_flag := 'N';
722 END IF;
723 --fetch all managers
724 OPEN mgr_cur(par_grp_rec.parent_group_id,
725 mem_dtls_rec.start_date_active,
726 mem_dtls_rec.end_date_active);
727 FETCH mgr_cur INTO mgr_rec;
728 WHILE (mgr_cur%FOUND)
729 LOOP
730
731 IF mem_dtls_rec.manager_flag = 'Y'
732 THEN
733 l_hierarchy_type := 'MGR_TO_MGR';
734 ELSIF mem_dtls_rec.admin_flag = 'Y'
735 THEN
736 l_hierarchy_type := 'MGR_TO_ADMIN';
737 ELSE
738 l_hierarchy_type := 'MGR_TO_REP';
739 END IF;
740
741
742
743
744 l_start_date_active := greatest(trunc(mem_dtls_rec.start_date_active),
745 trunc(mgr_rec.start_date_active),
746 trunc(par_grp_rec.start_date_active));
747 l_end_date_active := least(nvl(to_date(to_char(mem_dtls_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date),
748 nvl(to_date(to_char(mgr_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date),
749 nvl(to_date(to_char(par_grp_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date));
750 if(l_end_date_active = l_fnd_date)
751 then
752 l_end_date_active := null;
753 end if;
754
755 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
756 then
757 --call table handler
758 OPEN dup_cur2(mgr_rec.role_relate_id,
759 mem_dtls_rec.role_relate_id,
760 mem_dtls_rec.group_id,
761 l_start_date_active,
762 l_end_date_active);
763
764 FETCH dup_cur2 INTO DUP;
765 IF (dup_cur2%notfound)
766 THEN
767 --INSERT INTO TABLE
768 OPEN rep_mgr_seq_cur;
769 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
770 CLOSE rep_mgr_seq_cur;
771
772 jtf_rs_rep_managers_pkg.insert_row(
773 X_ROWID => x_row_id,
774 X_DENORM_MGR_ID => l_denorm_mgr_id,
775 X_RESOURCE_ID => mem_dtls_rec.resource_id,
776 X_PERSON_ID => mem_dtls_rec.person_id,
777 X_CATEGORY => mem_dtls_rec.category,
778 X_MANAGER_PERSON_ID => mgr_rec.person_id,
779 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
780 X_GROUP_ID => mem_dtls_rec.group_id,
781 X_HIERARCHY_TYPE => l_hierarchy_type,
782 X_REPORTS_TO_FLAG => l_reports_to_flag,
783 X_START_DATE_ACTIVE => trunc(l_start_date_active),
784 X_END_DATE_ACTIVE => trunc(l_end_date_active),
785 X_ATTRIBUTE2 => null,
786 X_ATTRIBUTE3 => null,
787 X_ATTRIBUTE4 => null,
788 X_ATTRIBUTE5 => null,
789 X_ATTRIBUTE6 => null,
790 X_ATTRIBUTE7 => null,
791 X_ATTRIBUTE8 => null,
792 X_ATTRIBUTE9 => null,
793 X_ATTRIBUTE10 => null,
794 X_ATTRIBUTE11 => null,
795 X_ATTRIBUTE12 => null,
796 X_ATTRIBUTE13 => null,
797 X_ATTRIBUTE14 => null,
798 X_ATTRIBUTE15 => null,
799 X_ATTRIBUTE_CATEGORY => null,
800 X_ATTRIBUTE1 => null,
801 X_CREATION_DATE => l_date,
802 X_CREATED_BY => l_user_id,
803 X_LAST_UPDATE_DATE => l_date,
804 X_LAST_UPDATED_BY => l_user_id,
805 X_LAST_UPDATE_LOGIN => l_login_id,
806 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
807 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id,
808 X_DENORM_LEVEL => par_grp_rec.denorm_level);
809
810 IF fnd_api.to_boolean (p_commit)
811 THEN
812 l_count := l_count + 1;
813 if (l_count > 1000)
814 then
815 COMMIT WORK;
816 l_count := 0;
817 end if;
818 END IF;
819
820 END IF; -- END OF DUP CHECK
821 CLOSE dup_cur2;
822
823
824 --for manager the oppsite record has to be inserted
825 IF mem_dtls_rec.manager_flag = 'Y'
826 THEN
827 --insert for group_id = parent_group_id
828 --call to table handler
829 OPEN dup_cur2(mgr_rec.role_relate_id,
830 mem_dtls_rec.role_relate_id,
831 par_grp_rec.parent_group_id,
832 l_start_date_active,
833 l_end_date_active);
834
835 FETCH dup_cur2 INTO DUP;
836 IF (dup_cur2%notfound)
837 THEN
838 --INSERT INTO TABLE
839 OPEN rep_mgr_seq_cur;
840 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
841 CLOSE rep_mgr_seq_cur;
842
843 jtf_rs_rep_managers_pkg.insert_row(
844 X_ROWID => x_row_id,
845 X_DENORM_MGR_ID => l_denorm_mgr_id,
846 X_RESOURCE_ID => mem_dtls_rec.resource_id,
847 X_PERSON_ID => mem_dtls_rec.person_id,
848 X_CATEGORY => mem_dtls_rec.category,
849 X_MANAGER_PERSON_ID => mgr_rec.person_id,
850 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
851 X_GROUP_ID => par_grp_rec.parent_group_id,
852 X_REPORTS_TO_FLAG => l_reports_to_flag,
853 X_HIERARCHY_TYPE => l_hierarchy_type,
854 X_START_DATE_ACTIVE => trunc(l_start_date_active),
855 X_END_DATE_ACTIVE => trunc(l_end_date_active),
856 X_ATTRIBUTE2 => null,
857 X_ATTRIBUTE3 => null,
858 X_ATTRIBUTE4 => null,
859 X_ATTRIBUTE5 => null,
860 X_ATTRIBUTE6 => null,
861 X_ATTRIBUTE7 => null,
862 X_ATTRIBUTE8 => null,
863 X_ATTRIBUTE9 => null,
864 X_ATTRIBUTE10 => null,
865 X_ATTRIBUTE11 => null,
866 X_ATTRIBUTE12 => null,
867 X_ATTRIBUTE13 => null,
868 X_ATTRIBUTE14 => null,
869 X_ATTRIBUTE15 => null,
870 X_ATTRIBUTE_CATEGORY => null,
871 X_ATTRIBUTE1 => null,
872 X_CREATION_DATE => l_date,
873 X_CREATED_BY => l_user_id,
874 X_LAST_UPDATE_DATE => l_date,
875 X_LAST_UPDATED_BY => l_user_id,
876 X_LAST_UPDATE_LOGIN => l_login_id,
877 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
878 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id,
879 X_DENORM_LEVEL => par_grp_rec.denorm_level);
880
881 IF fnd_api.to_boolean (p_commit)
882 THEN
883 l_count := l_count + 1;
884 if (l_count > 1000)
885 then
886 COMMIT WORK;
887 l_count := 0;
888 end if;
889 END IF;
890
891 end if; --end of dup check
892 CLOSE dup_cur2;
893 END IF; --end of mgr flag check for inserting opp rec
894 END IF; -- end of st date check
895 FETCH mgr_cur INTO mgr_rec;
896 END LOOP;
897 CLOSE mgr_cur;
898 FETCH par_grp_cur INTO par_grp_rec;
899 END LOOP;
900 CLOSE par_grp_cur;
901 --for managers get child groups and insert records for each of the members
902 IF((mem_dtls_rec.manager_flag = 'Y'))
903 THEN
904 --fetch all the parent groups for the group
905 OPEN child_grp_cur(mem_dtls_rec.group_id,
906 mem_dtls_rec.start_date_active,
907 mem_dtls_rec.end_date_active);
908
909 FETCH child_grp_cur INTO child_grp_rec;
910 WHILE (child_grp_cur%FOUND)
911 LOOP
912
913
914 --fetch all members
915 OPEN child_mem_cur(child_grp_rec.group_id,
916 mem_dtls_rec.start_date_active,
917 mem_dtls_rec.end_date_active);
918 FETCH child_mem_cur INTO child_mem_rec;
919 WHILE (child_mem_cur%FOUND)
920 LOOP
921
922 IF ((child_grp_rec.immediate_parent_flag = 'Y') AND
923 (child_mem_rec.manager_flag = 'Y'))
924 THEN
925 l_reports_to_flag := 'Y';
926 ELSE
927 l_reports_to_flag := 'N';
928 END IF;
929
930
931 IF mem_dtls_rec.manager_flag = 'Y'
932 THEN
933 IF(child_mem_rec.manager_flag = 'Y')
934 THEN
935 l_hierarchy_type := 'MGR_TO_MGR';
936 ELSIF(child_mem_rec.ADMIN_flag = 'Y')
937 THEN
938 l_hierarchy_type := 'MGR_TO_ADMIN';
939 ELSE
940 l_hierarchy_type := 'MGR_TO_REP';
941 END IF;
942 END IF;
943 l_start_date_active := greatest(trunc(mem_dtls_rec.start_date_active),
944 trunc(child_mem_rec.start_date_active),
945 trunc(child_grp_rec.start_date_active));
946 l_end_date_active := least(nvl(to_date(to_char(mem_dtls_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date),
947 nvl(to_date(to_char(child_mem_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date),
948 nvl(to_date(to_char(child_grp_rec.end_date_active, 'DD-MM-RRRR'),'DD-MM-RRRR'), l_fnd_date));
949
950 if(l_end_date_active = l_fnd_date)
951 then
952 l_end_date_active := null;
953 end if;
954
955 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
956 then
957 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
958 child_mem_rec.role_relate_id,
959 child_grp_rec.group_id,
960 l_start_date_active,
961 l_end_date_active);
962
963 FETCH dup_cur2 INTO DUP;
964 IF (dup_cur2%notfound)
965 THEN
966
967 --INSERT INTO TABLE
968 OPEN rep_mgr_seq_cur;
969 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
970 CLOSE rep_mgr_seq_cur;
971
972 jtf_rs_rep_managers_pkg.insert_row(
973 X_ROWID => x_row_id,
974 X_DENORM_MGR_ID => l_denorm_mgr_id,
975 X_RESOURCE_ID =>child_mem_rec.resource_id,
976 X_PERSON_ID => child_mem_rec.person_id,
977 X_CATEGORY => child_mem_rec.category,
978 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
979 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
980 X_GROUP_ID => child_grp_rec.group_id,
981 X_REPORTS_TO_FLAG => l_reports_to_flag,
982 X_HIERARCHY_TYPE => l_hierarchy_type,
983 X_START_DATE_ACTIVE => trunc(l_start_date_active),
984 X_END_DATE_ACTIVE => trunc(l_end_date_active),
985 X_ATTRIBUTE2 => null,
986 X_ATTRIBUTE3 => null,
987 X_ATTRIBUTE4 => null,
988 X_ATTRIBUTE5 => null,
989 X_ATTRIBUTE6 => null,
990 X_ATTRIBUTE7 => null,
991 X_ATTRIBUTE8 => null,
992 X_ATTRIBUTE9 => null,
993 X_ATTRIBUTE10 => null,
994 X_ATTRIBUTE11 => null,
995 X_ATTRIBUTE12 => null,
996 X_ATTRIBUTE13 => null,
997 X_ATTRIBUTE14 => null,
998 X_ATTRIBUTE15 => null,
999 X_ATTRIBUTE_CATEGORY => null,
1000 X_ATTRIBUTE1 => null,
1001 X_CREATION_DATE => l_date,
1002 X_CREATED_BY => l_user_id,
1003 X_LAST_UPDATE_DATE => l_date,
1004 X_LAST_UPDATED_BY => l_user_id,
1005 X_LAST_UPDATE_LOGIN => l_login_id,
1006 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
1007 X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id,
1008 X_DENORM_LEVEL => child_grp_rec.denorm_level);
1009
1010 IF fnd_api.to_boolean (p_commit)
1011 THEN
1012 l_count := l_count + 1;
1013 if (l_count > 1000)
1014 then
1015 COMMIT WORK;
1016 l_count := 0;
1017 end if;
1018 END IF;
1019
1020
1021 END IF; -- end of dup check
1022 CLOSE dup_cur2;
1023
1024 --for manager the opposite record has to be inserted
1025 IF child_mem_rec.manager_flag = 'Y'
1026 and mem_dtls_rec.manager_flag = 'Y'
1027 THEN
1028 --insert for group_id = parent_group_id
1029 --call to table handler
1030 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
1031 child_mem_rec.role_relate_id,
1032 mem_dtls_rec.group_id,
1033 l_start_date_active,
1034 l_end_date_active);
1035 FETCH dup_cur2 INTO DUP;
1036 IF (dup_cur2%notfound)
1037 THEN
1038
1039 OPEN rep_mgr_seq_cur;
1040 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1041 CLOSE rep_mgr_seq_cur;
1042
1043 jtf_rs_rep_managers_pkg.insert_row(
1044 X_ROWID => x_row_id,
1045 X_DENORM_MGR_ID => l_denorm_mgr_id,
1046 X_RESOURCE_ID =>child_mem_rec.resource_id,
1047 X_PERSON_ID => child_mem_rec.person_id,
1048 X_CATEGORY => child_mem_rec.category,
1049 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
1050 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
1051 X_GROUP_ID => mem_dtls_rec.group_id,
1052 X_REPORTS_TO_FLAG => l_reports_to_flag,
1053 X_HIERARCHY_TYPE => l_hierarchy_type,
1054 X_START_DATE_ACTIVE => trunc(l_start_date_active),
1055 X_END_DATE_ACTIVE => trunc(l_end_date_active),
1056 X_ATTRIBUTE2 => null,
1057 X_ATTRIBUTE3 => null,
1058 X_ATTRIBUTE4 => null,
1059 X_ATTRIBUTE5 => null,
1060 X_ATTRIBUTE6 => null,
1061 X_ATTRIBUTE7 => null,
1062 X_ATTRIBUTE8 => null,
1063 X_ATTRIBUTE9 => null,
1064 X_ATTRIBUTE10 => null,
1065 X_ATTRIBUTE11 => null,
1066 X_ATTRIBUTE12 => null,
1067 X_ATTRIBUTE13 => null,
1068 X_ATTRIBUTE14 => null,
1069 X_ATTRIBUTE15 => null,
1070 X_ATTRIBUTE_CATEGORY => null,
1071 X_ATTRIBUTE1 => null,
1072 X_CREATION_DATE => l_date,
1073 X_CREATED_BY => l_user_id,
1074 X_LAST_UPDATE_DATE => l_date,
1075 X_LAST_UPDATED_BY => l_user_id,
1076 X_LAST_UPDATE_LOGIN => l_login_id,
1077 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
1078 X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id,
1079 X_DENORM_LEVEL => child_grp_rec.denorm_level);
1080
1081 IF fnd_api.to_boolean (p_commit)
1082 THEN
1083 l_count := l_count + 1;
1084 if (l_count > 1000)
1085 then
1086 COMMIT WORK;
1087 l_count := 0;
1088 end if;
1089 END IF;
1090
1091 END IF; --end of dup check
1092 CLOSE dup_cur2;
1093 END IF; -- end of child mem mgr flag check
1094 END IF; --end of st dt check
1095
1096 FETCH child_mem_cur INTO child_mem_rec;
1097 END LOOP;
1098 CLOSE child_mem_cur;
1099
1100 FETCH child_grp_cur INTO child_grp_rec;
1101 END LOOP;
1102 CLOSE child_grp_cur;
1103 END IF; --end of child group members insert if mem mgr flag = Y
1104
1105 END IF;--end of member details found if statement
1106
1107
1108 CLOSE mem_dtls_cur;
1109
1110 --
1111 IF fnd_api.to_boolean (p_commit)
1112 THEN
1113 COMMIT WORK;
1114 END IF;
1115
1116 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1117
1118 EXCEPTION
1119 WHEN fnd_api.g_exc_unexpected_error
1120 THEN
1121 ROLLBACK TO member_denormalize;
1122 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
1123 --FND_MSG_PUB.add;
1124 --x_return_status := fnd_api.g_ret_sts_unexp_error;
1125 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1126 WHEN fnd_api.g_exc_error
1127 THEN
1128 ROLLBACK TO member_denormalize;
1129 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1130
1131 WHEN OTHERS
1132 THEN
1133 ROLLBACK TO member_denormalize;
1134 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1135 fnd_message.set_token('P_SQLCODE',SQLCODE);
1136 fnd_message.set_token('P_SQLERRM',SQLERRM);
1137 fnd_message.set_token('P_API_NAME',l_api_name);
1138 FND_MSG_PUB.add;
1139 x_return_status := fnd_api.g_ret_sts_unexp_error;
1140 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1141
1142
1143 END INSERT_REP_MANAGER;
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,
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
1158 CURSOR rep_mgr_seq_cur
1159 IS
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,
1169 rlt.role_id,
1170 rlt.start_date_active,
1171 rlt.end_date_active,
1172 rol.member_flag ,
1173 rol.admin_flag ,
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
1183 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1184 AND rlt.role_resource_id = mem.group_member_id
1185 AND rlt.role_id = rol.role_id
1186 AND nvl(rlt.delete_flag,'N') <> 'Y'
1187 AND nvl(mem.delete_flag,'N') <> 'Y'
1188 AND mem.resource_id = rsc.resource_id;
1189
1190
1191 mem_dtls_rec mem_dtls_cur%rowtype;
1192
1193 --CURSOR for other members in same group
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,
1203 rlt.role_id,
1204 rlt.start_date_active,
1205 rlt.end_date_active,
1206 rol.member_flag ,
1207 rol.admin_flag ,
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
1217 AND mem.group_member_id = rlt.role_resource_id
1218 AND nvl(rlt.delete_flag,'N') <> 'Y'
1219 AND nvl(mem.delete_flag,'N') <> 'Y'
1220 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1221 AND rlt.role_relate_id <> l_role_relate_id
1222 AND ((rlt.start_date_active between l_start_date_active and
1223 nvl(l_end_date_active,rlt.start_date_active+1))
1224 OR (rlt.end_date_active between l_start_date_active
1225 and nvl(l_end_date_active,rlt.end_date_active+1))
1226 OR ((rlt.start_date_active <= l_start_date_active)
1227 AND (rlt.end_date_active >= l_end_date_active
1228 OR l_end_date_active IS NULL)))
1229 AND rlt.role_id = rol.role_id
1230 AND mem.resource_id = rsc.resource_id;
1231
1232
1233 other_rec other_cur%rowtype;
1234
1235 --cursor for duplicate check
1236 CURSOR dup_cur(l_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
1237 l_manager_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
1238 l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1239 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
1240 l_start_date_active DATE,
1241 l_end_date_active DATE)
1242 IS
1243 SELECT person_id
1244 FROM jtf_rs_rep_managers
1245 WHERE group_id = l_group_id
1246 AND ( person_id = l_person_id
1247 OR (l_person_id IS NULL AND person_id IS NULL))
1248 AND manager_person_id = l_manager_person_id
1249 AND resource_id = l_resource_id
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
1259 FROM jtf_rs_rep_managers
1260 WHERE par_role_relate_id = l_par_role_relate_id
1261 AND child_role_relate_id = l_child_role_relate_id
1262 AND group_id = l_group_id;
1263
1264
1265
1266 dup NUMBER := 0;
1267
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,
1277 rlt.start_date_active,
1278 rlt.end_date_active,
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'
1288 AND rlt.role_relate_id <> l_role_relate_id
1289 AND nvl(rlt.delete_flag,'N') <> 'Y'
1290 AND nvl(mem.delete_flag,'N') <> 'Y'
1291 --AND rlt.role_relate_id <> l_role_relate_id
1292 AND ((l_start_date_active between rlt.start_date_active
1293 and nvl(rlt.end_date_active , l_start_date_active +1))
1294 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
1295 between rlt.start_date_active and
1296 nvl(rlt.end_date_active, l_end_date_active + 1))
1297 or (l_end_date_active is null and rlt.end_date_active is null)))
1298 AND rlt.role_id = rol.role_id
1299 AND (rol.manager_flag = 'Y');
1300
1301 -- removed this as admin is not reqd OR rol.admin_flag = 'Y');
1302
1303 same_grp_mgr_admin_rec same_grp_mgr_admin_cur%ROWTYPE;
1304
1305 --cursor for parent groups
1306 CURSOR par_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1307 l_start_date_active DATE,
1308 l_end_date_active DATE)
1309 IS
1310 SELECT parent_group_id,
1311 immediate_parent_flag
1312 FROM jtf_rs_groups_denorm
1313 WHERE group_id = l_group_id
1314 AND parent_group_id <> l_group_id
1315 AND ((l_start_date_active between start_date_active
1316 and nvl(end_date_active , l_start_date_active +1))
1317 OR ((nvl(l_end_date_active, start_date_active +1)
1318 between start_date_active and
1319 nvl(end_date_active, l_end_date_active + 1))
1320 or (l_end_date_active is null and end_date_active is null)));
1321
1322 par_grp_rec par_grp_cur%ROWTYPE;
1323
1324
1325 --cursor to fetch admin for a group
1326 CURSOR admin_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1327 l_start_date_active DATE,
1328 l_end_date_active DATE)
1329 IS
1330 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
1331 mem.resource_id,
1332 mem.person_id,
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'
1342 AND nvl(rlt.delete_flag,'N') <> 'Y'
1343 AND nvl(mem.delete_flag,'N') <> 'Y'
1344 AND rlt.role_id = rol.role_id
1345 AND rol.admin_flag = 'Y'
1346 AND ((l_start_date_active between rlt.start_date_active
1347 and nvl(rlt.end_date_active , l_start_date_active +1))
1348 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
1349 between rlt.start_date_active and
1350 nvl(rlt.end_date_active, l_end_date_active + 1))
1351 or (l_end_date_active is null and rlt.end_date_active is null)));
1352
1353 admin_rec admin_cur%rowtype;
1354
1355 --cursor to fetch managers for a group
1356 CURSOR mgr_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1357 l_start_date_active DATE,
1358 l_end_date_active DATE)
1359 IS
1360 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
1361 mem.resource_id,
1362 mem.person_id,
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'
1372 AND nvl(mem.delete_flag,'N') <> 'Y'
1373 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1374 AND rlt.role_id = rol.role_id
1375 AND rol.manager_flag = 'Y'
1376 AND ((l_start_date_active between rlt.start_date_active
1377 and nvl(rlt.end_date_active , l_start_date_active +1))
1378 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
1379 between rlt.start_date_active and
1380 nvl(rlt.end_date_active, l_end_date_active + 1))
1381 or (l_end_date_active is null and rlt.end_date_active is null)));
1382
1383
1384 mgr_rec mgr_cur%rowtype;
1385
1386 --cursor for child groups
1387 CURSOR child_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1388 l_start_date_active DATE,
1389 l_end_date_active DATE)
1390 IS
1391 SELECT group_id,
1392 immediate_parent_flag
1393 FROM jtf_rs_groups_denorm
1394 WHERE parent_group_id = l_group_id
1395 AND group_id <> l_group_id
1396 AND ((l_start_date_active between start_date_active
1397 and nvl(end_date_active , l_start_date_active +1))
1398 OR ((nvl(l_end_date_active, start_date_active +1)
1399 between start_date_active and
1400 nvl(end_date_active, l_end_date_active + 1))
1401 or (l_end_date_active is null and end_date_active is null)));
1402
1403 child_grp_rec child_grp_cur%rowtype;
1404
1405
1406 --cursor for child group members
1407 CURSOR child_mem_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1408 l_start_date_active DATE,
1409 l_end_date_active DATE)
1410 IS
1411 SELECT mem.resource_id,
1412 mem.person_id,
1413 rlt.start_date_active,
1414 rlt.end_date_active,
1415 rol.manager_flag,
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
1425 AND mem.group_member_id = rlt.role_resource_id
1426 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1427 AND rlt.role_id = rol.role_id
1428 --AND rlt.start_date_active <= l_start_date_active
1429 AND ((l_start_date_active between rlt.start_date_active
1430 and nvl(rlt.end_date_active , l_start_date_active +1))
1431 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
1432 between rlt.start_date_active and
1433 nvl(rlt.end_date_active, l_end_date_active + 1))
1434 or (l_end_date_active is null and rlt.end_date_active is null)))
1435 AND nvl(rlt.delete_flag,'N') <> 'Y'
1436 AND nvl(mem.delete_flag,'N') <> 'Y'
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);
1446
1447 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MANAGER';
1448 l_api_version CONSTANT NUMBER :=1.0;
1449 l_date Date;
1450 l_user_id Number;
1451 l_login_id Number;
1452
1453 l_start_date_active DATE;
1454 l_end_date_active DATE;
1455 l_count number := 0;
1456 BEGIN
1457 --Standard Start of API SAVEPOINT
1458 SAVEPOINT member_denormalize;
1459
1460 x_return_status := fnd_api.g_ret_sts_success;
1461
1462 --Standard Call to check API compatibility
1463 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1464 THEN
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF;
1467
1468 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1469 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1470 THEN
1471 FND_MSG_PUB.Initialize;
1472 END IF;
1473
1474
1475 l_date := sysdate;
1476 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1477 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1478
1479 -- if no group id or person id is passed in then return
1480 IF p_role_relate_id IS NULL
1481 THEN
1482 x_return_status := fnd_api.g_ret_sts_error;
1483 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_RESOURCE_NULL');
1484 FND_MSG_PUB.add;
1485 RAISE fnd_api.g_exc_error;
1486 END IF;
1487
1488
1489 --fetch the member details
1490 OPEN mem_dtls_cur(l_role_relate_id);
1491 FETCH mem_dtls_cur INTO mem_dtls_rec;
1492 IF(mem_dtls_cur%FOUND)
1493 THEN
1494 --duplicate check for the member record
1495 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
1496 mem_dtls_rec.role_relate_id,
1497 mem_dtls_rec.group_id);
1498
1499 FETCH dup_cur2 INTO DUP;
1500 IF (dup_cur2%NOTFOUND)
1501 THEN
1502 --set the hierarchy type for the record
1503 IF mem_dtls_rec.manager_flag = 'Y'
1504 THEN
1505 l_hierarchy_type := 'MGR_TO_MGR';
1506 ELSIF mem_dtls_rec.admin_flag = 'Y'
1507 THEN
1508 l_hierarchy_type := 'ADMIN_TO_ADMIN';
1509 ELSE
1510 l_hierarchy_type := 'REP_TO_REP';
1511 END IF;
1512
1513 --call table handler to insert record in rep manager
1514 l_reports_to_flag := 'N';
1515
1516 OPEN rep_mgr_seq_cur;
1517 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1518 CLOSE rep_mgr_seq_cur;
1519 jtf_rs_rep_managers_pkg.insert_row(
1520 X_ROWID => x_row_id,
1521 X_DENORM_MGR_ID => l_denorm_mgr_id,
1522 X_RESOURCE_ID => mem_dtls_rec.resource_id,
1523 X_PERSON_ID => mem_dtls_rec.person_id,
1524 X_CATEGORY => mem_dtls_rec.category,
1525 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
1526 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
1527 X_GROUP_ID => mem_dtls_rec.group_id,
1528 X_REPORTS_TO_FLAG => l_reports_to_flag,
1529 X_HIERARCHY_TYPE => l_hierarchy_type,
1530 X_START_DATE_ACTIVE => trunc(mem_dtls_rec.start_date_active),
1531 X_END_DATE_ACTIVE => trunc(mem_dtls_rec.end_date_active),
1532 X_ATTRIBUTE2 => null,
1533 X_ATTRIBUTE3 => null,
1534 X_ATTRIBUTE4 => null,
1535 X_ATTRIBUTE5 => null,
1536 X_ATTRIBUTE6 => null,
1537 X_ATTRIBUTE7 => null,
1538 X_ATTRIBUTE8 => null,
1539 X_ATTRIBUTE9 => null,
1540 X_ATTRIBUTE10 => null,
1541 X_ATTRIBUTE11 => null,
1542 X_ATTRIBUTE12 => null,
1543 X_ATTRIBUTE13 => null,
1544 X_ATTRIBUTE14 => null,
1545 X_ATTRIBUTE15 => null,
1546 X_ATTRIBUTE_CATEGORY => null,
1547 X_ATTRIBUTE1 => null,
1548 X_CREATION_DATE => l_date,
1549 X_CREATED_BY => l_user_id,
1550 X_LAST_UPDATE_DATE => l_date,
1551 X_LAST_UPDATED_BY => l_user_id,
1552 X_LAST_UPDATE_LOGIN => l_login_id,
1553 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
1554 X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
1555
1556 IF fnd_api.to_boolean (p_commit)
1557 THEN
1558 l_count := l_count + 1;
1559 if (l_count > 1000)
1560 then
1561 COMMIT WORK;
1562 l_count := 0;
1563 end if;
1564 END IF;
1565
1566 END IF; --close of dup check
1567 CLOSE dup_cur2;
1568
1569 --get all the managers and admins for the member within the same group
1570 --fetch managers and admins in the same group
1571 OPEN same_grp_mgr_admin_cur(mem_dtls_rec.group_id,
1572 mem_dtls_rec.start_date_active,
1573 mem_dtls_rec.end_date_active,
1574 mem_dtls_rec.role_relate_id);
1575
1576 FETCH same_grp_mgr_admin_cur INTO same_grp_mgr_admin_rec;
1577 l_reports_to_flag := 'Y';
1578
1579 WHILE(same_grp_mgr_admin_cur%FOUND)
1580 LOOP
1581
1582 --assign start date and end date for which this relation is valid
1583 IF(mem_dtls_rec.start_date_active < same_grp_mgr_admin_rec.start_date_active)
1584 THEN
1585 l_start_date_active := same_grp_mgr_admin_rec.start_date_active;
1586 ELSE
1587 l_start_date_active := mem_dtls_rec.start_date_active;
1588 END IF;
1589
1590 IF(mem_dtls_rec.end_date_active > same_grp_mgr_admin_rec.end_date_active)
1591 THEN
1592 l_end_date_active := same_grp_mgr_admin_rec.end_date_active;
1593 ELSIF(same_grp_mgr_admin_rec.end_date_active IS NULL)
1594 THEN
1595 l_end_date_active := mem_dtls_rec.end_date_active;
1596 ELSIF(mem_dtls_rec.end_date_active IS NULL)
1597 THEN
1598 l_end_date_active := same_grp_mgr_admin_rec.end_date_active;
1599 END IF;
1600
1601
1602 OPEN dup_cur2(same_grp_mgr_admin_rec.role_relate_id,
1603 mem_dtls_rec.role_relate_id,
1604 mem_dtls_rec.group_id);
1605
1606 FETCH dup_cur2 INTO DUP;
1607 IF (dup_cur2%notfound)
1608 THEN
1609
1610
1611 --set the hierarchy type if of type manager
1612 IF same_grp_mgr_admin_rec.manager_flag = 'Y'
1613 THEN
1614 IF mem_dtls_rec.manager_flag = 'Y'
1615 THEN
1616 l_hierarchy_type := 'MGR_TO_MGR';
1617 ELSIF mem_dtls_rec.admin_flag = 'Y'
1618 THEN
1619 l_hierarchy_type := 'MGR_TO_ADMIN';
1620 ELSE
1621 l_hierarchy_type := 'MGR_TO_REP';
1622 END IF;
1623
1624 --INSERT INTO TABLE
1625 OPEN rep_mgr_seq_cur;
1626 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1627 CLOSE rep_mgr_seq_cur;
1628 jtf_rs_rep_managers_pkg.insert_row(
1629 X_ROWID => x_row_id,
1630 X_DENORM_MGR_ID => l_denorm_mgr_id,
1631 X_RESOURCE_ID => mem_dtls_rec.resource_id,
1632 X_PERSON_ID => mem_dtls_rec.person_id,
1633 X_CATEGORY => mem_dtls_rec.category,
1634 X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
1635 X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
1636 X_GROUP_ID => mem_dtls_rec.group_id,
1637 X_REPORTS_TO_FLAG => l_reports_to_flag,
1638 X_HIERARCHY_TYPE => l_hierarchy_type,
1639 X_START_DATE_ACTIVE => trunc(l_start_date_active),
1640 X_END_DATE_ACTIVE => trunc(l_end_date_active),
1641 X_ATTRIBUTE2 => null,
1642 X_ATTRIBUTE3 => null,
1643 X_ATTRIBUTE4 => null,
1644 X_ATTRIBUTE5 => null,
1645 X_ATTRIBUTE6 => null,
1646 X_ATTRIBUTE7 => null,
1647 X_ATTRIBUTE8 => null,
1648 X_ATTRIBUTE9 => null,
1649 X_ATTRIBUTE10 => null,
1650 X_ATTRIBUTE11 => null,
1651 X_ATTRIBUTE12 => null,
1652 X_ATTRIBUTE13 => null,
1653 X_ATTRIBUTE14 => null,
1654 X_ATTRIBUTE15 => null,
1655 X_ATTRIBUTE_CATEGORY => null,
1656 X_ATTRIBUTE1 => null,
1657 X_CREATION_DATE => l_date,
1658 X_CREATED_BY => l_user_id,
1659 X_LAST_UPDATE_DATE => l_date,
1660 X_LAST_UPDATED_BY => l_user_id,
1661 X_LAST_UPDATE_LOGIN => l_login_id,
1662 X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
1663 X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
1664
1665 IF fnd_api.to_boolean (p_commit)
1666 THEN
1667 l_count := l_count + 1;
1668 if (l_count > 1000)
1669 then
1670 COMMIT WORK;
1671 l_count := 0;
1672 end if;
1673 END IF;
1674
1675
1676 END IF;
1677
1678 --set the hierarchy type if of type admin
1679 /*IF same_grp_mgr_admin_rec.admin_flag = 'Y'
1680 THEN
1681 IF mem_dtls_rec.manager_flag = 'Y'
1682 THEN
1683 l_hierarchy_type := 'ADMIN_TO_MGR';
1684 ELSIF mem_dtls_rec.admin_flag = 'Y'
1685 THEN
1686 l_hierarchy_type := 'ADMIN_TO_ADMIN';
1687 ELSE
1688 l_hierarchy_type := 'ADMIN_TO_REP';
1689 END IF;
1690
1691 -- CALL TABLE HANDLER TO insert record
1692 OPEN rep_mgr_seq_cur;
1693 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1694 CLOSE rep_mgr_seq_cur;
1695
1696 jtf_rs_rep_managers_pkg.insert_row(
1697 X_ROWID => x_row_id,
1698 X_DENORM_MGR_ID => l_denorm_mgr_id,
1699 X_RESOURCE_ID => mem_dtls_rec.resource_id,
1700 X_PERSON_ID => mem_dtls_rec.person_id,
1701 X_CATEGORY => mem_dtls_rec.category,
1702 X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
1703 X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
1704 X_GROUP_ID => mem_dtls_rec.group_id,
1705 X_REPORTS_TO_FLAG => l_reports_to_flag,
1706 X_HIERARCHY_TYPE => l_hierarchy_type,
1707 X_START_DATE_ACTIVE => l_start_date_active,
1708 X_END_DATE_ACTIVE => l_end_date_active,
1709 X_ATTRIBUTE2 => null,
1710 X_ATTRIBUTE3 => null,
1711 X_ATTRIBUTE4 => null,
1712 X_ATTRIBUTE5 => null,
1713 X_ATTRIBUTE6 => null,
1714 X_ATTRIBUTE7 => null,
1715 X_ATTRIBUTE8 => null,
1716 X_ATTRIBUTE9 => null,
1717 X_ATTRIBUTE10 => null,
1718 X_ATTRIBUTE11 => null,
1719 X_ATTRIBUTE12 => null,
1720 X_ATTRIBUTE13 => null,
1721 X_ATTRIBUTE14 => null,
1722 X_ATTRIBUTE15 => null,
1723 X_ATTRIBUTE_CATEGORY => null,
1724 X_ATTRIBUTE1 => null,
1725 X_CREATION_DATE => l_date,
1726 X_CREATED_BY => l_user_id,
1727 X_LAST_UPDATE_DATE => l_date,
1728 X_LAST_UPDATED_BY => l_user_id,
1729 X_LAST_UPDATE_LOGIN => l_login_id,
1730 X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
1731 X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
1732 END IF; */
1733
1734
1735 end if;
1736 close dup_cur2;
1737 FETCH same_grp_mgr_admin_cur INTO same_grp_mgr_admin_rec;
1738 END LOOP; -- end of same_grp_mgr_admin_cur
1739
1740 --IF MEMBER IS OF TYPE ADMIN OR MANAGER THEN INSERT RECORDS FOR THE OTHER MEMBERS OF THE GROUP
1741 --IF(mem_dtls_rec.admin_flag = 'Y' OR mem_dtls_rec.manager_flag = 'Y')
1742 --changed this for migration
1743 IF(mem_dtls_rec.manager_flag = 'Y')
1744 THEN
1745 OPEN other_cur(mem_dtls_rec.group_id,
1746 mem_dtls_rec.start_date_active,
1747 mem_dtls_rec.end_date_active,
1748 mem_dtls_rec.role_relate_id);
1749
1750 FETCH other_cur INTO other_rec;
1751 WHILE (other_cur%FOUND)
1752 LOOP
1753
1754 --assign start date and end date for which this relation is valid
1755 IF(mem_dtls_rec.start_date_active < other_rec.start_date_active)
1756 THEN
1757 l_start_date_active := other_rec.start_date_active;
1758 ELSE
1759 l_start_date_active := mem_dtls_rec.start_date_active;
1760 END IF;
1761
1762 IF(mem_dtls_rec.end_date_active > other_rec.end_date_active)
1763 THEN
1764 l_end_date_active := other_rec.end_date_active;
1765 ELSIF(other_rec.end_date_active IS NULL)
1766 THEN
1767 l_end_date_active := mem_dtls_rec.end_date_active;
1768 ELSIF(mem_dtls_rec.end_date_active IS NULL)
1769 THEN
1770 l_end_date_active := other_rec.end_date_active;
1771 END IF;
1772
1773 --duplicate check
1774 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
1775 other_rec.role_relate_id,
1776 mem_dtls_rec.group_id);
1777
1778 FETCH dup_cur2 INTO DUP;
1779 IF (dup_cur2%NOTFOUND)
1780 THEN
1781
1782 l_reports_to_flag := 'Y';
1783 IF mem_dtls_rec.manager_flag = 'Y'
1784 THEN
1785 IF(other_rec.manager_flag = 'Y')
1786 THEN
1787 l_hierarchy_type := 'MGR_TO_MGR';
1788 ELSIF(other_rec.admin_flag = 'Y')
1789 THEN
1790 l_hierarchy_type := 'MGR_TO_ADMIN';
1791 ELSE
1792 l_hierarchy_type := 'MGR_TO_REP';
1793 END IF;
1794
1795 --call table handler
1796
1797 --INSERT INTO TABLE
1798 OPEN rep_mgr_seq_cur;
1799 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1800 CLOSE rep_mgr_seq_cur;
1801
1802 jtf_rs_rep_managers_pkg.insert_row(
1803 X_ROWID => x_row_id,
1804 X_DENORM_MGR_ID => l_denorm_mgr_id,
1805 X_RESOURCE_ID =>other_rec.resource_id,
1806 X_PERSON_ID =>other_rec.person_id,
1807 X_CATEGORY => other_rec.category,
1808 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
1809 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
1810 X_GROUP_ID => mem_dtls_rec.group_id,
1811 X_REPORTS_TO_FLAG => l_reports_to_flag,
1812 X_HIERARCHY_TYPE => l_hierarchy_type,
1813 X_START_DATE_ACTIVE => l_start_date_active,
1814 X_END_DATE_ACTIVE => l_end_date_active,
1815 X_ATTRIBUTE2 => null,
1816 X_ATTRIBUTE3 => null,
1817 X_ATTRIBUTE4 => null,
1818 X_ATTRIBUTE5 => null,
1819 X_ATTRIBUTE6 => null,
1820 X_ATTRIBUTE7 => null,
1821 X_ATTRIBUTE8 => null,
1822 X_ATTRIBUTE9 => null,
1823 X_ATTRIBUTE10 => null,
1824 X_ATTRIBUTE11 => null,
1825 X_ATTRIBUTE12 => null,
1826 X_ATTRIBUTE13 => null,
1827 X_ATTRIBUTE14 => null,
1828 X_ATTRIBUTE15 => null,
1829 X_ATTRIBUTE_CATEGORY => null,
1830 X_ATTRIBUTE1 => null,
1831 X_CREATION_DATE => l_date,
1832 X_CREATED_BY => l_user_id,
1833 X_LAST_UPDATE_DATE => l_date,
1834 X_LAST_UPDATED_BY => l_user_id,
1835 X_LAST_UPDATE_LOGIN => l_login_id,
1836 X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
1837 X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id);
1838
1839 IF fnd_api.to_boolean (p_commit)
1840 THEN
1841 l_count := l_count + 1;
1842 if (l_count > 1000)
1843 then
1844 COMMIT WORK;
1845 l_count := 0;
1846 end if;
1847 END IF;
1848
1849
1850 END IF; -- end of manager flag = y
1851
1852
1853
1854 /*IF mem_dtls_rec.admin_flag = 'Y'
1855 THEN
1856 IF(other_rec.manager_flag = 'Y')
1857 THEN
1858 l_hierarchy_type := 'ADMIN_TO_MGR';
1859 ELSIF(other_rec.admin_flag = 'Y')
1860 THEN
1861 l_hierarchy_type := 'ADMIN_TO_ADMIN';
1862 ELSE
1863 l_hierarchy_type := 'ADMIN_TO_REP';
1864 END IF;
1865
1866 --call table handler
1867
1868 --INSERT INTO TABLE
1869 OPEN rep_mgr_seq_cur;
1870 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1871 CLOSE rep_mgr_seq_cur;
1872
1873 jtf_rs_rep_managers_pkg.insert_row(
1874 X_ROWID => x_row_id,
1875 X_DENORM_MGR_ID => l_denorm_mgr_id,
1876 X_RESOURCE_ID =>other_rec.resource_id,
1877 X_PERSON_ID =>other_rec.person_id,
1878 X_CATEGORY => other_rec.category,
1879 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
1880 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
1881 X_GROUP_ID => mem_dtls_rec.group_id,
1882 X_REPORTS_TO_FLAG => l_reports_to_flag,
1883 X_HIERARCHY_TYPE => l_hierarchy_type,
1884 X_START_DATE_ACTIVE => l_start_date_active,
1885 X_END_DATE_ACTIVE => l_end_date_active,
1886 X_ATTRIBUTE2 => null,
1887 X_ATTRIBUTE3 => null,
1888 X_ATTRIBUTE4 => null,
1889 X_ATTRIBUTE5 => null,
1890 X_ATTRIBUTE6 => null,
1891 X_ATTRIBUTE7 => null,
1892 X_ATTRIBUTE8 => null,
1893 X_ATTRIBUTE9 => null,
1894 X_ATTRIBUTE10 => null,
1895 X_ATTRIBUTE11 => null,
1896 X_ATTRIBUTE12 => null,
1897 X_ATTRIBUTE13 => null,
1898 X_ATTRIBUTE14 => null,
1899 X_ATTRIBUTE15 => null,
1900 X_ATTRIBUTE_CATEGORY => null,
1901 X_ATTRIBUTE1 => null,
1902 X_CREATION_DATE => l_date,
1903 X_CREATED_BY => l_user_id,
1904 X_LAST_UPDATE_DATE => l_date,
1905 X_LAST_UPDATED_BY => l_user_id,
1906 X_LAST_UPDATE_LOGIN => l_login_id,
1907 X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
1908 X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id);
1909 END IF; -- end of admin flag = y */
1910 end if;
1911 close dup_cur2;
1912
1913 FETCH other_cur INTO other_rec;
1914 END LOOP; -- END OF OTHER_CUR
1915 END IF;
1916
1917 --fetch all the parent groups for the group
1918 OPEN par_grp_cur(mem_dtls_rec.group_id,
1919 mem_dtls_rec.start_date_active,
1920 mem_dtls_rec.end_date_active);
1921
1922 FETCH par_grp_cur INTO par_grp_rec;
1923 WHILE (par_grp_cur%FOUND)
1924 LOOP
1925
1926 IF((par_grp_rec.immediate_parent_flag = 'Y')
1927 AND (nvl(mem_dtls_rec.manager_flag,'N')='Y' ))
1928 THEN
1929 l_reports_to_flag := 'Y';
1930 ELSE
1931 l_reports_to_flag := 'N';
1932 END IF;
1933 --fetch all managers
1934 OPEN mgr_cur(par_grp_rec.parent_group_id,
1935 mem_dtls_rec.start_date_active,
1936 mem_dtls_rec.end_date_active);
1937 FETCH mgr_cur INTO mgr_rec;
1938 WHILE (mgr_cur%FOUND)
1939 LOOP
1940
1941 IF mem_dtls_rec.manager_flag = 'Y'
1942 THEN
1943 l_hierarchy_type := 'MGR_TO_MGR';
1944 ELSIF mem_dtls_rec.admin_flag = 'Y'
1945 THEN
1946 l_hierarchy_type := 'MGR_TO_ADMIN';
1947 ELSE
1948 l_hierarchy_type := 'MGR_TO_REP';
1949 END IF;
1950
1951
1952
1953 --assign start date and end date for which this relation is valid
1954 IF(mem_dtls_rec.start_date_active < mgr_rec.start_date_active)
1955 THEN
1956 l_start_date_active := mgr_rec.start_date_active;
1957 ELSE
1958 l_start_date_active := mem_dtls_rec.start_date_active;
1959 END IF;
1960
1961 IF(mem_dtls_rec.end_date_active > mgr_rec.end_date_active)
1962 THEN
1963 l_end_date_active := mgr_rec.end_date_active;
1964 ELSIF(mgr_rec.end_date_active IS NULL)
1965 THEN
1966 l_end_date_active := mem_dtls_rec.end_date_active;
1967 ELSIF(mem_dtls_rec.end_date_active IS NULL)
1968 THEN
1969 l_end_date_active := mgr_rec.end_date_active;
1970 END IF;
1971
1972
1973 --call table handler
1974 OPEN dup_cur2(mgr_rec.role_relate_id,
1975 mem_dtls_rec.role_relate_id,
1976 mem_dtls_rec.group_id);
1977
1978 FETCH dup_cur2 INTO DUP;
1979 IF (dup_cur2%notfound)
1980 THEN
1981 --INSERT INTO TABLE
1982 OPEN rep_mgr_seq_cur;
1983 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
1984 CLOSE rep_mgr_seq_cur;
1985
1986 jtf_rs_rep_managers_pkg.insert_row(
1987 X_ROWID => x_row_id,
1988 X_DENORM_MGR_ID => l_denorm_mgr_id,
1989 X_RESOURCE_ID => mem_dtls_rec.resource_id,
1990 X_PERSON_ID => mem_dtls_rec.person_id,
1991 X_CATEGORY => mem_dtls_rec.category,
1992 X_MANAGER_PERSON_ID => mgr_rec.person_id,
1993 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
1994 X_GROUP_ID => mem_dtls_rec.group_id,
1995 X_HIERARCHY_TYPE => l_hierarchy_type,
1996 X_REPORTS_TO_FLAG => l_reports_to_flag,
1997 X_START_DATE_ACTIVE => l_start_date_active,
1998 X_END_DATE_ACTIVE => l_end_date_active,
1999 X_ATTRIBUTE2 => null,
2000 X_ATTRIBUTE3 => null,
2001 X_ATTRIBUTE4 => null,
2002 X_ATTRIBUTE5 => null,
2003 X_ATTRIBUTE6 => null,
2004 X_ATTRIBUTE7 => null,
2005 X_ATTRIBUTE8 => null,
2006 X_ATTRIBUTE9 => null,
2007 X_ATTRIBUTE10 => null,
2008 X_ATTRIBUTE11 => null,
2009 X_ATTRIBUTE12 => null,
2010 X_ATTRIBUTE13 => null,
2011 X_ATTRIBUTE14 => null,
2012 X_ATTRIBUTE15 => null,
2013 X_ATTRIBUTE_CATEGORY => null,
2014 X_ATTRIBUTE1 => null,
2015 X_CREATION_DATE => l_date,
2016 X_CREATED_BY => l_user_id,
2017 X_LAST_UPDATE_DATE => l_date,
2018 X_LAST_UPDATED_BY => l_user_id,
2019 X_LAST_UPDATE_LOGIN => l_login_id,
2020 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
2021 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
2022
2023 IF fnd_api.to_boolean (p_commit)
2024 THEN
2025 l_count := l_count + 1;
2026 if (l_count > 1000)
2027 then
2028 COMMIT WORK;
2029 l_count := 0;
2030 end if;
2031 END IF;
2032
2033 END IF; -- END OF DUP CHECK
2034 CLOSE dup_cur2;
2035
2036
2037
2038 --for manager the oppsite record has to be inserted
2039 IF mem_dtls_rec.manager_flag = 'Y'
2040 THEN
2041 --insert for group_id = parent_group_id
2042 --call to table handler
2043 OPEN dup_cur2(mgr_rec.role_relate_id,
2044 mem_dtls_rec.role_relate_id,
2045 par_grp_rec.parent_group_id);
2046
2047 FETCH dup_cur2 INTO DUP;
2048 IF (dup_cur2%notfound)
2049 THEN
2050 --INSERT INTO TABLE
2051 OPEN rep_mgr_seq_cur;
2052 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
2053 CLOSE rep_mgr_seq_cur;
2054
2055 jtf_rs_rep_managers_pkg.insert_row(
2056 X_ROWID => x_row_id,
2057 X_DENORM_MGR_ID => l_denorm_mgr_id,
2058 X_RESOURCE_ID => mem_dtls_rec.resource_id,
2059 X_PERSON_ID => mem_dtls_rec.person_id,
2060 X_CATEGORY => mem_dtls_rec.category,
2061 X_MANAGER_PERSON_ID => mgr_rec.person_id,
2062 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
2063 X_GROUP_ID => par_grp_rec.parent_group_id,
2064 X_REPORTS_TO_FLAG => l_reports_to_flag,
2065 X_HIERARCHY_TYPE => l_hierarchy_type,
2066 X_START_DATE_ACTIVE => l_start_date_active,
2067 X_END_DATE_ACTIVE => l_end_date_active,
2068 X_ATTRIBUTE2 => null,
2069 X_ATTRIBUTE3 => null,
2070 X_ATTRIBUTE4 => null,
2071 X_ATTRIBUTE5 => null,
2072 X_ATTRIBUTE6 => null,
2073 X_ATTRIBUTE7 => null,
2074 X_ATTRIBUTE8 => null,
2075 X_ATTRIBUTE9 => null,
2076 X_ATTRIBUTE10 => null,
2077 X_ATTRIBUTE11 => null,
2078 X_ATTRIBUTE12 => null,
2079 X_ATTRIBUTE13 => null,
2080 X_ATTRIBUTE14 => null,
2081 X_ATTRIBUTE15 => null,
2082 X_ATTRIBUTE_CATEGORY => null,
2083 X_ATTRIBUTE1 => null,
2084 X_CREATION_DATE => l_date,
2085 X_CREATED_BY => l_user_id,
2086 X_LAST_UPDATE_DATE => l_date,
2087 X_LAST_UPDATED_BY => l_user_id,
2088 X_LAST_UPDATE_LOGIN => l_login_id,
2089 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
2090 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
2091
2092 IF fnd_api.to_boolean (p_commit)
2093 THEN
2094 l_count := l_count + 1;
2095 if (l_count > 1000)
2096 then
2097 COMMIT WORK;
2098 l_count := 0;
2099 end if;
2100 END IF;
2101
2102 end if;
2103 CLOSE dup_cur2;
2104 END IF;
2105 FETCH mgr_cur INTO mgr_rec;
2106 END LOOP;
2107 CLOSE mgr_cur;
2108
2109
2110 --for admin reports to flag is always N for parent groups
2111 l_reports_to_flag := 'N';
2112
2113
2114 --fetch all ADMINS --- commented out for migrate
2115 /*OPEN admin_cur(par_grp_rec.parent_group_id,
2116 mem_dtls_rec.start_date_active,
2117 mem_dtls_rec.end_date_active);
2118 FETCH admin_cur INTO admin_rec;
2119 WHILE (admin_cur%FOUND)
2120 LOOP
2121
2122
2123
2124 IF mem_dtls_rec.manager_flag = 'Y'
2125 THEN
2126 l_hierarchy_type := 'ADMIN_TO_MGR';
2127 ELSIF mem_dtls_rec.admin_flag = 'Y'
2128 THEN
2129 l_hierarchy_type := 'ADMIN_TO_ADMIN';
2130 ELSE
2131 l_hierarchy_type := 'ADMIN_TO_REP';
2132 END IF;
2133 --call table handler
2134 --assign start date and end date for which this relation is valid
2135 IF(mem_dtls_rec.start_date_active < admin_rec.start_date_active)
2136 THEN
2137 l_start_date_active := admin_rec.start_date_active;
2138 ELSE
2139 l_start_date_active := mem_dtls_rec.start_date_active;
2140 END IF;
2141
2142 IF(mem_dtls_rec.end_date_active > admin_rec.end_date_active)
2143 THEN
2144 l_end_date_active := admin_rec.end_date_active;
2145 ELSIF(admin_rec.end_date_active IS NULL)
2146 THEN
2147 l_end_date_active := mem_dtls_rec.end_date_active;
2148 ELSIF(mem_dtls_rec.end_date_active IS NULL)
2149 THEN
2150 l_end_date_active := admin_rec.end_date_active;
2151 END IF;
2152
2153 OPEN dup_cur2(admin_rec.role_relate_id,
2154 mem_dtls_rec.role_relate_id,
2155 mem_dtls_rec.group_id);
2156
2157 FETCH dup_cur2 INTO DUP;
2158 IF (dup_cur2%notfound)
2159 THEN
2160
2161 --INSERT INTO TABLE
2162 OPEN rep_mgr_seq_cur;
2163 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
2164 CLOSE rep_mgr_seq_cur;
2165
2166 jtf_rs_rep_managers_pkg.insert_row(
2167 X_ROWID => x_row_id,
2168 X_DENORM_MGR_ID => l_denorm_mgr_id,
2169 X_RESOURCE_ID => mem_dtls_rec.resource_id,
2170 X_PERSON_ID => mem_dtls_rec.person_id,
2171 X_CATEGORY => mem_dtls_rec.category,
2172 X_MANAGER_PERSON_ID => admin_rec.person_id,
2173 X_PARENT_RESOURCE_ID => admin_rec.resource_id,
2174 X_GROUP_ID => mem_dtls_rec.group_id,
2175 X_REPORTS_TO_FLAG => l_reports_to_flag,
2176 X_HIERARCHY_TYPE => l_hierarchy_type,
2177 X_START_DATE_ACTIVE => l_start_date_active,
2178 X_END_DATE_ACTIVE => l_end_date_active,
2179 X_ATTRIBUTE2 => null,
2180 X_ATTRIBUTE3 => null,
2181 X_ATTRIBUTE4 => null,
2182 X_ATTRIBUTE5 => null,
2183 X_ATTRIBUTE6 => null,
2184 X_ATTRIBUTE7 => null,
2185 X_ATTRIBUTE8 => null,
2186 X_ATTRIBUTE9 => null,
2187 X_ATTRIBUTE10 => null,
2188 X_ATTRIBUTE11 => null,
2189 X_ATTRIBUTE12 => null,
2190 X_ATTRIBUTE13 => null,
2191 X_ATTRIBUTE14 => null,
2192 X_ATTRIBUTE15 => null,
2193 X_ATTRIBUTE_CATEGORY => null,
2194 X_ATTRIBUTE1 => null,
2195 X_CREATION_DATE => l_date,
2196 X_CREATED_BY => l_user_id,
2197 X_LAST_UPDATE_DATE => l_date,
2198 X_LAST_UPDATED_BY => l_user_id,
2199 X_LAST_UPDATE_LOGIN => l_login_id,
2200 X_PAR_ROLE_RELATE_ID => admin_rec.role_relate_id,
2201 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
2202
2203 IF fnd_api.to_boolean (p_commit)
2204 THEN
2205 l_count := l_count + 1;
2206 if (l_count > 1000)
2207 then
2208 COMMIT WORK;
2209 l_count := 0;
2210 end if;
2211 END IF;
2212
2213 end if; -- end of dup check
2214 CLOSE dup_cur2;
2215 --for manager the oppsite record has to be inserted
2216 IF mem_dtls_rec.manager_flag = 'Y'
2217 THEN
2218 --insert for group_id = parent_group_id
2219 --call to table handler
2220 --INSERT INTO TABLE
2221 OPEN dup_cur2(admin_rec.role_relate_id,
2222 mem_dtls_rec.role_relate_id,
2223 mem_dtls_rec.group_id);
2224
2225 FETCH dup_cur2 INTO DUP;
2226 IF (dup_cur2%notfound)
2227 THEN
2228 OPEN rep_mgr_seq_cur;
2229 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
2230 CLOSE rep_mgr_seq_cur;
2231
2232 jtf_rs_rep_managers_pkg.insert_row(
2233 X_ROWID => x_row_id,
2234 X_DENORM_MGR_ID => l_denorm_mgr_id,
2235 X_RESOURCE_ID => mem_dtls_rec.resource_id,
2236 X_PERSON_ID => mem_dtls_rec.person_id,
2237 X_CATEGORY => mem_dtls_rec.category,
2238 X_MANAGER_PERSON_ID => admin_rec.person_id,
2239 X_PARENT_RESOURCE_ID => admin_rec.resource_id,
2240 X_GROUP_ID => mem_dtls_rec.group_id,
2241 X_REPORTS_TO_FLAG => l_reports_to_flag,
2242 X_HIERARCHY_TYPE => l_hierarchy_type,
2243 X_START_DATE_ACTIVE => l_start_date_active,
2244 X_END_DATE_ACTIVE => l_end_date_active,
2245 X_ATTRIBUTE2 => null,
2246 X_ATTRIBUTE3 => null,
2247 X_ATTRIBUTE4 => null,
2248 X_ATTRIBUTE5 => null,
2249 X_ATTRIBUTE6 => null,
2250 X_ATTRIBUTE7 => null,
2251 X_ATTRIBUTE8 => null,
2252 X_ATTRIBUTE9 => null,
2253 X_ATTRIBUTE10 => null,
2254 X_ATTRIBUTE11 => null,
2255 X_ATTRIBUTE12 => null,
2256 X_ATTRIBUTE13 => null,
2257 X_ATTRIBUTE14 => null,
2258 X_ATTRIBUTE15 => null,
2259 X_ATTRIBUTE_CATEGORY => null,
2260 X_ATTRIBUTE1 => null,
2261 X_CREATION_DATE => l_date,
2262 X_CREATED_BY => l_user_id,
2263 X_LAST_UPDATE_DATE => l_date,
2264 X_LAST_UPDATED_BY => l_user_id,
2265 X_LAST_UPDATE_LOGIN => l_login_id,
2266 X_PAR_ROLE_RELATE_ID => admin_rec.role_relate_id,
2267 X_CHILD_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id);
2268 end if; -- end of duplicate check;
2269 CLOSE dup_cur2;
2270 END IF;
2271 FETCH admin_cur INTO admin_rec;
2272
2273 END LOOP;
2274 CLOSE admin_cur; */
2275
2276 FETCH par_grp_cur INTO par_grp_rec;
2277 END LOOP;
2278 CLOSE par_grp_cur;
2279
2280
2281 --for managers and admins get child groups and insert records for each of the members
2282 IF((mem_dtls_rec.manager_flag = 'Y') )
2283 -- OR (mem_dtls_rec.admin_flag = 'Y')) --for migration
2284 THEN
2285 --fetch all the parent groups for the group
2286 OPEN child_grp_cur(mem_dtls_rec.group_id,
2287 mem_dtls_rec.start_date_active,
2288 mem_dtls_rec.end_date_active);
2289
2290 FETCH child_grp_cur INTO child_grp_rec;
2291 WHILE (child_grp_cur%FOUND)
2292 LOOP
2293
2294
2295 --fetch all members
2296 OPEN child_mem_cur(child_grp_rec.group_id,
2297 mem_dtls_rec.start_date_active,
2298 mem_dtls_rec.end_date_active);
2299 FETCH child_mem_cur INTO child_mem_rec;
2300 WHILE (child_mem_cur%FOUND)
2301 LOOP
2302
2303 IF ((child_grp_rec.immediate_parent_flag = 'Y') AND
2304 (child_mem_rec.manager_flag = 'Y'))
2305 THEN
2306 l_reports_to_flag := 'Y';
2307 ELSE
2308 l_reports_to_flag := 'N';
2309 END IF;
2310
2311
2312 IF mem_dtls_rec.manager_flag = 'Y'
2313 THEN
2314 IF(child_mem_rec.manager_flag = 'Y')
2315 THEN
2316 l_hierarchy_type := 'MGR_TO_MGR';
2317 ELSIF(child_mem_rec.ADMIN_flag = 'Y')
2318 THEN
2319 l_hierarchy_type := 'MGR_TO_ADMIN';
2320 ELSE
2321 l_hierarchy_type := 'MGR_TO_REP';
2322 END IF;
2323 END IF;
2324 /* IF mem_dtls_rec.admin_flag = 'Y'
2325 THEN
2326 IF(child_mem_rec.manager_flag = 'Y')
2327 THEN
2328 l_hierarchy_type := 'ADMIN_TO_MGR';
2329 ELSIF(child_mem_rec.ADMIN_flag = 'Y')
2330 THEN
2331 l_hierarchy_type := 'ADMIN_TO_ADMIN';
2332 ELSE
2333 l_hierarchy_type := 'ADMIN_TO_REP';
2334 END IF;
2335 END IF; */
2336 --call table handler
2337 --assign start date and end date for which this relation is valid
2338 IF(mem_dtls_rec.start_date_active < child_mem_rec.start_date_active)
2339 THEN
2340 l_start_date_active := child_mem_rec.start_date_active;
2341 ELSE
2342 l_start_date_active := mem_dtls_rec.start_date_active;
2343 END IF;
2344
2345 IF(mem_dtls_rec.end_date_active > child_mem_rec.end_date_active)
2346 THEN
2347 l_end_date_active := child_mem_rec.end_date_active;
2348 ELSIF(child_mem_rec.end_date_active IS NULL)
2349 THEN
2350 l_end_date_active := mem_dtls_rec.end_date_active;
2351 ELSIF(mem_dtls_rec.end_date_active IS NULL)
2352 THEN
2353 l_end_date_active := child_mem_rec.end_date_active;
2354 END IF;
2355
2356 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
2357 child_mem_rec.role_relate_id,
2358 child_grp_rec.group_id);
2359
2360 FETCH dup_cur2 INTO DUP;
2361 IF (dup_cur2%notfound)
2362 THEN
2363
2364 --INSERT INTO TABLE
2365 OPEN rep_mgr_seq_cur;
2366 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
2367 CLOSE rep_mgr_seq_cur;
2368
2369 jtf_rs_rep_managers_pkg.insert_row(
2370 X_ROWID => x_row_id,
2371 X_DENORM_MGR_ID => l_denorm_mgr_id,
2372 X_RESOURCE_ID =>child_mem_rec.resource_id,
2373 X_PERSON_ID => child_mem_rec.person_id,
2374 X_CATEGORY => child_mem_rec.category,
2375 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
2376 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
2377 X_GROUP_ID => child_grp_rec.group_id,
2378 X_REPORTS_TO_FLAG => l_reports_to_flag,
2379 X_HIERARCHY_TYPE => l_hierarchy_type,
2380 X_START_DATE_ACTIVE => l_start_date_active,
2381 X_END_DATE_ACTIVE => l_end_date_active,
2382 X_ATTRIBUTE2 => null,
2383 X_ATTRIBUTE3 => null,
2384 X_ATTRIBUTE4 => null,
2385 X_ATTRIBUTE5 => null,
2386 X_ATTRIBUTE6 => null,
2387 X_ATTRIBUTE7 => null,
2388 X_ATTRIBUTE8 => null,
2389 X_ATTRIBUTE9 => null,
2390 X_ATTRIBUTE10 => null,
2391 X_ATTRIBUTE11 => null,
2392 X_ATTRIBUTE12 => null,
2393 X_ATTRIBUTE13 => null,
2394 X_ATTRIBUTE14 => null,
2395 X_ATTRIBUTE15 => null,
2396 X_ATTRIBUTE_CATEGORY => null,
2397 X_ATTRIBUTE1 => null,
2398 X_CREATION_DATE => l_date,
2399 X_CREATED_BY => l_user_id,
2400 X_LAST_UPDATE_DATE => l_date,
2401 X_LAST_UPDATED_BY => l_user_id,
2402 X_LAST_UPDATE_LOGIN => l_login_id,
2403 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
2404 X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id);
2405
2406 IF fnd_api.to_boolean (p_commit)
2407 THEN
2408 l_count := l_count + 1;
2409 if (l_count > 1000)
2410 then
2411 COMMIT WORK;
2412 l_count := 0;
2413 end if;
2414 END IF;
2415
2416
2417 END IF; -- end of dup check
2418 CLOSE dup_cur2;
2419
2420
2421 --for manager the opposite record has to be inserted
2422 IF child_mem_rec.manager_flag = 'Y'
2423 and mem_dtls_rec.manager_flag = 'Y'
2424 THEN
2425 --insert for group_id = parent_group_id
2426 --call to table handler
2427 OPEN dup_cur2(mem_dtls_rec.role_relate_id,
2428 child_mem_rec.role_relate_id,
2429 mem_dtls_rec.group_id);
2430 FETCH dup_cur2 INTO DUP;
2431 IF (dup_cur2%notfound)
2432 THEN
2433
2434 OPEN rep_mgr_seq_cur;
2435 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
2436 CLOSE rep_mgr_seq_cur;
2437
2438 jtf_rs_rep_managers_pkg.insert_row(
2439 X_ROWID => x_row_id,
2440 X_DENORM_MGR_ID => l_denorm_mgr_id,
2441 X_RESOURCE_ID =>child_mem_rec.resource_id,
2442 X_PERSON_ID => child_mem_rec.person_id,
2443 X_CATEGORY => child_mem_rec.category,
2444 X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
2445 X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
2446 X_GROUP_ID => mem_dtls_rec.group_id,
2447 X_REPORTS_TO_FLAG => l_reports_to_flag,
2448 X_HIERARCHY_TYPE => l_hierarchy_type,
2449 X_START_DATE_ACTIVE => l_start_date_active,
2450 X_END_DATE_ACTIVE => l_end_date_active,
2451 X_ATTRIBUTE2 => null,
2452 X_ATTRIBUTE3 => null,
2453 X_ATTRIBUTE4 => null,
2454 X_ATTRIBUTE5 => null,
2455 X_ATTRIBUTE6 => null,
2456 X_ATTRIBUTE7 => null,
2457 X_ATTRIBUTE8 => null,
2458 X_ATTRIBUTE9 => null,
2459 X_ATTRIBUTE10 => null,
2460 X_ATTRIBUTE11 => null,
2461 X_ATTRIBUTE12 => null,
2462 X_ATTRIBUTE13 => null,
2463 X_ATTRIBUTE14 => null,
2464 X_ATTRIBUTE15 => null,
2465 X_ATTRIBUTE_CATEGORY => null,
2466 X_ATTRIBUTE1 => null,
2467 X_CREATION_DATE => l_date,
2468 X_CREATED_BY => l_user_id,
2469 X_LAST_UPDATE_DATE => l_date,
2470 X_LAST_UPDATED_BY => l_user_id,
2471 X_LAST_UPDATE_LOGIN => l_login_id,
2472 X_PAR_ROLE_RELATE_ID => l_role_relate_id,
2473 X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id);
2474
2475 IF fnd_api.to_boolean (p_commit)
2476 THEN
2477 l_count := l_count + 1;
2478 if (l_count > 1000)
2479 then
2480 COMMIT WORK;
2481 l_count := 0;
2482 end if;
2483 END IF;
2484
2485 END IF; --end of dup check
2486 CLOSE dup_cur2;
2487
2488 END IF;
2489
2490 FETCH child_mem_cur INTO child_mem_rec;
2491 END LOOP;
2492 CLOSE child_mem_cur;
2493
2494 FETCH child_grp_cur INTO child_grp_rec;
2495 END LOOP;
2496 CLOSE child_grp_cur;
2497 END IF; --end of child group members insert
2498
2499
2500 END IF;--end of member details found if statement
2501
2502
2503 CLOSE mem_dtls_cur;
2504
2505 --
2506 IF fnd_api.to_boolean (p_commit)
2507 THEN
2508 COMMIT WORK;
2509 END IF;
2510
2511 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2512
2513 EXCEPTION
2514 WHEN fnd_api.g_exc_unexpected_error
2515 THEN
2516 ROLLBACK TO member_denormalize;
2517 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
2518 --FND_MSG_PUB.add;
2519 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2520 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2521 WHEN fnd_api.g_exc_error
2522 THEN
2523 ROLLBACK TO member_denormalize;
2524 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2525
2526 WHEN OTHERS
2527 THEN
2528 ROLLBACK TO member_denormalize;
2529 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2530 fnd_message.set_token('P_SQLCODE',SQLCODE);
2531 fnd_message.set_token('P_SQLERRM',SQLERRM);
2532 fnd_message.set_token('P_API_NAME',l_api_name);
2533 FND_MSG_PUB.add;
2534 x_return_status := fnd_api.g_ret_sts_unexp_error;
2535 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2536
2537
2538 END INSERT_REP_MANAGER_MIGR;
2539
2540
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,
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
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
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;
2570 l_user_id Number;
2571 l_login_id Number;
2572
2573 L_RETURN_STATUS VARCHAR2(100);
2574 L_MSG_COUNT NUMBER;
2575 L_MSG_DATA VARCHAR2(200);
2576
2577 l_count number := 0;
2578 l_pass_commit varchar2(1) := fnd_api.g_false;
2579 BEGIN
2580 --Standard Start of API SAVEPOINT
2581 SAVEPOINT member_denormalize;
2582
2583 x_return_status := fnd_api.g_ret_sts_success;
2584
2585 --Standard Call to check API compatibility
2586 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2587 THEN
2588 RAISE FND_API.G_EXC_ERROR;
2589 END IF;
2590
2591 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2592 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2593 THEN
2594 FND_MSG_PUB.Initialize;
2595 END IF;
2596
2597 IF fnd_api.to_boolean (p_commit)
2598 THEN
2599 l_pass_commit := fnd_api.g_true;
2600 END IF;
2601
2602 l_date := sysdate;
2603 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2604 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2605
2606 --
2607 --get all the denorm id's for the role relate id and delete the rows from rep manager table
2608 OPEN denorm_cur(l_role_relate_id);
2609 FETCH denorm_cur INTO denorm_rec;
2610 WHILE(denorm_cur%FOUND)
2611 LOOP
2612 jtf_rs_rep_managers_pkg.delete_row(denorm_rec.denorm_mgr_id);
2613
2614
2615 IF fnd_api.to_boolean (p_commit)
2616 THEN
2617 l_count := l_count + 1;
2618 if (l_count > 1000)
2619 then
2620 COMMIT WORK;
2621 l_count := 0;
2622 end if;
2623 END IF;
2624
2625 FETCH denorm_cur INTO denorm_rec;
2626 END LOOP; --end of denorm cur loop
2627
2628
2629 --call the insert api for the role relate id
2630 JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER(
2631 P_API_VERSION => 1.0,
2632 P_INIT_MSG_LIST => p_init_msg_list,
2633 P_COMMIT => l_pass_commit,
2634 P_ROLE_RELATE_ID => l_role_relate_id ,
2635 X_RETURN_STATUS => L_RETURN_STATUS,
2636 X_MSG_COUNT => L_MSG_COUNT,
2637 X_MSG_DATA => L_MSG_DATA);
2638
2639 IF ( l_return_status <> fnd_api.g_ret_sts_success )
2640 THEN
2641 x_return_status := l_return_status;
2642 raise fnd_api.g_exc_error ;
2643 END IF;
2644
2645 --
2646 IF fnd_api.to_boolean (p_commit)
2647 THEN
2648 COMMIT WORK;
2649 END IF;
2650
2651 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2652
2653 EXCEPTION
2654 WHEN fnd_api.g_exc_unexpected_error
2655 THEN
2656 ROLLBACK TO member_denormalize;
2657 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
2658 --FND_MSG_PUB.add;
2659 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2660 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2661 WHEN fnd_api.g_exc_error
2662 THEN
2663 ROLLBACK TO member_denormalize;
2664 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2665
2666 WHEN OTHERS
2667 THEN
2668 ROLLBACK TO member_denormalize;
2669 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2670 fnd_message.set_token('P_SQLCODE',SQLCODE);
2671 fnd_message.set_token('P_SQLERRM',SQLERRM);
2672 fnd_message.set_token('P_API_NAME',l_api_name);
2673 FND_MSG_PUB.add;
2674 x_return_status := fnd_api.g_ret_sts_unexp_error;
2675 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2676
2677 END UPDATE_REP_MANAGER;
2678
2679
2680 -- FOR DELETE ON JTF_RS_ROLE_RELATE
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
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
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;
2704 l_user_id Number;
2705 l_login_id Number;
2706
2707 L_RETURN_STATUS VARCHAR2(100);
2708 L_MSG_COUNT NUMBER;
2709 L_MSG_DATA VARCHAR2(200);
2710 l_pass_commit varchar2(1) := fnd_api.g_false;
2711 l_commit number := 0;
2712 l_count number := 0;
2713 BEGIN
2714 --Standard Start of API SAVEPOINT
2715 SAVEPOINT member_denormalize;
2716
2717 x_return_status := fnd_api.g_ret_sts_success;
2718
2719 --Standard Call to check API compatibility
2720 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2721 THEN
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724
2725 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2726 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2727 THEN
2728 FND_MSG_PUB.Initialize;
2729 END IF;
2730
2731 IF fnd_api.to_boolean (p_commit)
2732 THEN
2733 l_pass_commit := fnd_api.g_true;
2734 END IF;
2735
2736 l_date := sysdate;
2737 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2738 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2739
2740 --
2741 --get all the denorm id's for the role relate id and delete the rows from rep manager table
2742 /* OPEN denorm_cur(l_role_relate_id);
2743 FETCH denorm_cur INTO denorm_rec;
2744 WHILE(denorm_cur%FOUND)
2745 LOOP
2746 jtf_rs_rep_managers_pkg.delete_row(denorm_rec.denorm_mgr_id);
2747
2748 IF fnd_api.to_boolean (p_commit)
2749 THEN
2750 l_count := l_count + 1;
2751 if (l_count > 1000)
2752 then
2753 COMMIT WORK;
2754 l_count := 0;
2755 end if;
2756 END IF;
2757
2758 FETCH denorm_cur INTO denorm_rec;
2759 END LOOP; --end of denorm cur loop
2760 */
2761
2762 delete jtf_rs_rep_managers where par_role_relate_id = p_role_relate_id;
2763
2764 IF fnd_api.to_boolean (p_commit)
2765 THEN
2766 COMMIT WORK;
2767 END IF;
2768
2769 delete jtf_rs_rep_managers where child_role_relate_id = p_role_relate_id;
2770
2771 IF fnd_api.to_boolean (p_commit)
2772 THEN
2773 COMMIT WORK;
2774 END IF;
2775
2776 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2777
2778 EXCEPTION
2779 WHEN fnd_api.g_exc_unexpected_error
2780 THEN
2781 ROLLBACK TO member_denormalize;
2782 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
2783 --FND_MSG_PUB.add;
2784 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2785 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2786 WHEN fnd_api.g_exc_error
2787 THEN
2788 ROLLBACK TO member_denormalize;
2789 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2790
2791 WHEN OTHERS
2792 THEN
2793 ROLLBACK TO member_denormalize;
2794 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2795 fnd_message.set_token('P_SQLCODE',SQLCODE);
2796 fnd_message.set_token('P_SQLERRM',SQLERRM);
2797 fnd_message.set_token('P_API_NAME',l_api_name);
2798 FND_MSG_PUB.add;
2799 x_return_status := fnd_api.g_ret_sts_unexp_error;
2800 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2801
2802
2803 END DELETE_MEMBERS;
2804
2805
2806 -- FOR DELETE ON JTF_RS_GROUPS_DENORM
2807 PROCEDURE DELETE_GROUP_DENORM(
2808 P_API_VERSION IN NUMBER,
2809 P_INIT_MSG_LIST IN VARCHAR2,
2810 P_COMMIT IN VARCHAR2,
2811 P_DENORM_GRP_ID IN JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE,
2812 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2813 X_MSG_COUNT OUT NOCOPY NUMBER,
2814 X_MSG_DATA OUT NOCOPY VARCHAR2 )
2815 IS
2816
2817 l_denorm_grp_id JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE := p_denorm_grp_id;
2818
2819 CURSOR denorm_cur(l_denorm_group_id JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE)
2820 IS
2821 SELECT parent_group_id ,
2822 group_id
2823 FROM jtf_rs_groups_denorm
2824 WHERE denorm_grp_id = l_denorm_group_id;
2825
2826 denorm_rec denorm_cur%rowtype;
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'
2836 AND nvl(rlt.delete_flag,'N') <> 'Y'
2837 AND nvl(mem.delete_flag,'N') <> 'Y';
2838
2839 par_role_relate_rec par_role_relate_cur%rowtype;
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'
2849 AND nvl(rlt.delete_flag,'N') <> 'Y'
2850 AND nvl(mem.delete_flag,'N') <> 'Y';
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
2860 WHERE par_role_relate_id = l_par_role_relate_id
2861 AND child_role_relate_id = l_child_role_relate_id;
2862
2863 rep_denorm_rec rep_denorm_cur%rowtype;
2864
2865 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_DENORM';
2866 l_api_version CONSTANT NUMBER :=1.0;
2867 l_date Date;
2868 l_user_id Number;
2869 l_login_id Number;
2870
2871 L_RETURN_STATUS VARCHAR2(100);
2872 L_MSG_COUNT NUMBER;
2873 L_MSG_DATA VARCHAR2(200);
2874
2875 l_commit number := 0;
2876 l_count number := 0;
2877 l_pass_commit varchar2(1) := fnd_api.g_false;
2878 BEGIN
2879 --Standard Start of API SAVEPOINT
2880 SAVEPOINT member_denormalize;
2881
2882 x_return_status := fnd_api.g_ret_sts_success;
2883
2884 --Standard Call to check API compatibility
2885 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2886 THEN
2887 RAISE FND_API.G_EXC_ERROR;
2888 END IF;
2889
2890 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2891 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2892 THEN
2893 FND_MSG_PUB.Initialize;
2894 END IF;
2895
2896 l_date := sysdate;
2897 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2898 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2899
2900 IF fnd_api.to_boolean (p_commit)
2901 THEN
2902 l_pass_commit := fnd_api.g_true;
2903 END IF;
2904
2905 --
2906 --get all the denorm id's for the role relate id and delete the rows from rep manager table
2907 OPEN denorm_cur(l_denorm_grp_id);
2908 FETCH denorm_cur INTO denorm_rec;
2909 IF (denorm_cur%FOUND)
2910 THEN
2911 --added this to delete the denorm record before recreating it again
2912 jtf_rs_groups_denorm_pkg.delete_row(l_denorm_grp_id);
2913 --get all the role relate ids for the parent group
2914 OPEN par_role_relate_cur(denorm_rec.parent_group_id);
2915 FETCH par_role_relate_cur INTO par_role_relate_rec;
2916 WHILE(par_role_relate_cur%FOUND)
2917 LOOP
2918
2919 --get all the role relate ids for the child group
2920 OPEN child_role_relate_cur(denorm_rec.group_id);
2921 FETCH child_role_relate_cur INTO child_role_relate_rec;
2922 WHILE(child_role_relate_cur%FOUND)
2923 LOOP
2924 delete jtf_rs_rep_managers
2925 where par_role_relate_id = par_role_relate_rec.role_relate_id
2926 and child_role_relate_id = child_role_relate_rec.role_relate_id;
2927
2928
2929 IF fnd_api.to_boolean (p_commit)
2930 THEN
2931 l_count := l_count + 1;
2932 if (l_count > 1000)
2933 then
2934 COMMIT WORK;
2935 l_count := 0;
2936 end if;
2937 END IF;
2938
2939 --recreate the rep manager records for the child role relate id
2940
2941 JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER(
2942 P_API_VERSION => 1.0,
2943 P_INIT_MSG_LIST => p_init_msg_list,
2944 P_COMMIT => l_pass_commit,
2945 P_ROLE_RELATE_ID => child_role_relate_rec.role_relate_id,
2946 X_RETURN_STATUS => l_return_status,
2947 X_MSG_COUNT => l_msg_count,
2948 X_MSG_DATA => l_msg_data);
2949 FETCH child_role_relate_cur INTO child_role_relate_rec;
2950 END LOOP;
2951 CLOSE child_role_relate_cur;
2952
2953 --recreate the rep manager records for the parent role relate id
2954
2955 JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER(
2956 P_API_VERSION => 1.0,
2957 P_INIT_MSG_LIST => p_init_msg_list,
2958 P_COMMIT => l_pass_commit,
2959 P_ROLE_RELATE_ID => par_role_relate_rec.role_relate_id,
2960 X_RETURN_STATUS => l_return_status,
2961 X_MSG_COUNT => l_msg_count,
2962 X_MSG_DATA => l_msg_data);
2963
2964 FETCH par_role_relate_cur INTO par_role_relate_rec;
2965 END LOOP; -- end of par_role_relate_cur
2966 close par_role_relate_cur;
2967
2968
2969 END IF; --end of denorm cur loop
2970 close denorm_cur;
2971 IF fnd_api.to_boolean (p_commit)
2972 THEN
2973 COMMIT WORK;
2974 END IF;
2975
2976 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2977
2978 EXCEPTION
2979 WHEN fnd_api.g_exc_unexpected_error
2980 THEN
2981 ROLLBACK TO member_denormalize;
2982 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
2983 --FND_MSG_PUB.add;
2984 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2985 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2986 WHEN fnd_api.g_exc_error
2987 THEN
2988 ROLLBACK TO member_denormalize;
2989 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2990
2991 WHEN OTHERS
2992 THEN
2993 ROLLBACK TO member_denormalize;
2994 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2995 fnd_message.set_token('P_SQLCODE',SQLCODE);
2996 fnd_message.set_token('P_SQLERRM',SQLERRM);
2997 fnd_message.set_token('P_API_NAME',l_api_name);
2998 FND_MSG_PUB.add;
2999 x_return_status := fnd_api.g_ret_sts_unexp_error;
3000 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3001
3002
3003 END DELETE_GROUP_DENORM;
3004
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
3014 CURSOR rep_mgr_seq_cur
3015 IS
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,
3025 rlt.role_id,
3026 rlt.start_date_active,
3027 rlt.end_date_active,
3028 rol.member_flag ,
3029 rol.admin_flag ,
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
3039 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
3040 AND rlt.role_resource_id = mem.group_member_id
3041 AND rlt.role_id = rol.role_id
3042 AND nvl(rlt.delete_flag,'N') <> 'Y'
3043 AND nvl(mem.delete_flag,'N') <> 'Y'
3044 AND mem.resource_id = rsc.resource_id;
3045
3046
3047 mem_dtls_rec mem_dtls_cur%rowtype;
3048
3049
3050 --cursor for duplicate check
3051 CURSOR dup_cur(l_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
3052 l_manager_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
3053 l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3054 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
3055 l_start_date_active DATE,
3056 l_end_date_active DATE)
3057 IS
3058 SELECT person_id
3059 FROM jtf_rs_rep_managers
3060 WHERE group_id = l_group_id
3061 AND ( person_id = l_person_id
3062 OR (l_person_id IS NULL AND person_id IS NULL))
3063 AND manager_person_id = l_manager_person_id
3064 AND resource_id = l_resource_id
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
3074 FROM jtf_rs_rep_managers
3075 WHERE par_role_relate_id = l_par_role_relate_id
3076 AND child_role_relate_id = l_child_role_relate_id
3077 AND group_id = l_group_id;
3078
3079
3080
3081 dup NUMBER := 0;
3082
3083 --cursor for parent groups
3084 CURSOR par_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3085 l_start_date_active DATE,
3086 l_end_date_active DATE)
3087 IS
3088 SELECT parent_group_id,
3089 immediate_parent_flag
3090 FROM jtf_rs_groups_denorm
3091 WHERE group_id = l_group_id
3092 AND parent_group_id <> l_group_id
3093 AND ((l_start_date_active between start_date_active
3094 and nvl(end_date_active , l_start_date_active +1))
3095 OR ((nvl(l_end_date_active, start_date_active +1)
3096 between start_date_active and
3097 nvl(end_date_active, l_end_date_active + 1))
3098 or (l_end_date_active is null and end_date_active is null)));
3099
3100 par_grp_rec par_grp_cur%ROWTYPE;
3101
3102
3103 --cursor to fetch admin for a group
3104 CURSOR admin_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3105 l_start_date_active DATE,
3106 l_end_date_active DATE)
3107 IS
3108 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
3109 mem.resource_id,
3110 mem.person_id,
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'
3120 AND nvl(rlt.delete_flag,'N') <> 'Y'
3121 AND nvl(mem.delete_flag,'N') <> 'Y'
3122 AND rlt.role_id = rol.role_id
3123 AND rol.admin_flag = 'Y'
3124 AND ((l_start_date_active between rlt.start_date_active
3125 and nvl(rlt.end_date_active , l_start_date_active +1))
3126 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
3127 between rlt.start_date_active and
3128 nvl(rlt.end_date_active, l_end_date_active + 1))
3129 or (l_end_date_active is null and rlt.end_date_active is null)));
3130
3131 admin_rec admin_cur%rowtype;
3132
3133 --cursor to fetch managers for a group
3134 CURSOR mgr_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3135 l_start_date_active DATE,
3136 l_end_date_active DATE)
3137 IS
3138 SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
3139 mem.resource_id,
3140 mem.person_id,
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'
3150 AND nvl(mem.delete_flag,'N') <> 'Y'
3151 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
3152 AND rlt.role_id = rol.role_id
3153 AND rol.manager_flag = 'Y'
3154 AND ((l_start_date_active between rlt.start_date_active
3155 and nvl(rlt.end_date_active , l_start_date_active +1))
3156 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
3157 between rlt.start_date_active and
3158 nvl(rlt.end_date_active, l_end_date_active + 1))
3159 or (l_end_date_active is null and rlt.end_date_active is null)));
3160
3161
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);
3171
3172 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MGR_PARENT';
3173 l_api_version CONSTANT NUMBER :=1.0;
3174 l_date Date;
3175 l_user_id Number;
3176 l_login_id Number;
3177
3178 l_start_date_active DATE;
3179 l_end_date_active DATE;
3180 l_commit number := 0;
3181 l_count number := 0;
3182 BEGIN
3183 --Standard Start of API SAVEPOINT
3184 SAVEPOINT member_denormalize;
3185
3186 x_return_status := fnd_api.g_ret_sts_success;
3187
3188 --Standard Call to check API compatibility
3189 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
3190 THEN
3191 RAISE FND_API.G_EXC_ERROR;
3192 END IF;
3193
3194 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3195 IF FND_API.To_boolean(P_INIT_MSG_LIST)
3196 THEN
3197 FND_MSG_PUB.Initialize;
3198 END IF;
3199
3200
3201 l_date := sysdate;
3202 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3203 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
3204
3205 -- if no group id or person id is passed in then return
3206 IF p_role_relate_id IS NULL
3207 THEN
3208 x_return_status := fnd_api.g_ret_sts_error;
3209 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_RESOURCE_NULL');
3210 FND_MSG_PUB.add;
3211 RAISE fnd_api.g_exc_error;
3212 END IF;
3213
3214
3215 --fetch the member details
3216 OPEN mem_dtls_cur(l_role_relate_id);
3217 FETCH mem_dtls_cur INTO mem_dtls_rec;
3218 IF((mem_dtls_cur%FOUND)
3219 AND
3220 (nvl(mem_dtls_rec.manager_flag, 'N') = 'Y' OR
3221 nvl(mem_dtls_rec.admin_flag, 'N') = 'Y' OR
3222 nvl(mem_dtls_rec.member_flag, 'N') = 'Y' ))
3223 THEN
3224
3225 --fetch all the parent groups for the group
3226 OPEN par_grp_cur(mem_dtls_rec.group_id,
3227 mem_dtls_rec.start_date_active,
3228 mem_dtls_rec.end_date_active);
3229
3230 FETCH par_grp_cur INTO par_grp_rec;
3231 WHILE (par_grp_cur%FOUND)
3232 LOOP
3233
3234 IF((par_grp_rec.immediate_parent_flag = 'Y')
3235 AND (nvl(mem_dtls_rec.manager_flag,'N')='Y' ))
3236 THEN
3237 l_reports_to_flag := 'Y';
3238 ELSE
3239 l_reports_to_flag := 'N';
3240 END IF;
3241 --fetch all managers
3242 OPEN mgr_cur(par_grp_rec.parent_group_id,
3243 mem_dtls_rec.start_date_active,
3244 mem_dtls_rec.end_date_active);
3245 FETCH mgr_cur INTO mgr_rec;
3246 WHILE (mgr_cur%FOUND)
3247 LOOP
3248
3249 IF mem_dtls_rec.manager_flag = 'Y'
3250 THEN
3251 l_hierarchy_type := 'MGR_TO_MGR';
3252 ELSIF mem_dtls_rec.admin_flag = 'Y'
3253 THEN
3254 l_hierarchy_type := 'MGR_TO_ADMIN';
3255 ELSE
3256 l_hierarchy_type := 'MGR_TO_REP';
3257 END IF;
3258
3259
3260
3261 --assign start date and end date for which this relation is valid
3262 IF(mem_dtls_rec.start_date_active < mgr_rec.start_date_active)
3263 THEN
3264 l_start_date_active := mgr_rec.start_date_active;
3265 ELSE
3266 l_start_date_active := mem_dtls_rec.start_date_active;
3267 END IF;
3268
3269 IF(mem_dtls_rec.end_date_active > mgr_rec.end_date_active)
3270 THEN
3271 l_end_date_active := mgr_rec.end_date_active;
3272 ELSIF(mgr_rec.end_date_active IS NULL)
3273 THEN
3274 l_end_date_active := mem_dtls_rec.end_date_active;
3275 ELSIF(mem_dtls_rec.end_date_active IS NULL)
3276 THEN
3277 l_end_date_active := mgr_rec.end_date_active;
3278 END IF;
3279
3280 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
3281 then
3282 --call table handler
3283 OPEN dup_cur2(mgr_rec.role_relate_id,
3284 mem_dtls_rec.role_relate_id,
3285 mem_dtls_rec.group_id);
3286
3287 FETCH dup_cur2 INTO DUP;
3288 IF (dup_cur2%notfound)
3289 THEN
3290 --INSERT INTO TABLE
3291 OPEN rep_mgr_seq_cur;
3292 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
3293 CLOSE rep_mgr_seq_cur;
3294
3295 jtf_rs_rep_managers_pkg.insert_row(
3296 X_ROWID => x_row_id,
3297 X_DENORM_MGR_ID => l_denorm_mgr_id,
3298 X_RESOURCE_ID => mem_dtls_rec.resource_id,
3299 X_PERSON_ID => mem_dtls_rec.person_id,
3300 X_CATEGORY => mem_dtls_rec.category,
3301 X_MANAGER_PERSON_ID => mgr_rec.person_id,
3302 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
3303 X_GROUP_ID => mem_dtls_rec.group_id,
3304 X_HIERARCHY_TYPE => l_hierarchy_type,
3305 X_REPORTS_TO_FLAG => l_reports_to_flag,
3306 X_START_DATE_ACTIVE => trunc(l_start_date_active),
3307 X_END_DATE_ACTIVE => trunc(l_end_date_active),
3308 X_ATTRIBUTE2 => null,
3309 X_ATTRIBUTE3 => null,
3310 X_ATTRIBUTE4 => null,
3311 X_ATTRIBUTE5 => null,
3312 X_ATTRIBUTE6 => null,
3313 X_ATTRIBUTE7 => null,
3314 X_ATTRIBUTE8 => null,
3315 X_ATTRIBUTE9 => null,
3316 X_ATTRIBUTE10 => null,
3317 X_ATTRIBUTE11 => null,
3318 X_ATTRIBUTE12 => null,
3319 X_ATTRIBUTE13 => null,
3320 X_ATTRIBUTE14 => null,
3321 X_ATTRIBUTE15 => null,
3322 X_ATTRIBUTE_CATEGORY => null,
3323 X_ATTRIBUTE1 => null,
3324 X_CREATION_DATE => l_date,
3325 X_CREATED_BY => l_user_id,
3326 X_LAST_UPDATE_DATE => l_date,
3327 X_LAST_UPDATED_BY => l_user_id,
3328 X_LAST_UPDATE_LOGIN => l_login_id,
3329 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
3330 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
3331
3332 IF fnd_api.to_boolean (p_commit)
3333 THEN
3334 l_count := l_count + 1;
3335 if (l_count > 1000)
3336 then
3337 COMMIT WORK;
3338 l_count := 0;
3339 end if;
3340 END IF;
3341
3342 END IF; -- END OF DUP CHECK
3343 CLOSE dup_cur2;
3344 end if; --end of st dt check
3345
3346
3347 --for manager the oppsite record has to be inserted
3348 IF mem_dtls_rec.manager_flag = 'Y'
3349 THEN
3350 --insert for group_id = parent_group_id
3351 --call to table handler
3352 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
3353 then
3354 OPEN dup_cur2(mgr_rec.role_relate_id,
3355 mem_dtls_rec.role_relate_id,
3356 par_grp_rec.parent_group_id);
3357
3358 FETCH dup_cur2 INTO DUP;
3359 IF (dup_cur2%notfound)
3360 THEN
3361 --INSERT INTO TABLE
3362 OPEN rep_mgr_seq_cur;
3363 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
3364 CLOSE rep_mgr_seq_cur;
3365
3366 jtf_rs_rep_managers_pkg.insert_row(
3367 X_ROWID => x_row_id,
3368 X_DENORM_MGR_ID => l_denorm_mgr_id,
3369 X_RESOURCE_ID => mem_dtls_rec.resource_id,
3370 X_PERSON_ID => mem_dtls_rec.person_id,
3371 X_CATEGORY => mem_dtls_rec.category,
3372 X_MANAGER_PERSON_ID => mgr_rec.person_id,
3373 X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
3374 X_GROUP_ID => par_grp_rec.parent_group_id,
3375 X_REPORTS_TO_FLAG => l_reports_to_flag,
3376 X_HIERARCHY_TYPE => l_hierarchy_type,
3377 X_START_DATE_ACTIVE => trunc(l_start_date_active),
3378 X_END_DATE_ACTIVE => trunc(l_end_date_active),
3379 X_ATTRIBUTE2 => null,
3380 X_ATTRIBUTE3 => null,
3381 X_ATTRIBUTE4 => null,
3382 X_ATTRIBUTE5 => null,
3383 X_ATTRIBUTE6 => null,
3384 X_ATTRIBUTE7 => null,
3385 X_ATTRIBUTE8 => null,
3386 X_ATTRIBUTE9 => null,
3387 X_ATTRIBUTE10 => null,
3388 X_ATTRIBUTE11 => null,
3389 X_ATTRIBUTE12 => null,
3390 X_ATTRIBUTE13 => null,
3391 X_ATTRIBUTE14 => null,
3392 X_ATTRIBUTE15 => null,
3393 X_ATTRIBUTE_CATEGORY => null,
3394 X_ATTRIBUTE1 => null,
3395 X_CREATION_DATE => l_date,
3396 X_CREATED_BY => l_user_id,
3397 X_LAST_UPDATE_DATE => l_date,
3398 X_LAST_UPDATED_BY => l_user_id,
3399 X_LAST_UPDATE_LOGIN => l_login_id,
3400 X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
3401 X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
3402
3403 IF fnd_api.to_boolean (p_commit)
3404 THEN
3405 l_count := l_count + 1;
3406 if (l_count > 1000)
3407 then
3408 COMMIT WORK;
3409 l_count := 0;
3410 end if;
3411 END IF;
3412
3413 end if;
3414 CLOSE dup_cur2;
3415 end if; -- end of st dt check
3416 END IF;
3417 FETCH mgr_cur INTO mgr_rec;
3418 END LOOP;
3419 CLOSE mgr_cur;
3420
3421
3422 FETCH par_grp_cur INTO par_grp_rec;
3423 END LOOP;
3424 CLOSE par_grp_cur;
3425
3426
3427 END IF;--end of member details found if statement
3428
3429
3430 CLOSE mem_dtls_cur;
3431
3432 --
3433 IF fnd_api.to_boolean (p_commit)
3434 THEN
3435 COMMIT WORK;
3436 END IF;
3437
3438 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3439
3440 EXCEPTION
3441 WHEN fnd_api.g_exc_unexpected_error
3442 THEN
3443 ROLLBACK TO member_denormalize;
3444 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
3445 --FND_MSG_PUB.add;
3446 --x_return_status := fnd_api.g_ret_sts_unexp_error;
3447 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3448 WHEN fnd_api.g_exc_error
3449 THEN
3450 ROLLBACK TO member_denormalize;
3451 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3452
3453 WHEN OTHERS
3454 THEN
3455 ROLLBACK TO member_denormalize;
3456 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3457 fnd_message.set_token('P_SQLCODE',SQLCODE);
3458 fnd_message.set_token('P_SQLERRM',SQLERRM);
3459 fnd_message.set_token('P_API_NAME',l_api_name);
3460 FND_MSG_PUB.add;
3461 x_return_status := fnd_api.g_ret_sts_unexp_error;
3462 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3463
3464
3465 END INSERT_REP_MGR_PARENT;
3466
3467
3468 PROCEDURE INSERT_GRP_DENORM(
3469 P_API_VERSION IN NUMBER,
3470 P_INIT_MSG_LIST IN VARCHAR2,
3471 P_COMMIT IN VARCHAR2,
3472 P_GROUP_DENORM_ID IN NUMBER,
3473 P_GROUP_ID IN NUMBER,
3474 P_PARENT_GROUP_ID IN NUMBER,
3475 P_START_DATE_ACTIVE IN DATE,
3476 P_END_DATE_ACTIVE IN DATE,
3477 P_IMMEDIATE_PARENT_FLAG IN VARCHAR2,
3478 P_DENORM_LEVEL IN NUMBER,
3479 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3480 X_MSG_COUNT OUT NOCOPY NUMBER,
3481 X_MSG_DATA OUT NOCOPY VARCHAR2 )
3482 IS
3483 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GRP_DENORM';
3484 l_api_version CONSTANT NUMBER :=1.0;
3485 l_date Date;
3486 l_fnd_date Date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
3487 l_user_id Number;
3488 l_login_id Number;
3489 j BINARY_INTEGER := 0;
3490 k BINARY_INTEGER := 0;
3491
3492
3493 TYPE process_table_rec IS RECORD(resource_id NUMBER,
3494 person_id NUMBER,
3495 category VARCHAR2(30),
3496 manager_person_id NUMBER,
3497 group_id NUMBER,
3498 hierarchy_type VARCHAR2(240),
3499 reports_to_flag VARCHAR2(1),
3500 start_date_active DATE,
3501 end_date_active DATE,
3502 par_role_relate_id NUMBER,
3503 child_role_relate_id NUMBER,
3504 parent_resource_id NUMBER);
3505
3506 TYPE process_table_tbl IS TABLE OF process_table_rec
3507 INDEX BY BINARY_INTEGER;
3508
3509 l_process_table process_table_tbl;
3510
3511 TYPE same_group_member_role_rec IS RECORD(resource_id NUMBER,
3512 person_id NUMBER,
3513 group_id NUMBER,
3514 role_id NUMBER,
3515 start_date_active DATE,
3516 end_date_active DATE,
3517 role_type VARCHAR2(10),
3518 category VARCHAR2(30),
3519 role_relate_id NUMBER);
3520
3521 TYPE same_group_member_role_tbl IS TABLE OF same_group_member_role_rec
3522 INDEX BY BINARY_INTEGER;
3523
3524 l_same_group_member_role same_group_member_role_tbl;
3525 l_diff_grp_parent_mbr_role same_group_member_role_tbl;
3526 l_diff_grp_child_mbr_role same_group_member_role_tbl;
3527
3528
3529 CURSOR grp_member_role(p_group_id IN NUMBER) IS
3530 SELECT mem.resource_id,
3531 mem.person_id,
3532 mem.group_id,
3533 rlt.role_id,
3534 rlt.start_date_active,
3535 rlt.end_date_active,
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
3545 AND nvl(mem.delete_flag,'N') <> 'Y'
3546 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
3547 AND rlt.role_resource_id = mem.group_member_id
3548 AND nvl(rlt.delete_flag,'N') <> 'Y'
3549 AND rlt.role_id = rol.role_id
3550 AND (nvl(rol.manager_flag, 'N') = 'Y'
3551 OR
3552 nvl(rol.admin_flag, 'N' ) = 'Y'
3553 OR
3554 nvl(rol.member_flag, 'N') = 'Y')
3555 AND mem.resource_id = rsc.resource_id;
3556
3557 CURSOR grp_member_mgr_role(p_group_id IN NUMBER) IS
3558 SELECT mem.resource_id,
3559 mem.person_id,
3560 mem.group_id,
3561 rlt.role_id,
3562 rlt.start_date_active,
3563 rlt.end_date_active,
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'
3573 AND rlt.role_resource_id = mem.group_member_id
3574 AND rlt.role_id = rol.role_id
3575 AND nvl(rlt.delete_flag,'N') <> 'Y'
3576 AND nvl(mem.delete_flag,'N') <> 'Y'
3577 AND mem.resource_id = rsc.resource_id
3578 AND mem.group_id = p_group_id
3579 AND nvl(rol.manager_flag,'N') = 'Y';
3580
3581 PROCEDURE load_processed_table IS
3582 k NUMBER := 0;
3583 l_denorm_manager_id number;
3584 skip_row exception ;
3585 l_start_date_active date;
3586 l_end_date_active date;
3587 l_count number := 0;
3588 BEGIN
3589 IF l_process_table.COUNT > 0 THEN
3590 k := l_process_table.FIRST;
3591 LOOP
3592 BEGIN
3593 l_start_date_active := to_date(to_char(l_process_table(k).START_DATE_ACTIVE,'DD-MM-RRRR'),'DD-MM-RRRR');
3594 l_end_date_active := to_date(to_char(nvl(l_process_table(k).END_DATE_ACTIVE,FND_API.G_MISS_DATE),
3595 'DD-MM-RRRR'),'DD-MM-RRRR');
3596 IF l_start_date_active > l_end_date_active THEN
3597 RAISE skip_row;
3598 END IF;
3599
3600 SELECT jtf_rs_rep_managers_s.nextval
3601 INTO l_denorm_manager_id
3602 FROM dual;
3603
3604 /* to_char(l_process_table(k).PERSON_ID)||'..'||
3605 l_process_table(k).CATEGORY||'..'||
3606 to_char(l_process_table(k).MANAGER_PERSON_ID)||'..'||
3607 to_char(l_process_table(k).GROUP_ID)||'..'||
3608 l_process_table(k).HIERARCHY_TYPE||'..'||
3609 l_process_table(k).REPORTS_TO_FLAG||'..'||
3610 to_char(l_process_table(k).START_DATE_ACTIVE, 'dd-MM-yyyy')||'..'||
3611 to_char(l_process_table(k).END_DATE_ACTIVE, 'dd-MM-yyyy')||'..'||
3612 to_char(l_process_table(k).PAR_ROLE_RELATE_ID)||'..'||
3613 to_char(l_process_table(k).CHILD_ROLE_RELATE_ID)||'..'||
3614 to_char(l_process_table(k).PARENT_RESOURCE_ID));
3615
3616 */
3617 INSERT INTO JTF_RS_REP_MANAGERS
3618 ( DENORM_MGR_ID,
3619 RESOURCE_ID,
3620 PERSON_ID,
3621 CATEGORY,
3622 MANAGER_PERSON_ID,
3623 GROUP_ID,
3624 HIERARCHY_TYPE,
3625 CREATED_BY,
3626 CREATION_DATE,
3627 LAST_UPDATED_BY,
3628 LAST_UPDATE_DATE,
3629 LAST_UPDATE_LOGIN,
3630 REPORTS_TO_FLAG,
3631 START_DATE_ACTIVE,
3632 END_DATE_ACTIVE,
3633 OBJECT_VERSION_NUMBER,
3634 PAR_ROLE_RELATE_ID,
3635 CHILD_ROLE_RELATE_ID,
3636 PARENT_RESOURCE_ID,
3637 DENORM_LEVEL)
3638 VALUES
3639 (L_DENORM_MANAGER_ID,
3640 l_process_table(k).RESOURCE_ID,
3641 l_process_table(k).PERSON_ID,
3642 l_process_table(k).CATEGORY,
3643 l_process_table(k).MANAGER_PERSON_ID,
3644 l_process_table(k).GROUP_ID,
3645 l_process_table(k).HIERARCHY_TYPE,
3646 l_user_id,
3647 l_date,
3648 l_user_id,
3649 l_date,
3650 l_login_id,
3651 l_process_table(k).REPORTS_TO_FLAG,
3652 trunc(l_process_table(k).START_DATE_ACTIVE),
3653 trunc(l_process_table(k).END_DATE_ACTIVE),
3654 1,
3655 l_process_table(k).PAR_ROLE_RELATE_ID,
3656 l_process_table(k).CHILD_ROLE_RELATE_ID,
3657 l_process_table(k).PARENT_RESOURCE_ID,
3658 p_denorm_level);
3659
3660
3661 IF fnd_api.to_boolean (p_commit)
3662 THEN
3663 l_count := l_count + 1;
3664 if (l_count > 1000)
3665 then
3666 COMMIT WORK;
3667 l_count := 0;
3668 end if;
3669 END IF;
3670
3671
3672 EXCEPTION when skip_row then null;
3673 END;
3674 EXIT WHEN k = l_process_table.LAST ;
3675 k := l_process_table.NEXT(k);
3676
3677 END LOOP;
3678 -- dbms_output.put_line (l_process_table.COUNT);
3679 END IF;
3680 NULL;
3681 END;
3682
3683
3684 PROCEDURE process_diff_group_member_role(p_immediate_parent_flag IN VARCHAR2,
3685 p_start_date_active IN DATE,
3686 p_end_date_active IN DATE) IS
3687 i number := 0;
3688 j number := 0;
3689 k number := 0;
3690 l_start_date_active date;
3691 l_end_date_active date;
3692 l_temp_fnd_end_date date;
3693 l_hierarchy_type varchar2(30);
3694 l_reports_to_flag varchar2(1);
3695 BEGIN
3696 IF l_process_table.COUNT > 0 THEN
3697 l_process_table.DELETE;
3698 END IF;
3699 IF l_diff_grp_parent_mbr_role.COUNT > 0 THEN
3700 i := l_diff_grp_parent_mbr_role.FIRST;
3701 LOOP
3702 IF l_diff_grp_child_mbr_role.COUNT > 0 THEN
3703 j := l_diff_grp_child_mbr_role.FIRST;
3704 LOOP
3705 k := k+1;
3706 l_hierarchy_type := l_diff_grp_parent_mbr_role(i).ROLE_TYPE||'_TO_'||l_diff_grp_child_mbr_role(j).ROLE_TYPE;
3707
3708 l_start_date_active := greatest(l_diff_grp_parent_mbr_role(i).start_date_active,
3709 l_diff_grp_child_mbr_role(j).start_date_active);
3710 l_start_date_active := greatest(l_start_date_active,p_start_date_active);
3711 l_temp_fnd_end_date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
3712 l_end_date_active := least(nvl(l_diff_grp_parent_mbr_role(i).end_date_active,l_temp_fnd_end_date),
3713 nvl(l_diff_grp_child_mbr_role(j).end_date_active,l_temp_fnd_end_date));
3714 l_end_date_active := least(l_end_date_active,nvl(p_end_date_active,l_temp_fnd_end_date));
3715
3716 IF l_end_date_active = l_temp_fnd_end_date THEN
3717 l_end_date_active := NULL;
3718 END IF;
3719
3720 IF l_hierarchy_type = 'MGR_TO_MGR' AND p_immediate_parent_flag = 'Y' THEN
3721 l_reports_to_flag := 'Y';
3722 ELSE
3723 l_reports_to_flag := 'N';
3724 END IF;
3725
3726 l_process_table(k).resource_id := l_diff_grp_child_mbr_role(j).resource_id;
3727 l_process_table(k).person_id := l_diff_grp_child_mbr_role(j).person_id;
3728 l_process_table(k).category := l_diff_grp_child_mbr_role(j).category;
3729 l_process_table(k).manager_person_id := l_diff_grp_parent_mbr_role(i).person_id;
3730 l_process_table(k).group_id := l_diff_grp_child_mbr_role(j).group_id;
3731 l_process_table(k).hierarchy_type := l_hierarchy_type;
3732 l_process_table(k).reports_to_flag := l_reports_to_flag;
3733 l_process_table(k).start_date_active := l_start_date_active;
3734 l_process_table(k).end_date_active := l_end_date_active;
3735 l_process_table(k).par_role_relate_id := l_diff_grp_parent_mbr_role(i).role_relate_id;
3736 l_process_table(k).child_role_relate_id := l_diff_grp_child_mbr_role(j).role_relate_id;
3737 l_process_table(k).parent_resource_id := l_diff_grp_parent_mbr_role(i).resource_id;
3738
3739 IF l_hierarchy_type = 'MGR_TO_MGR' THEN -- have a reverse record with parent group's Id.
3740 k := k+1;
3741 l_process_table(k).resource_id := l_diff_grp_child_mbr_role(j).resource_id;
3742 l_process_table(k).person_id := l_diff_grp_child_mbr_role(j).person_id;
3743 l_process_table(k).category := l_diff_grp_child_mbr_role(j).category;
3744 l_process_table(k).manager_person_id := l_diff_grp_parent_mbr_role(i).person_id;
3745 l_process_table(k).group_id := l_diff_grp_parent_mbr_role(i).group_id;
3746 l_process_table(k).hierarchy_type := l_hierarchy_type;
3747 l_process_table(k).reports_to_flag := l_reports_to_flag;
3748 l_process_table(k).start_date_active := l_start_date_active;
3749 l_process_table(k).end_date_active := l_end_date_active;
3750 l_process_table(k).par_role_relate_id := l_diff_grp_parent_mbr_role(i).role_relate_id;
3751 l_process_table(k).child_role_relate_id := l_diff_grp_child_mbr_role(j).role_relate_id;
3752 l_process_table(k).parent_resource_id := l_diff_grp_parent_mbr_role(i).resource_id;
3753 END IF;
3754
3755 EXIT WHEN j = l_diff_grp_child_mbr_role.LAST;
3756 j := l_diff_grp_child_mbr_role.NEXT(j);
3757 END LOOP;
3758 END IF;
3759 EXIT WHEN i = l_diff_grp_parent_mbr_role.LAST;
3760 i := l_diff_grp_parent_mbr_role.NEXT(i);
3761 END LOOP;
3762 END IF;
3763
3764 load_processed_table;
3765 END;
3766
3767
3768 BEGIN
3769 SAVEPOINT member_denormalize;
3770 --Standard Call to check API compatibility
3771 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
3772 THEN
3773 RAISE FND_API.G_EXC_ERROR;
3774 END IF;
3775
3776 x_return_status := fnd_api.g_ret_sts_success;
3777
3778 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3779 IF FND_API.To_boolean(P_INIT_MSG_LIST)
3780 THEN
3781 FND_MSG_PUB.Initialize;
3782 END IF;
3783
3784
3785 l_date := sysdate;
3786 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3787 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
3788
3789 IF(p_group_id <> p_parent_group_id)
3790 THEN
3791 --dbms_output.put_line('xxx'||to_char(p_group_id));
3792 IF l_diff_grp_parent_mbr_role.COUNT > 0 THEN
3793 l_diff_grp_parent_mbr_role.DELETE;
3794 END IF;
3795 IF l_diff_grp_child_mbr_role.COUNT > 0 THEN
3796 l_diff_grp_child_mbr_role.DELETE;
3797 END IF;
3798 FOR l_grp_member_role IN grp_member_role(p_group_id) LOOP
3799 j := j+1;
3800 l_diff_grp_child_mbr_role(j).resource_id := l_grp_member_role.resource_id;
3801 l_diff_grp_child_mbr_role(j).person_id := l_grp_member_role.person_id;
3802 l_diff_grp_child_mbr_role(j).group_id := l_grp_member_role.group_id;
3803 l_diff_grp_child_mbr_role(j).role_id := l_grp_member_role.role_id;
3804 l_diff_grp_child_mbr_role(j).start_date_active := l_grp_member_role.start_date_active;
3805 l_diff_grp_child_mbr_role(j).end_date_active := l_grp_member_role.end_date_active;
3806 l_diff_grp_child_mbr_role(j).role_type := l_grp_member_role.role_type;
3807 l_diff_grp_child_mbr_role(j).category := l_grp_member_role.category;
3808 l_diff_grp_child_mbr_role(j).role_relate_id := l_grp_member_role.role_relate_id;
3809 END LOOP;
3810
3811 FOR l_grp_member_mgr_role IN grp_member_mgr_role(p_parent_group_id) LOOP
3812 k := k+1;
3813 l_diff_grp_parent_mbr_role(k).resource_id := l_grp_member_mgr_role.resource_id;
3814 l_diff_grp_parent_mbr_role(k).person_id := l_grp_member_mgr_role.person_id;
3815 l_diff_grp_parent_mbr_role(k).group_id := l_grp_member_mgr_role.group_id;
3816 l_diff_grp_parent_mbr_role(k).role_id := l_grp_member_mgr_role.role_id;
3817 l_diff_grp_parent_mbr_role(k).start_date_active := l_grp_member_mgr_role.start_date_active;
3818 l_diff_grp_parent_mbr_role(k).end_date_active := l_grp_member_mgr_role.end_date_active;
3819 l_diff_grp_parent_mbr_role(k).role_type := l_grp_member_mgr_role.role_type;
3820 l_diff_grp_parent_mbr_role(k).category := l_grp_member_mgr_role.category;
3821 l_diff_grp_parent_mbr_role(k).role_relate_id := l_grp_member_mgr_role.role_relate_id;
3822 END LOOP;
3823
3824 process_diff_group_member_role(p_immediate_parent_flag,
3825 p_start_date_active,
3826 p_end_date_active);
3827
3828
3829 END IF; --end of p_group_id = p_parent_group_id check
3830
3831 IF fnd_api.to_boolean (p_commit)
3832 THEN
3833 COMMIT WORK;
3834 END IF;
3835
3836 EXCEPTION
3837 WHEN fnd_api.g_exc_unexpected_error
3838 THEN
3839 ROLLBACK TO member_denormalize;
3840 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3841 WHEN fnd_api.g_exc_error
3842 THEN
3843 ROLLBACK TO member_denormalize;
3844 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3845 WHEN OTHERS
3846 THEN
3847 ROLLBACK TO member_denormalize;
3848 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3849 fnd_message.set_token('P_SQLCODE',SQLCODE);
3850 fnd_message.set_token('P_SQLERRM',SQLERRM);
3851 fnd_message.set_token('P_API_NAME',l_api_name);
3852 FND_MSG_PUB.add;
3853 x_return_status := fnd_api.g_ret_sts_unexp_error;
3854 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3855 END INSERT_GRP_DENORM;
3856
3857
3858 PROCEDURE DELETE_REP_MGR (
3859 P_API_VERSION IN NUMBER,
3860 P_INIT_MSG_LIST IN VARCHAR2,
3861 P_COMMIT IN VARCHAR2,
3862 P_GROUP_ID IN JTF_RS_GROUPS_DENORM.GROUP_ID%TYPE,
3863 P_PARENT_GROUP_ID IN JTF_RS_GROUPS_DENORM.PARENT_GROUP_ID%TYPE,
3864 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3865 X_MSG_COUNT OUT NOCOPY NUMBER,
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'
3875 AND nvl(mem.delete_flag,'N') <> 'Y'
3876 AND mem.group_id = l_group_id;
3877
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'
3887 AND nvl(mem.delete_flag,'N') <> 'Y'
3888 AND mem.group_id = l_parent_group_id;
3889
3890 r_parent_role_relate_rec c_parent_role_relate_cur%rowtype;
3891
3892 --Declare the variables
3893 --
3894 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REP_MGR';
3895 l_api_version CONSTANT NUMBER :=1.0;
3896 l_date Date;
3897 l_user_id Number;
3898 l_login_id Number;
3899
3900 l_return_status VARCHAR2(200);
3901 l_msg_count NUMBER;
3902 l_msg_data VARCHAR2(200);
3903
3904 BEGIN
3905
3906 --Standard Start of API SAVEPOINT
3907 SAVEPOINT DEL_REP_MGR_SP;
3908
3909 x_return_status := fnd_api.g_ret_sts_success;
3910
3911 --Standard Call to check API compatibility
3912 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME) THEN
3913 RAISE FND_API.G_EXC_ERROR;
3914 END IF;
3915
3916 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3917 IF FND_API.To_boolean(P_INIT_MSG_LIST) THEN
3918 FND_MSG_PUB.Initialize;
3919 END IF;
3920
3921
3922 l_date := sysdate;
3923 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3924 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
3925
3926 --get all the child role relate id for this group
3927 OPEN c_child_role_relate_cur(p_group_id);
3928 FETCH c_child_role_relate_cur INTO r_child_role_relate_rec;
3929 WHILE(c_child_role_relate_cur%found)
3930 LOOP
3931
3932 OPEN c_parent_role_relate_cur(p_parent_group_id);
3933 FETCH c_parent_role_relate_cur INTO r_parent_role_relate_rec;
3934 WHILE(c_parent_role_relate_cur%found)
3935 LOOP
3936 DELETE JTF_RS_REP_MANAGERS
3937 WHERE child_role_relate_id = r_child_role_relate_rec.role_relate_id
3938 AND par_role_relate_id = r_parent_role_relate_rec.role_relate_id ;
3939
3940 FETCH c_parent_role_relate_cur INTO r_parent_role_relate_rec;
3941 END LOOP;
3942 CLOSE c_parent_role_relate_cur;
3943 FETCH c_child_role_relate_cur INTO r_child_role_relate_rec;
3944 END LOOP; --end of par_mgr_cur
3945 CLOSE c_child_role_relate_cur;
3946
3947 IF fnd_api.to_boolean (p_commit) THEN
3948 COMMIT WORK;
3949 END IF;
3950
3951 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3952
3953 EXCEPTION
3954 WHEN fnd_api.g_exc_unexpected_error THEN
3955 ROLLBACK TO del_rep_mgr_sp;
3956 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3957 WHEN fnd_api.g_exc_error THEN
3958 ROLLBACK TO del_rep_mgr_sp;
3959 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3960 WHEN OTHERS THEN
3961 ROLLBACK TO del_rep_mgr_sp;
3962 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3963 fnd_message.set_token('P_SQLCODE',SQLCODE);
3964 fnd_message.set_token('P_SQLERRM',SQLERRM);
3965 fnd_message.set_token('P_API_NAME',l_api_name);
3966 FND_MSG_PUB.add;
3967 x_return_status := fnd_api.g_ret_sts_unexp_error;
3968 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3969
3970 END DELETE_REP_MGR;
3971
3972
3973 /*FOR INSERT IN JTF_RS_GRP_RELATIONS */
3974 --not being used now as this id done from group denorm which calls INSERT_GRP_DENORM
3975 PROCEDURE INSERT_GRP_RELATIONS(
3976 P_API_VERSION IN NUMBER,
3977 P_INIT_MSG_LIST IN VARCHAR2,
3978 P_COMMIT IN VARCHAR2,
3979 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
3980 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3981 X_MSG_COUNT OUT NOCOPY NUMBER,
3982 X_MSG_DATA OUT NOCOPY VARCHAR2 )
3983 IS
3984 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE := p_group_relate_id;
3985 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GRP_RELATIONS';
3986 l_api_version CONSTANT NUMBER :=1.0;
3987 l_date Date;
3988 l_user_id Number;
3989 l_login_id Number;
3990
3991 l_hierarchy_type JTF_RS_REP_MANAGERS.HIERARCHY_TYPE%TYPE;
3992 l_reports_to_flag JTF_RS_REP_MANAGERS.REPORTS_TO_FLAG%TYPE := 'N';
3993 x_row_id VARCHAR2(100);
3994 l_start_date_active DATE;
3995 l_end_date_active DATE;
3996
3997 --cursor for the direct parent
3998 CURSOR rel_grp_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE)
3999 IS
4000 SELECT related_group_id,
4001 group_id,
4002 start_date_active,
4003 end_date_active
4004 FROM jtf_rs_grp_relations
4005 WHERE group_relate_id = l_group_relate_id
4006 and delete_flag <> 'Y';
4007
4008 rel_grp_rec rel_grp_cur%rowtype;
4009
4010 l_related_group_id JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE;
4011
4012 CURSOR par_mgr_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
4013 l_start_date_active DATE,
4014 l_end_date_active DATE)
4015 IS
4016 SELECT mem.resource_id,
4017 mem.person_id,
4018 mem.group_id,
4019 rlt.role_relate_id,
4020 rlt.role_id,
4021 rlt.start_date_active,
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)
4031 /* this has been added to include all parents in the hierarchy */
4032 AND mem.group_member_id = rlt.role_resource_id
4033 AND nvl(mem.delete_flag,'N') <> 'Y'
4034 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
4035 AND rlt.role_id = rol.role_id
4036 AND nvl(rlt.delete_flag, 'N') <> 'Y'
4037 AND (rol.admin_flag = 'Y'
4038 OR manager_flag = 'Y')
4039 AND ((l_start_date_active between rlt.start_date_active
4040 and nvl(rlt.end_date_active , l_start_date_active +1))
4041 OR ((nvl(l_end_date_active, rlt.start_date_active +1)
4042 between rlt.start_date_active and
4043 nvl(rlt.end_date_active, l_end_date_active + 1))
4044 or (l_end_date_active is null and rlt.end_date_active is null)));
4045
4046 par_mgr_rec par_mgr_cur%rowtype;
4047
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,
4057 p_manager_flag JTF_RS_ROLES_B.MANAGER_FLAG%TYPE);
4058
4059
4060 TYPE mgr_tab_type IS TABLE OF mgr_type INDEX BY BINARY_INTEGER;
4061 l_mgr_rec MGR_TAB_TYPE;
4062 query_str VARCHAR2(20000);
4063 i BINARY_INTEGER := 0;
4064
4065
4066 CURSOR child_grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
4067 IS
4068 SELECT group_id,
4069 start_date_active,
4070 end_date_active,
4071 immediate_parent_flag
4072 FROM jtf_rs_groups_denorm
4073 WHERE parent_group_id = l_group_id
4074 AND group_id NOT IN (l_group_id);
4075
4076 child_grp_rec child_grp_cur%ROWTYPE;
4077
4078 CURSOR member_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
4079 l_start_date_active DATE,
4080 l_end_date_active DATE)
4081 IS
4082 SELECT mem.resource_id,
4083 mem.person_id,
4084 mem.group_id,
4085 rlt.role_relate_id,
4086 rlt.role_id,
4087 rlt.start_date_active,
4088 rlt.end_date_active,
4089 rol.member_flag ,
4090 rol.admin_flag ,
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
4100 AND nvl(rlt.delete_flag,'N') <> 'Y'
4101 AND nvl(mem.delete_flag,'N') <> 'Y'
4102 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
4103 AND rlt.role_id = rol.role_id
4104 AND mem.resource_id = rsc.resource_id
4105 AND ((rlt.start_date_active between l_start_date_active and
4106 nvl(l_end_date_active ,rlt.start_date_active+1))
4107 OR (rlt.end_date_active between l_start_date_active
4108 and nvl(l_end_date_active,rlt.end_date_active+1))
4109 OR ((rlt.start_date_active <= l_start_date_active)
4110 AND (rlt.end_date_active >= l_end_date_active
4111 OR l_end_date_active IS NULL)));
4112
4113 /* SELECT mem.resource_id,
4114 mem.person_id,
4115 mem.group_id,
4116 rlt.role_relate_id,
4117 rlt.role_id,
4118 rlt.start_date_active,
4119 rlt.end_date_active,
4120 rol.member_flag ,
4121 rol.admin_flag ,
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
4131 AND nvl(rlt.delete_flag,'N') <> 'Y'
4132 AND nvl(mem.delete_flag,'N') <> 'Y'
4133 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
4134 AND rlt.role_id = rol.role_id
4135 AND mem.resource_id = rsc.resource_id
4136 AND rlt.start_date_active >= to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')
4137 AND ((to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy') between rlt.start_date_active
4138 and nvl(rlt.end_date_active , to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')+1))
4139 OR ((nvl(to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy'), rlt.start_date_active +1)
4140 between rlt.start_date_active and
4141 nvl(rlt.end_date_active, to_date(to_char(l_end_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')+ 1))
4142 or (to_date(to_char(l_end_date_active,'dd-MM-yyyy'),'dd-MM-yyyy') is null and rlt.end_date_active is null)));
4143 */
4144
4145
4146
4147 member_rec member_cur%rowtype;
4148
4149 CURSOR rep_mgr_seq_cur
4150 IS
4151 SELECT jtf_rs_rep_managers_s.nextval
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
4161 FROM jtf_rs_rep_managers
4162 WHERE par_role_relate_id = l_par_role_relate_id
4163 AND child_role_relate_id = l_child_role_relate_id
4164 AND group_id = l_group_id;
4165 /*
4166 AND ((l_start_date_active between start_date_active and
4167 nvl(end_date_active,l_start_date_active+1))
4168 OR (l_end_date_active between start_date_active
4169 and nvl(end_date_active,l_end_date_active+1))
4170 OR ((l_start_date_active <= start_date_active)
4171 AND (l_end_date_active >= end_date_active
4172 OR l_end_date_active IS NULL)));
4173
4174 */
4175
4176
4177
4178 dup NUMBER := 0;
4179
4180
4181 l_denorm_mgr_id JTF_RS_REP_MANAGERS.DENORM_MGR_ID%TYPE;
4182 l_commit number := 0;
4183
4184 l_count number := 0;
4185 BEGIN
4186 --Standard Start of API SAVEPOINT
4187 SAVEPOINT member_denormalize;
4188
4189 x_return_status := fnd_api.g_ret_sts_success;
4190
4191 --Standard Call to check API compatibility
4192 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
4193 THEN
4194 RAISE FND_API.G_EXC_ERROR;
4195 END IF;
4196
4197 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
4198 IF FND_API.To_boolean(P_INIT_MSG_LIST)
4199 THEN
4200 FND_MSG_PUB.Initialize;
4201 END IF;
4202
4203
4204 l_date := sysdate;
4205 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
4206 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
4207
4208 --
4209 OPEN rel_grp_cur(l_group_relate_id);
4210 FETCH rel_grp_cur INTO rel_grp_rec;
4211 CLOSE rel_grp_cur;
4212
4213 --pick up all the managers and admin for the parent group
4214 OPEN par_mgr_cur(rel_grp_rec.related_group_id,
4215 rel_grp_rec.start_date_active,
4216 rel_grp_rec.end_date_active);
4217
4218 FETCH par_mgr_cur INTO par_mgr_rec;
4219 WHILE(par_mgr_cur%FOUND)
4220 LOOP
4221 --dbms_output.put_line('h2');
4222 i := i + 1;
4223 l_mgr_rec(i).p_resource_id := par_mgr_rec.resource_id;
4224 l_mgr_rec(i).p_person_id := par_mgr_rec.person_id;
4225 l_mgr_rec(i).p_group_id := par_mgr_rec.group_id;
4226 l_mgr_rec(i).p_role_relate_id := par_mgr_rec.role_relate_id;
4227 l_mgr_rec(i).p_role_id := par_mgr_rec.role_id;
4228 l_mgr_rec(i).p_start_date_active := par_mgr_rec.start_date_active;
4229 l_mgr_rec(i).p_end_date_active := par_mgr_rec.end_date_active;
4230 l_mgr_rec(i).p_admin_flag := par_mgr_rec.admin_flag;
4231 l_mgr_rec(i).p_manager_flag := par_mgr_rec.manager_flag;
4232
4233
4234 FETCH par_mgr_cur INTO par_mgr_rec;
4235
4236 END LOOP; --end of par_mgr_cur
4237 CLOSE par_mgr_cur;
4238 --insert records for the same group for this parent
4239 OPEN member_cur(rel_grp_rec.group_id,
4240 rel_grp_rec.start_date_active,
4241 rel_grp_rec.end_date_active);
4242 FETCH member_cur INTO member_rec;
4243
4244 WHILE(member_cur%FOUND)
4245 LOOP
4246
4247 --dbms_output.put_line('h3');
4248 --insert records for all the members of the group
4249 i := 0;
4250 FOR I IN 1 .. l_mgr_rec.COUNT
4251 LOOP
4252 IF(rel_grp_rec.related_group_id = l_mgr_rec(i).p_group_id)
4253 THEN
4254
4255 IF(nvl(member_rec.manager_flag,'N') = 'Y')
4256 THEN
4257 l_reports_to_flag := 'Y';
4258 ELSE
4259 l_reports_to_flag := 'N';
4260 END IF;
4261 ELSE
4262 l_reports_to_flag := 'N';
4263 END IF;
4264
4265 --assign start date and end date for which this relation is valid
4266 IF(member_rec.start_date_active < l_mgr_rec(i).p_start_date_active)
4267 THEN
4268 l_start_date_active := l_mgr_rec(i).p_start_date_active;
4269 ELSE
4270 l_start_date_active := member_rec.start_date_active;
4271 END IF;
4272
4273 IF(member_rec.end_date_active > l_mgr_rec(i).p_end_date_active)
4274 THEN
4275 l_end_date_active := l_mgr_rec(i).p_end_date_active;
4276 ELSIF(l_mgr_rec(i).p_end_date_active IS NULL)
4277 THEN
4278 l_end_date_active :=member_rec.end_date_active;
4279 ELSIF(member_rec.end_date_active IS NULL)
4280 THEN
4281 l_end_date_active := l_mgr_rec(i).p_end_date_active;
4282 END IF;
4283
4284 --set the hierarchy type if of type manager
4285 IF l_mgr_rec(i).p_manager_flag = 'Y'
4286 THEN
4287 IF member_rec.manager_flag = 'Y'
4288 THEN
4289 l_hierarchy_type := 'MGR_TO_MGR';
4290 ELSIF member_rec.admin_flag = 'Y'
4291 THEN
4292 l_hierarchy_type := 'MGR_TO_ADMIN';
4293 ELSE
4294 l_hierarchy_type := 'MGR_TO_REP';
4295 END IF;
4296
4297 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
4298 then
4299
4300 open dup_cur2(l_mgr_rec(i).P_role_relate_id ,
4301 member_rec.role_relate_id,
4302 member_rec.group_id);
4303 fetch dup_cur2 INTO dup;
4304 IF(dup_cur2%NOTFOUND)
4305 THEN
4306 --CALL TABLE HANDLER FOR INSETING IN REP MANAGER
4307 --dbms_output.put_line('h4');
4308 OPEN rep_mgr_seq_cur;
4309 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4310 CLOSE rep_mgr_seq_cur;
4311
4312 jtf_rs_rep_managers_pkg.insert_row(
4313 X_ROWID => x_row_id,
4314 X_DENORM_MGR_ID => l_denorm_mgr_id,
4315 X_RESOURCE_ID => member_rec.resource_id,
4316 X_PERSON_ID => member_rec.person_id,
4317 X_CATEGORY => member_rec.category,
4318 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4319 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4320 X_GROUP_ID => member_rec.group_id,
4321 X_REPORTS_TO_FLAG => l_reports_to_flag,
4322 X_HIERARCHY_TYPE => l_hierarchy_type,
4323 X_START_DATE_ACTIVE => trunc(l_start_date_active),
4324 X_END_DATE_ACTIVE => trunc(l_end_date_active),
4325 X_ATTRIBUTE2 => null,
4326 X_ATTRIBUTE3 => null,
4327 X_ATTRIBUTE4 => null,
4328 X_ATTRIBUTE5 => null,
4329 X_ATTRIBUTE6 => null,
4330 X_ATTRIBUTE7 => null,
4331 X_ATTRIBUTE8 => null,
4332 X_ATTRIBUTE9 => null,
4333 X_ATTRIBUTE10 => null,
4334 X_ATTRIBUTE11 => null,
4335 X_ATTRIBUTE12 => null,
4336 X_ATTRIBUTE13 => null,
4337 X_ATTRIBUTE14 => null,
4338 X_ATTRIBUTE15 => null,
4339 X_ATTRIBUTE_CATEGORY => null,
4340 X_ATTRIBUTE1 => null,
4341 X_CREATION_DATE => l_date,
4342 X_CREATED_BY => l_user_id,
4343 X_LAST_UPDATE_DATE => l_date,
4344 X_LAST_UPDATED_BY => l_user_id,
4345 X_LAST_UPDATE_LOGIN => l_login_id,
4346 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
4347 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4348
4349 IF fnd_api.to_boolean (p_commit)
4350 THEN
4351 l_count := l_count + 1;
4352 if (l_count > 1000)
4353 then
4354 COMMIT WORK;
4355 l_count := 0;
4356 end if;
4357 END IF;
4358
4359
4360 --insert the reverse record if manager flag = 'Y'
4361 IF member_rec.manager_flag = 'Y'
4362 THEN
4363 OPEN rep_mgr_seq_cur;
4364 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4365 CLOSE rep_mgr_seq_cur;
4366
4367 jtf_rs_rep_managers_pkg.insert_row(
4368 X_ROWID => x_row_id,
4369 X_DENORM_MGR_ID => l_denorm_mgr_id,
4370 X_RESOURCE_ID => member_rec.resource_id,
4371 X_PERSON_ID => member_rec.person_id,
4372 X_CATEGORY => member_rec.category,
4373 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4374 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4375 X_GROUP_ID => l_mgr_rec(i).p_group_id,
4376 X_REPORTS_TO_FLAG => l_reports_to_flag,
4377 X_HIERARCHY_TYPE => l_hierarchy_type,
4378 X_START_DATE_ACTIVE => trunc(l_start_date_active),
4379 X_END_DATE_ACTIVE => trunc(l_end_date_active),
4380 X_ATTRIBUTE2 => null,
4381 X_ATTRIBUTE3 => null,
4382 X_ATTRIBUTE4 => null,
4383 X_ATTRIBUTE5 => null,
4384 X_ATTRIBUTE6 => null,
4385 X_ATTRIBUTE7 => null,
4386 X_ATTRIBUTE8 => null,
4387 X_ATTRIBUTE9 => null,
4388 X_ATTRIBUTE10 => null,
4389 X_ATTRIBUTE11 => null,
4390 X_ATTRIBUTE12 => null,
4391 X_ATTRIBUTE13 => null,
4392 X_ATTRIBUTE14 => null,
4393 X_ATTRIBUTE15 => null,
4394 X_ATTRIBUTE_CATEGORY => null,
4395 X_ATTRIBUTE1 => null,
4396 X_CREATION_DATE => l_date,
4397 X_CREATED_BY => l_user_id,
4398 X_LAST_UPDATE_DATE => l_date,
4399 X_LAST_UPDATED_BY => l_user_id,
4400 X_LAST_UPDATE_LOGIN => l_login_id,
4401 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
4402 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4403
4404 IF fnd_api.to_boolean (p_commit)
4405 THEN
4406 l_count := l_count + 1;
4407 if (l_count > 1000)
4408 then
4409 COMMIT WORK;
4410 l_count := 0;
4411 end if;
4412 END IF;
4413
4414
4415 END IF; --end of reverse record insert
4416 END IF; --end of duplicate check
4417 close dup_cur2;
4418
4419 END IF; --end of st dt and end dt check
4420 END IF; --MANAGER FLAG END
4421
4422
4423 IF l_mgr_rec(i).p_admin_flag = 'Y'
4424 THEN
4425 IF member_rec.manager_flag = 'Y'
4426 THEN
4427 l_hierarchy_type := 'ADMIN_TO_MGR';
4428 ELSIF member_rec.admin_flag = 'Y'
4429 THEN
4430 l_hierarchy_type := 'ADMIN_TO_ADMIN';
4431 ELSE
4432 l_hierarchy_type := 'ADMIN_TO_REP';
4433 END IF;
4434
4435 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
4436 then
4437 open dup_cur2(l_mgr_rec(i).P_role_relate_id ,
4438 member_rec.role_relate_id,
4439 member_rec.group_id);
4440 fetch dup_cur2 INTO dup;
4441 IF(dup_cur2%NOTFOUND)
4442 THEN
4443 --CALL TABLE HANDLER FOR INSERTING IN REP MANAGER
4444 OPEN rep_mgr_seq_cur;
4445 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4446 CLOSE rep_mgr_seq_cur;
4447
4448 jtf_rs_rep_managers_pkg.insert_row(
4449 X_ROWID => x_row_id,
4450 X_DENORM_MGR_ID => l_denorm_mgr_id,
4451 X_RESOURCE_ID => member_rec.resource_id,
4452 X_PERSON_ID => member_rec.person_id,
4453 X_CATEGORY => member_rec.category,
4454 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4455 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4456 X_GROUP_ID => member_rec.group_id,
4457 X_REPORTS_TO_FLAG => l_reports_to_flag,
4458 X_HIERARCHY_TYPE => l_hierarchy_type,
4459 X_START_DATE_ACTIVE => trunc(l_start_date_active),
4460 X_END_DATE_ACTIVE => trunc(l_end_date_active),
4461 X_ATTRIBUTE2 => null,
4462 X_ATTRIBUTE3 => null,
4463 X_ATTRIBUTE4 => null,
4464 X_ATTRIBUTE5 => null,
4465 X_ATTRIBUTE6 => null,
4466 X_ATTRIBUTE7 => null,
4467 X_ATTRIBUTE8 => null,
4468 X_ATTRIBUTE9 => null,
4469 X_ATTRIBUTE10 => null,
4470 X_ATTRIBUTE11 => null,
4471 X_ATTRIBUTE12 => null,
4472 X_ATTRIBUTE13 => null,
4473 X_ATTRIBUTE14 => null,
4474 X_ATTRIBUTE15 => null,
4475 X_ATTRIBUTE_CATEGORY => null,
4476 X_ATTRIBUTE1 => null,
4477 X_CREATION_DATE => l_date,
4478 X_CREATED_BY => l_user_id,
4479 X_LAST_UPDATE_DATE => l_date,
4480 X_LAST_UPDATED_BY => l_user_id,
4481 X_LAST_UPDATE_LOGIN => l_login_id,
4482 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).p_role_relate_id,
4483 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4484
4485 IF fnd_api.to_boolean (p_commit)
4486 THEN
4487 l_count := l_count + 1;
4488 if (l_count > 1000)
4489 then
4490 COMMIT WORK;
4491 l_count := 0;
4492 end if;
4493 END IF;
4494
4495 END IF; --end of dup check
4496 close dup_cur2;
4497 END IF; --st dt check
4498 END IF; --ADMIN FLAG END
4499 END LOOP; --end of for loop for all managers and admin of parent table stored in pl/sql table
4500 FETCH member_cur INTO member_rec;
4501 END LOOP; --member cur
4502 CLOSE member_cur;
4503
4504 --end of insert record for the same group and this parent
4505
4506
4507 --open child group cursor
4508 OPEN child_grp_cur(rel_grp_rec.group_id);
4509 FETCH child_grp_cur INTO child_grp_rec;
4510 WHILE(child_grp_cur%FOUND)
4511 LOOP
4512
4513 OPEN member_cur(child_grp_rec.group_id,
4514 rel_grp_rec.start_date_active,
4515 rel_grp_rec.end_date_active);
4516 FETCH member_cur INTO member_rec;
4517 WHILE(member_cur%FOUND)
4518 LOOP
4519 --insert records for all the members of the child group
4520 /*IF((child_grp_rec.immediate_parent_flag = 'Y')
4521 AND (nvl(member_rec.manager_flag,'N') = 'Y'))
4522 THEN
4523 l_reports_to_flag := 'Y';
4524 ELSE
4525 l_reports_to_flag := 'N';
4526 END IF;*/
4527
4528 l_reports_to_flag := 'N';
4529
4530
4531 i := 0;
4532 FOR I IN 1 .. l_mgr_rec.COUNT
4533 LOOP
4534 --assign start date and end date for which this relation is valid
4535 IF(member_rec.start_date_active < l_mgr_rec(i).p_start_date_active)
4536 THEN
4537 l_start_date_active := l_mgr_rec(i).p_start_date_active;
4538 ELSE
4539 l_start_date_active := member_rec.start_date_active;
4540 END IF;
4541
4542 IF(member_rec.end_date_active > l_mgr_rec(i).p_end_date_active)
4543 THEN
4544 l_end_date_active := l_mgr_rec(i).p_end_date_active;
4545 ELSIF(l_mgr_rec(i).p_end_date_active IS NULL)
4546 THEN
4547 l_end_date_active :=member_rec.end_date_active;
4548 ELSIF(member_rec.end_date_active IS NULL)
4549 THEN
4550 l_end_date_active := l_mgr_rec(i).p_end_date_active;
4551 END IF;
4552
4553 --set the hierarchy type if of type manager
4554 IF l_mgr_rec(i).p_manager_flag = 'Y'
4555 THEN
4556 IF member_rec.manager_flag = 'Y'
4557 THEN
4558 l_hierarchy_type := 'MGR_TO_MGR';
4559 ELSIF member_rec.admin_flag = 'Y'
4560 THEN
4561 l_hierarchy_type := 'MGR_TO_ADMIN';
4562 ELSE
4563 l_hierarchy_type := 'MGR_TO_REP';
4564 END IF;
4565 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
4566 then
4567 open dup_cur2(l_mgr_rec(i).p_role_relate_id ,
4568 member_rec.role_relate_id,
4569 member_rec.group_id);
4570 fetch dup_cur2 INTO dup;
4571 IF(dup_cur2%NOTFOUND)
4572 THEN
4573 --CALL TABLE HANDLER FOR INSETING IN REP MANAGER
4574 OPEN rep_mgr_seq_cur;
4575 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4576 CLOSE rep_mgr_seq_cur;
4577
4578 jtf_rs_rep_managers_pkg.insert_row(
4579 X_ROWID => x_row_id,
4580 X_DENORM_MGR_ID => l_denorm_mgr_id,
4581 X_RESOURCE_ID => member_rec.resource_id,
4582 X_PERSON_ID => member_rec.person_id,
4583 X_CATEGORY => member_rec.category,
4584 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4585 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4586 X_GROUP_ID => member_rec.group_id,
4587 X_REPORTS_TO_FLAG => l_reports_to_flag,
4588 X_HIERARCHY_TYPE => l_hierarchy_type,
4589 X_START_DATE_ACTIVE => trunc(l_start_date_active),
4590 X_END_DATE_ACTIVE => trunc(l_end_date_active),
4591 X_ATTRIBUTE2 => null,
4592 X_ATTRIBUTE3 => null,
4593 X_ATTRIBUTE4 => null,
4594 X_ATTRIBUTE5 => null,
4595 X_ATTRIBUTE6 => null,
4596 X_ATTRIBUTE7 => null,
4597 X_ATTRIBUTE8 => null,
4598 X_ATTRIBUTE9 => null,
4599 X_ATTRIBUTE10 => null,
4600 X_ATTRIBUTE11 => null,
4601 X_ATTRIBUTE12 => null,
4602 X_ATTRIBUTE13 => null,
4603 X_ATTRIBUTE14 => null,
4604 X_ATTRIBUTE15 => null,
4605 X_ATTRIBUTE_CATEGORY => null,
4606 X_ATTRIBUTE1 => null,
4607 X_CREATION_DATE => l_date,
4608 X_CREATED_BY => l_user_id,
4609 X_LAST_UPDATE_DATE => l_date,
4610 X_LAST_UPDATED_BY => l_user_id,
4611 X_LAST_UPDATE_LOGIN => l_login_id,
4612 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
4613 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4614
4615 IF fnd_api.to_boolean (p_commit)
4616 THEN
4617 l_count := l_count + 1;
4618 if (l_count > 1000)
4619 then
4620 COMMIT WORK;
4621 l_count := 0;
4622 end if;
4623 END IF;
4624
4625
4626
4627 --INSERT REVERSE RECORD FOR MGR_TO_MGR
4628
4629 IF member_rec.manager_flag = 'Y'
4630 THEN
4631
4632 OPEN rep_mgr_seq_cur;
4633 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4634 CLOSE rep_mgr_seq_cur;
4635
4636 jtf_rs_rep_managers_pkg.insert_row(
4637 X_ROWID => x_row_id,
4638 X_DENORM_MGR_ID => l_denorm_mgr_id,
4639 X_RESOURCE_ID => member_rec.resource_id,
4640 X_PERSON_ID => member_rec.person_id,
4641 X_CATEGORY => member_rec.category,
4642 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4643 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4644 X_GROUP_ID => l_mgr_rec(i).p_group_id,
4645 X_REPORTS_TO_FLAG => l_reports_to_flag,
4646 X_HIERARCHY_TYPE => l_hierarchy_type,
4647 X_START_DATE_ACTIVE => trunc(l_start_date_active),
4648 X_END_DATE_ACTIVE => trunc(l_end_date_active),
4649 X_ATTRIBUTE2 => null,
4650 X_ATTRIBUTE3 => null,
4651 X_ATTRIBUTE4 => null,
4652 X_ATTRIBUTE5 => null,
4653 X_ATTRIBUTE6 => null,
4654 X_ATTRIBUTE7 => null,
4655 X_ATTRIBUTE8 => null,
4656 X_ATTRIBUTE9 => null,
4657 X_ATTRIBUTE10 => null,
4658 X_ATTRIBUTE11 => null,
4659 X_ATTRIBUTE12 => null,
4660 X_ATTRIBUTE13 => null,
4661 X_ATTRIBUTE14 => null,
4662 X_ATTRIBUTE15 => null,
4663 X_ATTRIBUTE_CATEGORY => null,
4664 X_ATTRIBUTE1 => null,
4665 X_CREATION_DATE => l_date,
4666 X_CREATED_BY => l_user_id,
4667 X_LAST_UPDATE_DATE => l_date,
4668 X_LAST_UPDATED_BY => l_user_id,
4669 X_LAST_UPDATE_LOGIN => l_login_id,
4670 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
4671 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4672
4673 IF fnd_api.to_boolean (p_commit)
4674 THEN
4675 l_count := l_count + 1;
4676 if (l_count > 1000)
4677 then
4678 COMMIT WORK;
4679 l_count := 0;
4680 end if;
4681 END IF;
4682
4683 END IF; --END OF MGR_TO_MGR REVERSE RECORD POSTING
4684 END IF; --end of dup check
4685 close dup_cur2;
4686 END IF; --end of st dt check
4687 END IF; --MANAGER FLAG END
4688
4689
4690 IF l_mgr_rec(i).p_admin_flag = 'Y'
4691 THEN
4692 IF member_rec.manager_flag = 'Y'
4693 THEN
4694 l_hierarchy_type := 'ADMIN_TO_MGR';
4695 ELSIF member_rec.admin_flag = 'Y'
4696 THEN
4697 l_hierarchy_type := 'ADMIN_TO_ADMIN';
4698 ELSE
4699 l_hierarchy_type := 'ADMIN_TO_REP';
4700 END IF;
4701 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
4702 then
4703
4704 open dup_cur2(l_mgr_rec(i).P_role_relate_id ,
4705 member_rec.role_relate_id,
4706 member_rec.group_id);
4707 fetch dup_cur2 INTO dup;
4708 IF(dup_cur2%NOTFOUND)
4709 THEN
4710 --CALL TABLE HANDLER FOR INSERTING IN REP MANAGER
4711 OPEN rep_mgr_seq_cur;
4712 FETCH rep_mgr_seq_cur INTO l_denorm_mgr_id;
4713 CLOSE rep_mgr_seq_cur;
4714
4715 jtf_rs_rep_managers_pkg.insert_row(
4716 X_ROWID => x_row_id,
4717 X_DENORM_MGR_ID => l_denorm_mgr_id,
4718 X_RESOURCE_ID => member_rec.resource_id,
4719 X_PERSON_ID => member_rec.person_id,
4720 X_CATEGORY => member_rec.category,
4721 X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
4722 X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
4723 X_GROUP_ID => member_rec.group_id,
4724 X_REPORTS_TO_FLAG => l_reports_to_flag,
4725 X_HIERARCHY_TYPE => l_hierarchy_type,
4726 X_START_DATE_ACTIVE => l_start_date_active,
4727 X_END_DATE_ACTIVE => l_end_date_active,
4728 X_ATTRIBUTE2 => null,
4729 X_ATTRIBUTE3 => null,
4730 X_ATTRIBUTE4 => null,
4731 X_ATTRIBUTE5 => null,
4732 X_ATTRIBUTE6 => null,
4733 X_ATTRIBUTE7 => null,
4734 X_ATTRIBUTE8 => null,
4735 X_ATTRIBUTE9 => null,
4736 X_ATTRIBUTE10 => null,
4737 X_ATTRIBUTE11 => null,
4738 X_ATTRIBUTE12 => null,
4739 X_ATTRIBUTE13 => null,
4740 X_ATTRIBUTE14 => null,
4741 X_ATTRIBUTE15 => null,
4742 X_ATTRIBUTE_CATEGORY => null,
4743 X_ATTRIBUTE1 => null,
4744 X_CREATION_DATE => l_date,
4745 X_CREATED_BY => l_user_id,
4746 X_LAST_UPDATE_DATE => l_date,
4747 X_LAST_UPDATED_BY => l_user_id,
4748 X_LAST_UPDATE_LOGIN => l_login_id,
4749 X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).p_role_relate_id,
4750 X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
4751
4752 IF fnd_api.to_boolean (p_commit)
4753 THEN
4754 l_count := l_count + 1;
4755 if (l_count > 1000)
4756 then
4757 COMMIT WORK;
4758 l_count := 0;
4759 end if;
4760 END IF;
4761
4762 END IF; --end of dup check
4763 close dup_cur2;
4764 END IF; --END OF ST DATE CHECK
4765 END IF; --ADMIN FLAG END
4766 END LOOP; --end of for loop for all managers and admin of parent table stored in pl/sql table
4767 FETCH member_cur INTO member_rec;
4768 END LOOP; --member cur
4769 CLOSE member_cur;
4770
4771
4772 FETCH child_grp_cur INTO child_grp_rec;
4773 END LOOP; --CHILD GRP CUR
4774 --
4775 IF fnd_api.to_boolean (p_commit)
4776 THEN
4777 COMMIT WORK;
4778 END IF;
4779
4780 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4781
4782 EXCEPTION
4783 WHEN fnd_api.g_exc_unexpected_error
4784 THEN
4785
4786 ROLLBACK TO member_denormalize;
4787 --fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_DENORM_ERR');
4788 --FND_MSG_PUB.add;
4789 --x_return_status := fnd_api.g_ret_sts_unexp_error;
4790 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4791 WHEN fnd_api.g_exc_error
4792 THEN
4793 ROLLBACK TO member_denormalize;
4794 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4795
4796 WHEN OTHERS
4797 THEN
4798 ROLLBACK TO member_denormalize;
4799 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
4800 fnd_message.set_token('P_SQLCODE',SQLCODE);
4801 fnd_message.set_token('P_SQLERRM',SQLERRM);
4802 fnd_message.set_token('P_API_NAME',l_api_name);
4803 FND_MSG_PUB.add;
4804 x_return_status := fnd_api.g_ret_sts_unexp_error;
4805 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4806 END INSERT_GRP_RELATIONS;
4807 END JTF_RS_REP_MGR_DENORM_PVT;