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