[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_DENORM_PVT
Source
1 PACKAGE body JTF_RS_GROUP_DENORM_PVT AS
2 /* $Header: jtfrsvdb.pls 120.1 2005/06/13 21:15:33 baianand ship $ */
3 -- API Name : JTF_RS_GROUP_DENORM_PVT
4 -- Type : Private
5 -- Purpose : Inserts/Update the JTF_RS_GROUP_DENORM_PVT table based on changes in jtf_rs_group_relations
6 -- Modification History
7 -- DATE NAME PURPOSE
8 -- S Choudhury Created
9 -- Notes:
10 --
11 g_pkg_name varchar2(30) := 'JTF_RS_GROUP_DENORM_PVT';
12
13 ------- USED ONLY BY "NO CONNECT BY" SECTION - BEGIN
14 TYPE REL_RECORD_TYPE IS RECORD
15 ( p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
16 p_related_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
17 p_start_date_active DATE,
18 p_end_date_active DATE,
19 level NUMBER);
20
21
22 TYPE rel_table IS TABLE OF REL_RECORD_TYPE INDEX BY BINARY_INTEGER;
23 g_parent_tab rel_table;
24 g_child_tab rel_table;
25
26
27 FUNCTION getDirectParent(p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
28 p_level JTF_RS_GROUPS_DENORM.DENORM_LEVEL%type,
29 p_parent_group_id JTF_RS_GROUPS_DENORM.parent_group_id%type,
30 p_start_date JTF_RS_GROUPS_DENORM.start_date_active%TYPE,
31 p_end_date JTF_RS_GROUPS_DENORM.end_date_active%TYPE) RETURN NUMBER
32 IS
33 CURSOR prnt_cur IS
34 SELECT A.RELATED_GROUP_ID FROM JTF_RS_GRP_RELATIONS A
35 WHERE A.GROUP_ID = P_GROUP_ID
36 AND NVL(A.DELETE_FLAG, 'N') <> 'Y'
37 AND A.START_DATE_ACTIVE <= P_START_DATE
38 AND NVL(P_END_DATE, P_START_DATE) <= NVL(A.END_DATE_ACTIVE,
39 NVL(P_END_DATE, P_START_DATE))
40 ORDER BY A.START_DATE_ACTIVE; -- just in case there are multiple
41 -- records(dirty data).. to have predictable result
42 prnt_rec prnt_cur%rowtype;
43 BEGIN
44 if (p_level < 2) then
45 return p_parent_group_id;
46 end if;
47 open prnt_cur;
48 fetch prnt_cur into prnt_rec;
49 if (prnt_cur%found) then
50 close prnt_cur;
51 return prnt_rec.related_group_id;
52 end if;
53 close prnt_cur;
54 return NULL;
55 EXCEPTION
56 WHEN OTHERS
57 THEN
58 if prnt_cur%isopen then
59 close prnt_cur;
60 end if;
61 raise;
62 END;
63 ------- USED ONLY BY "NO CONNECT BY" SECTION - END
64 ------- FORWARD DECLARATION OF PROCEDURES In "NO CONNECT BY" SECTION - BEGIN
65 /* These are the procedures which are clones of correponding
66 procedures with no "_NO_CON". These procedures have the same
67 processing logic as their respective no "_NO_CON" procedures
68 except that they use POPULATE_PARENT_TABLE and
69 POPULATE_CHILD_TABLE procedures to get same result as connect
70 by loop in the no "_NO_CON" procedures.
71 These procedures were created due to escalations and
72 urgent one off requirement for Bug # 2140655, 2428389 and 2716624,
73 which were due to connect by error, for which there was no plausible
74 solution possible, other than simulating connect by thru PL/SQL.
75 These procedures are called by respective no "_NO_CON" procedures
76 when there is connect by loop exception.
77 Due to the major repeation of processing logic code changes
78 must be repelated in both "_NO_CON" and no "_NO_CON" procedures.
79 Hari, Nimit, Nishant. */
80 PROCEDURE INSERT_GROUPS_NO_CON(
81 P_API_VERSION IN NUMBER,
82 P_INIT_MSG_LIST IN VARCHAR2,
83 P_COMMIT IN VARCHAR2,
84 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
85 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
86 X_MSG_COUNT OUT NOCOPY NUMBER,
87 X_MSG_DATA OUT NOCOPY VARCHAR2 );
88
89 PROCEDURE UPDATE_GROUPS_NO_CON(
90 P_API_VERSION IN NUMBER,
91 P_INIT_MSG_LIST IN VARCHAR2,
92 P_COMMIT IN VARCHAR2,
93 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
94 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
95 X_MSG_COUNT OUT NOCOPY NUMBER,
96 X_MSG_DATA OUT NOCOPY VARCHAR2 );
97
98 PROCEDURE DELETE_GRP_RELATIONS_NO_CON(
99 P_API_VERSION IN NUMBER,
100 P_INIT_MSG_LIST IN VARCHAR2,
101 P_COMMIT IN VARCHAR2,
102 p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
103 p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
104 p_related_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
105 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
106 X_MSG_COUNT OUT NOCOPY NUMBER,
107 X_MSG_DATA OUT NOCOPY VARCHAR2);
108
109 PROCEDURE INSERT_GROUPS_PARENT_NO_CON(
110 P_API_VERSION IN NUMBER,
111 P_INIT_MSG_LIST IN VARCHAR2,
112 P_COMMIT IN VARCHAR2,
113 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
114 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
115 X_MSG_COUNT OUT NOCOPY NUMBER,
116 X_MSG_DATA OUT NOCOPY VARCHAR2 );
117
118 ------- FORWARD DECLARATION OF PROCEDURES In "NO CONNECT BY" SECTION - END
119
120 ------ CONNECT BY PRIOR - SECTION - Starts
121 ------ The original procedures that are using connect by prior
122 ------ These procedures are modified to call their corresponding
123 ------ "_NO_CON" procedures in the next section (NO CONNECT BY - SECTION)
124 ------ in case of connect by loop error/exception.
125
126
127 PROCEDURE CREATE_RES_GROUPS(
128 P_API_VERSION IN NUMBER,
129 P_INIT_MSG_LIST IN VARCHAR2,
130 P_COMMIT IN VARCHAR2,
131 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
132 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
133 X_MSG_COUNT OUT NOCOPY NUMBER,
134 X_MSG_DATA OUT NOCOPY VARCHAR2 )
135 IS
136
137 CURSOR c_dup(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
138 x_parent_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
139 IS
140 SELECT den.group_id
141 FROM jtf_rs_groups_denorm den
142 WHERE den.group_id = x_group_id
143 AND den.parent_group_id = x_parent_group_id;
144
145 /*
146 AND den.start_date_active = l_start_date
147 AND den.end_date_active = l_end_date; */
148
149 CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
150 IS
151 SELECT grp.start_date_active,
152 grp.end_date_active
153 FROM jtf_rs_groups_b grp
154 WHERE group_id = x_group_id;
155
156 --Declare the variables
157 --
158 dup c_dup%ROWTYPE;
159
160 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RES_GROUPS';
161 l_api_version CONSTANT NUMBER :=1.0;
162 l_immediate_parent_flag VARCHAR2(1) := 'N';
163 l_date Date;
164 l_user_id Number;
165 l_login_id Number;
166 l_start_date Date;
167 l_end_date Date;
168
169 l_start_date_1 Date;
170 l_end_date_1 Date;
171 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
172 x_row_id varchar2(24) := null;
173
174 l_actual_parent_id NUMBER := null;
175
176 BEGIN
177
178 --Standard Start of API SAVEPOINT
179 SAVEPOINT group_denormalize;
180
181 x_return_status := fnd_api.g_ret_sts_success;
182
183 --Standard Call to check API compatibility
184 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
185 THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
190 IF FND_API.To_boolean(P_INIT_MSG_LIST)
191 THEN
192 FND_MSG_PUB.Initialize;
193 END IF;
194
195 l_date := sysdate;
196 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
197 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
198
199 -- if no group id is passed in then raise error
200 IF p_group_id IS NULL
201 THEN
202 x_return_status := fnd_api.g_ret_sts_error;
203 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
204 FND_MSG_PUB.add;
205 RAISE fnd_api.g_exc_error;
206 RETURN;
207 END IF;
208
209 l_date := sysdate;
210 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
211 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
212
213
214 --fetch the start date and the end date for the group
215 OPEN c_date(p_group_id);
216 FETCH c_date INTO l_start_date, l_end_date;
217 CLOSE c_date;
218
219
220 -- insert a record for the group id that has been passed
221 OPEN c_dup(p_group_id, p_group_id);
222 FETCH c_dup into dup;
223 IF (c_dup%NOTFOUND)
224 THEN
225
226 --insert the record for the group with itself as the parent group
227
228 SELECT jtf_rs_groups_denorm_s.nextval
229 INTO l_DENORM_GRP_ID
230 FROM dual;
231
232 l_actual_parent_id := getDirectParent(p_group_id,
233 0,
234 p_group_id,
235 trunc(l_start_date),
236 trunc(l_end_date));
237 jtf_rs_groups_denorm_pkg.insert_row(
238 X_ROWID => x_row_id,
239 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
240 X_GROUP_ID => p_group_id,
241 X_PARENT_GROUP_ID => p_group_id,
242 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
243 X_ACTUAL_PARENT_ID => l_actual_parent_id,
244 X_START_DATE_ACTIVE => trunc(l_start_date),
245 X_END_DATE_ACTIVE => trunc(l_end_date),
246 X_ATTRIBUTE2 => null,
247 X_ATTRIBUTE3 => null,
248 X_ATTRIBUTE4 => null,
249 X_ATTRIBUTE5 => null,
250 X_ATTRIBUTE6 => null,
251 X_ATTRIBUTE7 => null,
252 X_ATTRIBUTE8 => null,
253 X_ATTRIBUTE9 => null,
254 X_ATTRIBUTE10 => null,
255 X_ATTRIBUTE11 => null,
256 X_ATTRIBUTE12 => null,
257 X_ATTRIBUTE13 => null,
258 X_ATTRIBUTE14 => null,
259 X_ATTRIBUTE15 => null,
260 X_ATTRIBUTE_CATEGORY => null,
261 X_ATTRIBUTE1 => null,
262 X_CREATION_DATE => l_date,
263 X_CREATED_BY => l_user_id,
264 X_LAST_UPDATE_DATE => l_date,
265 X_LAST_UPDATED_BY => l_user_id,
266 X_LAST_UPDATE_LOGIN => l_login_id,
267 X_DENORM_LEVEL => 0) ;
268
269
270 END IF;
271 CLOSE c_dup;
272
273
274 IF fnd_api.to_boolean (p_commit)
275 THEN
276 COMMIT WORK;
277 END IF;
278
279
280 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
281
282 EXCEPTION
283 WHEN fnd_api.g_exc_unexpected_error
284 THEN
285 ROLLBACK TO group_denormalize;
286 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
287 --FND_MSG_PUB.add;
288 --x_return_status := fnd_api.g_ret_sts_unexp_error;
289 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
290 WHEN fnd_api.g_exc_error
291 THEN
292 ROLLBACK TO group_denormalize;
293 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
294
295 WHEN OTHERS
296 THEN
297 ROLLBACK TO group_denormalize;
298 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
299 fnd_message.set_token('P_SQLCODE',SQLCODE);
300 fnd_message.set_token('P_SQLERRM',SQLERRM);
301 fnd_message.set_token('P_API_NAME',l_api_name);
302 FND_MSG_PUB.add;
303 x_return_status := fnd_api.g_ret_sts_unexp_error;
304 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
305 END CREATE_RES_GROUPS;
306
307
308
309 PROCEDURE UPDATE_RES_GROUPS(
310 P_API_VERSION IN NUMBER,
311 P_INIT_MSG_LIST IN VARCHAR2,
312 P_COMMIT IN VARCHAR2,
313 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
314 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
315 X_MSG_COUNT OUT NOCOPY NUMBER,
316 X_MSG_DATA OUT NOCOPY VARCHAR2 )
317 IS
318 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RES_GROUPS';
319
320 l_api_version CONSTANT NUMBER :=1.0;
321 l_immediate_parent_flag VARCHAR2(1) := 'N';
322 l_date Date;
323 l_user_id Number;
324 l_login_id Number;
325 l_start_date Date;
326 l_end_date Date;
327
328 l_start_date_1 Date;
329 l_end_date_1 Date;
330 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
331 x_row_id varchar2(24) := null;
332 l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success;
333 L_MSG_DATA VARCHAR2(200);
334 L_MSG_COUNT number;
335
336
337 CURSOR denorm_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
338 IS
339 SELECT denorm_grp_id
340 FROM jtf_rs_groups_denorm
341 WHERE group_id = l_group_id
342 AND parent_group_id = l_group_id;
343
344 BEGIN
345
346 --Standard Start of API SAVEPOINT
347 SAVEPOINT group_denormalize;
348
349 x_return_status := fnd_api.g_ret_sts_success;
350
351 --Standard Call to check API compatibility
352 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
353 THEN
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
358 IF FND_API.To_boolean(P_INIT_MSG_LIST)
359 THEN
360 FND_MSG_PUB.Initialize;
361 END IF;
362
363 l_date := sysdate;
364 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
365 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
366
367 -- if no group id is passed in then raise error
368 IF p_group_id IS NULL
369 THEN
370 x_return_status := fnd_api.g_ret_sts_error;
371 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
372 FND_MSG_PUB.add;
373 RAISE fnd_api.g_exc_error;
374 RETURN;
375 END IF;
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
382 -- delete the rescord and create it again in denorm
383 OPEN denorm_cur(p_group_id);
384 FETCH denorm_cur into l_denorm_grp_id;
385
386 IF (denorm_cur%FOUND)
387 THEN
388 jtf_rs_groups_denorm_pkg.delete_row(X_DENORM_GRP_ID => l_DENORM_GRP_ID);
389
390 JTF_RS_GROUP_DENORM_PVT.CREATE_RES_GROUPS(
391 P_API_VERSION => 1.0,
392 P_INIT_MSG_LIST => null,
393 P_COMMIT => null,
394 p_group_id => p_group_id,
395 X_RETURN_STATUS => l_return_status,
396 X_MSG_COUNT => l_msg_count,
397 X_MSG_DATA => l_msg_data);
398
399 END IF;
400 CLOSE denorm_cur;
401
402
403 IF fnd_api.to_boolean (p_commit)
404 THEN
405 COMMIT WORK;
406 END IF;
407
408
409 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
410
411 EXCEPTION
412 WHEN fnd_api.g_exc_unexpected_error
413 THEN
414 ROLLBACK TO group_denormalize;
415 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
416 --FND_MSG_PUB.add;
417 --x_return_status := fnd_api.g_ret_sts_unexp_error;
418 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
419 WHEN fnd_api.g_exc_error
420 THEN
421 ROLLBACK TO group_denormalize;
422 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
423
424 WHEN OTHERS
425 THEN
426 ROLLBACK TO group_denormalize;
427 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
428 fnd_message.set_token('P_SQLCODE',SQLCODE);
429 fnd_message.set_token('P_SQLERRM',SQLERRM);
430 fnd_message.set_token('P_API_NAME',l_api_name);
431 FND_MSG_PUB.add;
432 x_return_status := fnd_api.g_ret_sts_unexp_error;
433 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
434 END UPDATE_RES_GROUPS;
435
436
437
438 --FOR INSERT in grp relate
439
440
441 PROCEDURE INSERT_GROUPS(
442 P_API_VERSION IN NUMBER,
443 P_INIT_MSG_LIST IN VARCHAR2,
444 P_COMMIT IN VARCHAR2,
445 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
446 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
447 X_MSG_COUNT OUT NOCOPY NUMBER,
448 X_MSG_DATA OUT NOCOPY VARCHAR2 )
449 IS
450
451 CURSOR c_parents(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
452 IS
453 SELECT rel.group_id,
454 rel.related_group_id,
455 rel.start_date_active,
456 rel.end_date_active,
457 rel.delete_flag,
458 level
459 FROM jtf_rs_grp_relations rel
460 WHERE relation_type = 'PARENT_GROUP'
461 CONNECT BY rel.group_id = prior rel.related_group_id
462 AND NVL(rel.delete_flag, 'N') <> 'Y'
463 AND rel.related_group_id <> x_group_id
464 START WITH rel.group_id = x_group_id
465 AND NVL(rel.delete_flag, 'N') <> 'Y';
466
467 r_parents c_parents%rowtype;
468
469 CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
470 IS
471 SELECT grp.start_date_active,
472 grp.end_date_active
473 FROM jtf_rs_groups_b grp
474 WHERE group_id = x_group_id;
475
476 CURSOR c_dup(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
477 x_parent_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
478 l_start_date_active date,
479 l_end_date_active date)
480 IS
481 SELECT den.group_id
482 FROM jtf_rs_groups_denorm den
483 WHERE den.group_id = x_group_id
484 AND den.parent_group_id = x_parent_group_id
485 --AND start_date_active = l_start_date_active
486 AND ((l_start_date_active between den.start_date_active and
487 nvl(den.end_date_active,l_start_date_active+1))
488 OR (l_end_date_active between den.start_date_active
489 and nvl(den.end_date_active,l_end_date_active+1))
490 OR ((l_start_date_active <= den.start_date_active)
491 AND (l_end_date_active >= den.end_date_active
492 OR l_end_date_active IS NULL)));
493
494 CURSOR c_child(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
495 l_start_date date,
496 l_end_date date)
497 IS
498 SELECT rel.group_id,
499 rel.related_group_id,
500 rel.start_date_active,
501 rel.end_date_active,
502 level
503 FROM jtf_rs_grp_relations rel
504 WHERE relation_type = 'PARENT_GROUP'
505 CONNECT BY rel.related_group_id = prior rel.group_id
506 AND NVL(rel.delete_flag, 'N') <> 'Y'
507 AND rel.group_id <> x_group_id
508 START WITH rel.related_group_id = x_group_id
509 AND NVL(rel.delete_flag, 'N') <> 'Y';
510 -- AND rel.start_date_active between l_start_date and nvl(l_end_date, rel.start_date_active +1);
511
512
513 r_child c_child%rowtype;
514
515 ---------------------------------------------------------
516 -- This is added on 12/24/2002 to fix connect by loop error for customer
517 -- bug. In case of connect by loop exception, a new procedure will be called
518 -- This way, the existing proccedure is not disturbed. But any code change in
519 -- this procedure will need a modification in new parallel code.
520
521 l_connect_by_loop_error EXCEPTION;--exception to handle connect by loop error
522 PRAGMA EXCEPTION_INIT(l_connect_by_loop_error, -1436 );
523
524 cb_p_api_version number := p_api_version;
525 cb_p_init_msg_list varchar2(10) := P_INIT_MSG_LIST;
526 cb_p_commit varchar2(10) := P_COMMIT;
527 cb_p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_group_id;
528 ---------------------------------------------------------
529
530 TYPE CHILD_TYPE IS RECORD
531 ( p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
532 p_related_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
533 p_start_date_active DATE,
534 p_end_date_active DATE,
535 level NUMBER);
536
537
538 TYPE child_table IS TABLE OF CHILD_type INDEX BY BINARY_INTEGER;
539 l_child_tab child_table;
540
541 i BINARY_INTEGER := 0;
542 j BINARY_INTEGER := 0;
543
544 --Declare the variables
545 --
546 dup c_dup%ROWTYPE;
547 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS';
548 l_api_version CONSTANT NUMBER :=1.0;
549 l_immediate_parent_flag VARCHAR2(1) := 'N';
550 l_date Date;
551 l_user_id Number;
552 l_login_id Number;
553 l_start_date Date;
554 l_end_date Date;
555 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
556 l_msg_count number;
557 l_msg_data varchar2(2000);
558
559 l_start_date_active Date;
560 l_end_date_active Date;
561
562 l_start_date_1 Date;
563 l_end_date_1 Date;
564 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
565 x_row_id varchar2(24) := null;
566
567
568 l_prev_level number := 0;
569 l_prev_par_level number := 0;
570
571 TYPE LEVEL_INFO IS RECORD
572 ( level NUMBER,
573 start_date date,
574 end_date date);
575
576 TYPE level_table IS TABLE OF level_info INDEX BY BINARY_INTEGER;
577
578 level_child_table level_table;
579 level_par_table level_table;
580
581 l_actual_parent_id NUMBER := null;
582
583 procedure populate_table(p_level in number,
584 p_start_date in date,
585 p_end_date in date,
586 l_flag in varchar2)
587 is
588 l BINARY_INTEGER;
589 begin
590 if(l_flag = 'C')
591 THEN
592 l := 0;
593 l := level_child_table.count;
594 l := l + 1;
595 level_child_table(l).level := p_level;
596 level_child_table(l).start_date := p_start_date;
597 level_child_table(l).end_date := p_end_date;
598 ELSE
599
600 l := 0;
601 l := level_par_table.count;
602 l := l + 1;
603 level_par_table(l).level := p_level;
604 level_par_table(l).start_date := p_start_date;
605 level_par_table(l).end_date := p_end_date;
606
607
608 END IF;
609
610 end populate_table;
611
612 procedure delete_table(p_level in number,
613 l_flag in varchar2)
614 is
615 k BINARY_INTEGER;
616 j BINARY_INTEGER;
617
618 begin
619 IF (l_flag = 'C')
620 THEN
621 IF level_child_table.COUNT > 0 THEN
622 k := level_child_table.FIRST;
623 LOOP
624 IF level_child_table(k).level >= p_level THEN
625 j := k;
626 IF k = level_child_table.LAST THEN
627 level_child_table.DELETE(j);
628 EXIT;
629 ELSE
630 k:= level_child_table.NEXT(k);
631 level_child_table.DELETE(j);
632 END IF;
633 ELSE
634 exit when k = level_child_table.LAST;
635 k:= level_child_table.NEXT(k);
636 END IF;
637 END LOOP;
638
639 END IF;
640 ELSE
641 IF level_par_table.COUNT > 0 THEN
642 k := level_par_table.FIRST;
643 LOOP
644 IF level_par_table(k).level >= p_level THEN
645 j := k;
646 IF k = level_par_table.LAST THEN
647 level_par_table.DELETE(j);
648 EXIT;
649 ELSE
650 k:= level_par_table.NEXT(k);
651 level_par_table.DELETE(j);
652 END IF;
653 ELSE
654 exit when k = level_par_table.LAST;
655 k:= level_par_table.NEXT(k);
656 END IF;
657 END LOOP;
658
659 END IF;
660 END IF;
661
662 end delete_table;
663
664 procedure get_table_date(p_level in number,
665 p_start_date out NOCOPY date,
666 p_end_date out NOCOPY date,
667 l_flag in varchar2)
668 is
669
670 k BINARY_INTEGER := 0;
671
672 begin
673 IF(l_flag = 'C')
674 THEN
675 for k in 1..level_child_table.COUNT
676 loop
677 if level_child_table(k).level = p_level
678 then
679 p_start_date := level_child_table(k).start_date;
680 p_end_date := level_child_table(k).end_date;
681 exit;
682 end if;
683 end loop;
684
685 ELSE
686 for k in 1..level_par_table.COUNT
687 loop
688
689 if level_par_table(k).level = p_level
690 then
691 p_start_date := level_par_table(k).start_date;
692 p_end_date := level_par_table(k).end_date;
693 exit;
694 end if;
695 end loop;
696 END IF;
697 end get_table_date;
698
699
700 BEGIN
701
702 --Standard Start of API SAVEPOINT
703 SAVEPOINT group_denormalize;
704
705 x_return_status := fnd_api.g_ret_sts_success;
706
707 --Standard Call to check API compatibility
708 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
709 THEN
710 RAISE FND_API.G_EXC_ERROR;
711 END IF;
712
713 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
714 IF FND_API.To_boolean(P_INIT_MSG_LIST)
715 THEN
716 FND_MSG_PUB.Initialize;
717 END IF;
718
719 l_date := sysdate;
720 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
721 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
722
723 -- if no group id is passed in then raise error
724 IF p_group_id IS NULL
725 THEN
726 x_return_status := fnd_api.g_ret_sts_error;
727 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
728 FND_MSG_PUB.add;
729 RAISE fnd_api.g_exc_error;
730 RETURN;
731 END IF;
732
733 l_date := sysdate;
734 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
735 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
736
737
738 --fetch the start date and the end date for the group
739 OPEN c_date(p_group_id);
740 FETCH c_date INTO l_start_date, l_end_date;
741 CLOSE c_date;
742
743
744
745 --get all the child groups for this group
746 open c_child(p_group_id, l_start_date, l_end_date);
747
748 fetch c_child INTO r_child;
749 WHILE(c_child%found)
750 loop
751 i := i + 1;
752 l_child_tab(i).p_group_id := r_child.group_id;
753 l_child_tab(i).p_related_group_id := r_child.related_group_id;
754 l_child_tab(i).p_start_date_active := r_child.start_date_active;
755 l_child_tab(i).p_end_date_active := r_child.end_date_active;
756 l_child_tab(i).level := r_child.level;
757
758 FETCH c_child INTO r_child;
759 END LOOP; --end of par_mgr_cur
760 CLOSE c_child;
761 IF(l_child_tab.COUNT > 0)
762 THEN
763 --changed l_start_date to l_start_date_active
764 l_start_date_active := l_child_tab(1).p_start_date_active;
765 l_end_date_active := l_child_tab(1).p_end_date_active;
766 END IF;
767 --insert a record with this group for the child group also
768 i := 0;
769
770
771 FOR I IN 1 .. l_child_tab.COUNT
772 LOOP
773 IF(l_child_tab(i).level = 1)
774 THEN
775 l_start_date_active := l_child_tab(i).p_start_date_active;
776 l_end_date_active := l_child_tab(i).p_end_date_active;
777 delete_table(l_child_tab(i).level, 'C');
778 ELSIF(l_prev_level >= l_child_tab(i).level)
779 THEN
780 get_table_date(l_child_tab(i).level - 1, l_start_date_active, l_end_date_active,'C');
781 delete_table(l_child_tab(i).level, 'C');
782 END IF; -- end of level check
783
784
785 --assign start date and end date for which this relation is valid
786
787
788 IF(l_start_date_active < l_child_tab(i).p_start_date_active)
789 THEN
790 l_start_date_active := l_child_tab(i).p_start_date_active;
791 ELSIF(l_start_date_active is null)
792 THEN
793 l_start_date_active := l_child_tab(i).p_start_date_active;
794 ELSE
795 l_start_date_active := l_start_date_active;
796 END IF;
797
798 IF(l_end_date_active > l_child_tab(i).p_end_date_active)
799 THEN
800 l_end_date_active := l_child_tab(i).p_end_date_active;
801 ELSIF(l_child_tab(i).p_end_date_active IS NULL)
802 THEN
803 l_end_date_active := l_end_date_active;
804 ELSIF(l_end_date_active IS NULL)
805 THEN
806 l_end_date_active := l_child_tab(i).p_end_date_active;
807 END IF;
808
809
810 IF (l_child_tab(i).p_related_group_id = P_GROUP_ID)
811 THEN
812 l_immediate_parent_flag := 'Y';
813 ELSE
814 l_immediate_parent_flag := 'N';
815 END IF;
816 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
817 THEN
818 OPEN c_dup(l_child_tab(i).p_group_id, p_group_id, l_start_date_active, l_end_date_active);
819 FETCH c_dup into dup;
820 IF (c_dup%NOTFOUND)
821 THEN
822
823 SELECT jtf_rs_groups_denorm_s.nextval
824 INTO l_denorm_grp_id
825 FROM dual;
826
827
828 l_actual_parent_id := getDirectParent(l_child_tab(i).p_group_id,
829 l_child_tab(i).level,
830 p_group_id,
831 trunc(l_start_date_active),
832 trunc(l_end_date_active));
833
834 jtf_rs_groups_denorm_pkg.insert_row(
835 X_ROWID => x_row_id,
836 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
837 X_GROUP_ID => l_child_tab(i).p_group_id,
838 X_PARENT_GROUP_ID => p_group_id,
839 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
840 X_ACTUAL_PARENT_ID => l_actual_parent_id,
841 X_START_DATE_ACTIVE => trunc(l_start_date_active),
842 X_END_DATE_ACTIVE => trunc(l_end_date_active),
843 X_ATTRIBUTE2 => null,
844 X_ATTRIBUTE3 => null,
845 X_ATTRIBUTE4 => null,
846 X_ATTRIBUTE5 => null,
847 X_ATTRIBUTE6 => null,
848 X_ATTRIBUTE7 => null,
849 X_ATTRIBUTE8 => null,
850 X_ATTRIBUTE9 => null,
851 X_ATTRIBUTE10 => null,
852 X_ATTRIBUTE11 => null,
853 X_ATTRIBUTE12 => null,
854 X_ATTRIBUTE13 => null,
855 X_ATTRIBUTE14 => null,
856 X_ATTRIBUTE15 => null,
857 X_ATTRIBUTE_CATEGORY => null,
858 X_ATTRIBUTE1 => null,
859 X_CREATION_DATE => l_date,
860 X_CREATED_BY => l_user_id,
861 X_LAST_UPDATE_DATE => l_date,
862 X_LAST_UPDATED_BY => l_user_id,
863 X_LAST_UPDATE_LOGIN => l_login_id,
864 X_DENORM_LEVEL => l_child_tab(i).level);
865
866 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
867 P_API_VERSION => 1.0,
868 P_GROUP_DENORM_ID => l_denorm_grp_id,
869 P_GROUP_ID => l_child_tab(i).p_group_id ,
870 P_PARENT_GROUP_ID => p_group_id ,
871 P_START_DATE_ACTIVE => l_start_date_active ,
872 P_END_DATE_ACTIVE => l_end_date_active ,
873 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
874 P_DENORM_LEVEL => l_child_tab(i).level,
875 X_RETURN_STATUS => l_return_status,
876 X_MSG_COUNT => l_msg_count,
877 X_MSG_DATA => l_msg_data ) ;
878
879 IF(l_return_status <> fnd_api.g_ret_sts_success)
880 THEN
881 x_return_status := fnd_api.g_ret_sts_error;
882 RAISE fnd_api.g_exc_error;
883 END IF;
884
885 END IF; -- end of duplicate check
886 CLOSE c_dup;
887 END IF; -- end of start date < end date check
888
889 --populating the plsql table
890 l_prev_level := l_child_tab(i).level;
891 populate_table(l_prev_level, l_start_date_active, l_end_date_active, 'C');
892
893
894 END LOOP;
895
896 -- delete all rows from pl/sql table for level
897 -- delete_table(1, 'C');
898
899
900 OPEN c_parents(p_group_id);
901 FETCH c_parents INTO r_parents;
902
903 l_prev_par_level := 0;
904 --FOR r_parents IN c_parents(p_group_id)
905 WHILE(c_parents%FOUND)
906 LOOP
907 --dbms_output.put_line('444');
908 IF(r_parents.delete_flag <> 'Y')
909 THEN
910 l_start_date := r_parents.start_date_active;
911 l_end_date := r_parents.end_date_active;
912 IF (r_parents.related_group_id IS NOT NULL)
913 THEN
914 IF(l_prev_par_level >= r_parents.level)
915 THEN
916 get_table_date(r_parents.level - 1, l_start_date_1, l_end_date_1, 'P');
917 delete_table(r_parents.level, 'P');
918 END IF; -- end of level check
919
920 --if parent group id is null then this group has no upward hierarchy structure, hence no records
921 --are to be inserted in the denormalized table
922 IF r_parents.GROUP_ID = P_GROUP_ID
923 THEN
924 l_immediate_parent_flag := 'Y';
925 l_start_date_1 := r_parents.start_date_active;
926 l_end_date_1 := r_parents.end_date_active;
927
928 ELSE
929 l_immediate_parent_flag := 'N';
930 if((l_start_date_1 < l_start_date)
931 OR (l_start_date_1 is null))
932 then
933 l_start_date_1 := l_start_date;
934 end if;
935 if(l_end_date < l_end_date_1)
936 then
937 l_end_date_1 := l_end_date;
938 elsif(l_end_date_1 is null)
939 then
940 l_end_date_1 := l_end_date;
941 end if;
942 END IF;
943 IF(l_start_date_1 <= nvl(l_end_date_1, l_start_date_1))
944 THEN
945 OPEN c_dup(p_group_id, r_parents.related_group_id, l_start_date_1, l_end_date_1);
946
947 FETCH c_dup into dup;
948 IF (c_dup%NOTFOUND)
949 THEN
950
951 SELECT jtf_rs_groups_denorm_s.nextval
952 INTO l_denorm_grp_id
953 FROM dual;
954
955 l_actual_parent_id := getDirectParent(p_group_id,
956 r_parents.level,
957 r_parents.related_group_id,
958 trunc(l_start_date_1),
959 trunc(l_end_date_1));
960
961 jtf_rs_groups_denorm_pkg.insert_row(
962 X_ROWID => x_row_id,
963 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
964 X_GROUP_ID => p_group_id,
965 X_PARENT_GROUP_ID => r_parents.related_group_id,
966 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
967 X_ACTUAL_PARENT_ID => l_actual_parent_id,
968 X_START_DATE_ACTIVE => trunc(l_start_date_1),
969 X_END_DATE_ACTIVE => trunc(l_end_date_1),
970 X_ATTRIBUTE2 => null,
971 X_ATTRIBUTE3 => null,
972 X_ATTRIBUTE4 => null,
973 X_ATTRIBUTE5 => null,
974 X_ATTRIBUTE6 => null,
975 X_ATTRIBUTE7 => null,
976 X_ATTRIBUTE8 => null,
977 X_ATTRIBUTE9 => null,
978 X_ATTRIBUTE10 => null,
979 X_ATTRIBUTE11 => null,
980 X_ATTRIBUTE12 => null,
981 X_ATTRIBUTE13 => null,
982 X_ATTRIBUTE14 => null,
983 X_ATTRIBUTE15 => null,
984 X_ATTRIBUTE_CATEGORY => null,
985 X_ATTRIBUTE1 => null,
986 X_CREATION_DATE => l_date,
987 X_CREATED_BY => l_user_id,
988 X_LAST_UPDATE_DATE => l_date,
989 X_LAST_UPDATED_BY => l_user_id,
990 X_LAST_UPDATE_LOGIN => l_login_id,
991 X_DENORM_LEVEL => r_parents.level);
992
993
994
995 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
996 P_API_VERSION => 1.0,
997 P_GROUP_DENORM_ID => l_denorm_grp_id,
998 P_GROUP_ID => p_group_id ,
999 P_PARENT_GROUP_ID => r_parents.related_group_id ,
1000 P_START_DATE_ACTIVE => l_start_date_1 ,
1001 P_END_DATE_ACTIVE => l_end_date_1 ,
1002 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
1003 P_DENORM_LEVEL => r_parents.level,
1004 X_RETURN_STATUS => l_return_status,
1005 X_MSG_COUNT => l_msg_count,
1006 X_MSG_DATA => l_msg_data ) ;
1007
1008 IF(l_return_status <> fnd_api.g_ret_sts_success)
1009 THEN
1010 x_return_status := fnd_api.g_ret_sts_error;
1011 RAISE fnd_api.g_exc_error;
1012 END IF;
1013 END IF;
1014 CLOSE c_dup;
1015
1016
1017 --insert a record with this parent group for the child group also
1018 l_prev_level := 0;
1019 i := 0;
1020 --initialize dates
1021 FOR i IN 1 .. l_child_tab.COUNT
1022 LOOP
1023 IF(l_child_tab(i).level = 1)
1024 THEN
1025 l_start_date_active := l_start_date_1;
1026 l_end_date_active := l_end_date_1;
1027 delete_table(l_child_tab(i).level, 'C');
1028 ELSIF(l_prev_level >= l_child_tab(i).level)
1029 THEN
1030 get_table_date(l_child_tab(i).level - 1, l_start_date_active, l_end_date_active,'C');
1031 delete_table(l_child_tab(i).level, 'C');
1032 END IF; -- end of level check
1033 --dbms_output.put_line('group..'||to_char(l_child_tab(i).p_group_id));
1034 --dbms_output.put_line(to_char(l_start_date_active, 'dd-mon-yyyy')||'..'|| to_char(l_end_date_active, 'dd-mon-yyyy'));
1035 --dbms_output.put_line(to_char(l_child_tab(i).p_start_date_active, 'dd-mon-yyyy') ||'..'||to_char(l_child_tab(i).p_end_date_active, 'dd-mon-yyyy'));
1036
1037 --assign start date and end date for which this relation is valid
1038 IF(l_start_date_active < l_child_tab(i).p_start_date_active)
1039 THEN
1040 l_start_date_active := l_child_tab(i).p_start_date_active;
1041 ELSIF(l_start_date_active is null)
1042 THEN
1043 l_start_date_active := l_child_tab(i).p_start_date_active;
1044 ELSE
1045 l_start_date_active := l_start_date_active;
1046 END IF;
1047
1048 IF(l_end_date_active > l_child_tab(i).p_end_date_active)
1049 THEN
1050 l_end_date_active := l_child_tab(i).p_end_date_active;
1051 ELSIF(l_child_tab(i).p_end_date_active IS NULL)
1052 THEN
1053 l_end_date_active := l_end_date_active;
1054 ELSIF(l_end_date_active IS NULL)
1055 THEN
1056 l_end_date_active := l_child_tab(i).p_end_date_active;
1057 END IF;
1058
1059 l_immediate_parent_flag := 'N';
1060 IF(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
1061 THEN
1062 OPEN c_dup(l_child_tab(i).p_group_id, r_parents.related_group_id, l_start_date_active, l_end_date_active);
1063 FETCH c_dup into dup;
1064 IF (c_dup%NOTFOUND)
1065 THEN
1066
1067 SELECT jtf_rs_groups_denorm_s.nextval
1068 INTO l_denorm_grp_id
1069 FROM dual;
1070
1071 l_actual_parent_id := getDirectParent(l_child_tab(i).p_group_id,
1072 l_child_tab(i).level + r_parents.level,
1073 r_parents.related_group_id,
1074 trunc(l_start_date_active),
1075 trunc(l_end_date_active));
1076 jtf_rs_groups_denorm_pkg.insert_row(
1077 X_ROWID => x_row_id,
1078 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
1079 X_GROUP_ID => l_child_tab(i).p_group_id,
1080 X_PARENT_GROUP_ID => r_parents.related_group_id,
1081 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
1082 X_ACTUAL_PARENT_ID => l_actual_parent_id,
1083 X_START_DATE_ACTIVE => trunc(l_start_date_active),
1084 X_END_DATE_ACTIVE => trunc(l_end_date_active),
1085 X_ATTRIBUTE2 => null,
1086 X_ATTRIBUTE3 => null,
1087 X_ATTRIBUTE4 => null,
1088 X_ATTRIBUTE5 => null,
1089 X_ATTRIBUTE6 => null,
1090 X_ATTRIBUTE7 => null,
1091 X_ATTRIBUTE8 => null,
1092 X_ATTRIBUTE9 => null,
1093 X_ATTRIBUTE10 => null,
1094 X_ATTRIBUTE11 => null,
1095 X_ATTRIBUTE12 => null,
1096 X_ATTRIBUTE13 => null,
1097 X_ATTRIBUTE14 => null,
1098 X_ATTRIBUTE15 => null,
1099 X_ATTRIBUTE_CATEGORY => null,
1100 X_ATTRIBUTE1 => null,
1101 X_CREATION_DATE => l_date,
1102 X_CREATED_BY => l_user_id,
1103 X_LAST_UPDATE_DATE => l_date,
1104 X_LAST_UPDATED_BY => l_user_id,
1105 X_LAST_UPDATE_LOGIN => l_login_id,
1106 X_DENORM_LEVEL => l_child_tab(i).level + r_parents.level);
1107
1108 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
1109 P_API_VERSION => 1.0,
1110 P_GROUP_DENORM_ID => l_denorm_grp_id,
1111 P_GROUP_ID => l_child_tab(i).p_group_id ,
1112 P_PARENT_GROUP_ID => r_parents.related_group_id ,
1113 P_START_DATE_ACTIVE => l_start_date_active ,
1114 P_END_DATE_ACTIVE => l_end_date_active ,
1115 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
1116 P_DENORM_LEVEL => l_child_tab(i).level + r_parents.level,
1117 X_RETURN_STATUS => l_return_status,
1118 X_MSG_COUNT => l_msg_count,
1119 X_MSG_DATA => l_msg_data ) ;
1120
1121 IF(l_return_status <> fnd_api.g_ret_sts_success)
1122 THEN
1123 x_return_status := fnd_api.g_ret_sts_error;
1124 RAISE fnd_api.g_exc_error;
1125 END IF;
1126
1127 END IF; -- end of duplicate check
1128 CLOSE c_dup;
1129
1130 END IF; -- end of start_date_active check
1131
1132 --populating the plsql table
1133 l_prev_level := l_child_tab(i).level;
1134 populate_table(l_prev_level, l_start_date_active, l_end_date_active, 'C');
1135
1136 END LOOP; -- end of child tab insert
1137 -- delete all rows from pl/sql table for level
1138 delete_table(1, 'C');
1139
1140 END IF; -- end of parent start date check
1141 --populating the plsql table
1142 l_prev_par_level := r_parents.level;
1143 populate_table(l_prev_par_level, l_start_date_1, l_end_date_1, 'P');
1144 END IF; --end of group id check
1145
1146 END IF; -- end of delete flag check
1147
1148
1149
1150 FETCH c_parents INTO r_parents;
1151 END LOOP;
1152 CLOSE c_parents;
1153
1154
1155 IF fnd_api.to_boolean (p_commit)
1156 THEN
1157 COMMIT WORK;
1158 END IF;
1159
1160
1161 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1162
1163 EXCEPTION
1164 WHEN l_connect_by_loop_error
1165 THEN
1166 ROLLBACK TO group_denormalize;
1167 BEGIN
1168 INSERT_GROUPS_NO_CON(
1169 P_API_VERSION => cb_p_api_version,
1170 P_INIT_MSG_LIST => cb_p_init_msg_list,
1171 P_COMMIT => cb_p_commit,
1172 p_group_id => cb_p_group_id,
1173 X_RETURN_STATUS => x_return_status,
1174 X_MSG_COUNT => x_msg_count,
1175 X_MSG_DATA => x_msg_data );
1176 EXCEPTION
1177 WHEN OTHERS
1178 THEN
1179 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1180 fnd_message.set_token('P_SQLCODE',SQLCODE);
1181 fnd_message.set_token('P_SQLERRM',SQLERRM);
1182 fnd_message.set_token('P_API_NAME',l_api_name);
1183 FND_MSG_PUB.add;
1184 x_return_status := fnd_api.g_ret_sts_unexp_error;
1185 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1186 END;
1187 WHEN fnd_api.g_exc_unexpected_error
1188 THEN
1189 ROLLBACK TO group_denormalize;
1190
1191 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
1192 --ND_MSG_PUB.add;
1193 --x_return_status := fnd_api.g_ret_sts_unexp_error;
1194 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1195 WHEN fnd_api.g_exc_error
1196 THEN
1197 ROLLBACK TO group_denormalize;
1198 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1199
1200 WHEN OTHERS
1201 THEN
1202 ROLLBACK TO group_denormalize;
1203 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1204 fnd_message.set_token('P_SQLCODE',SQLCODE);
1205 fnd_message.set_token('P_SQLERRM',SQLERRM);
1206 fnd_message.set_token('P_API_NAME',l_api_name);
1207 FND_MSG_PUB.add;
1208 x_return_status := fnd_api.g_ret_sts_unexp_error;
1209 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1210 END INSERT_GROUPS;
1211
1212
1213 --Start of procedure Body
1214 --FOR UPDATE
1215
1216 PROCEDURE UPDATE_GROUPS(
1217 P_API_VERSION IN NUMBER,
1218 P_INIT_MSG_LIST IN VARCHAR2,
1219 P_COMMIT IN VARCHAR2,
1220 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1221 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1222 X_MSG_COUNT OUT NOCOPY NUMBER,
1223 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1224 IS
1225
1226 CURSOR c_child(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
1227 IS
1228 SELECT rel.group_id,
1229 rel.related_group_id,
1230 rel.start_date_active,
1231 rel.end_date_active
1232 FROM jtf_rs_grp_relations rel
1233 WHERE relation_type = 'PARENT_GROUP'
1234 CONNECT BY rel.related_group_id = prior rel.group_id
1235 AND NVL(rel.delete_flag, 'N') <> 'Y'
1236 AND rel.group_id <> x_group_id
1237 START WITH rel.group_id = x_group_id
1238 AND NVL(rel.delete_flag, 'N') <> 'Y';
1239
1240
1241
1242 CURSOR c_group_denorm(l_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
1243 IS
1244 SELECT denorm_grp_id,
1245 group_id,
1246 parent_group_id
1247 FROM JTF_RS_GROUPS_DENORM
1248 WHERE group_id = l_group_id
1249 AND PARENT_GROUP_ID <> L_GROUP_ID;
1250
1251
1252 ---------------------------------------------------------
1253 -- This is added on 12/24/2002 to fix connect by loop error for customer
1254 -- bug. In case of connect by loop exception, a new procedure will be called
1255 -- This way, the existing proccedure is not disturbed. But any code change in
1256 -- this procedure will need a modification in new parallel code.
1257 l_connect_by_loop_error EXCEPTION;--exception to handle connect by loop error
1258 PRAGMA EXCEPTION_INIT(l_connect_by_loop_error, -1436 );
1259
1260 cb_p_api_version number := p_api_version;
1261 cb_p_init_msg_list varchar2(10) := P_INIT_MSG_LIST;
1262 cb_p_commit varchar2(10) := P_COMMIT;
1263 cb_p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_group_id;
1264 ---------------------------------------------------------
1265
1266 --Declare the variables
1267 --
1268
1269 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUPS';
1270 l_api_version CONSTANT NUMBER :=1.0;
1271
1272 l_date DATE;
1273 l_user_id NUMBER := 1;
1274 l_login_id NUMBER := 1;
1275 l_return_status VARCHAR2(200) := fnd_api.g_ret_sts_success;
1276 l_msg_count NUMBER;
1277 l_msg_data VARCHAR2(200);
1278 BEGIN
1279
1280 --Standard Start of API SAVEPOINT
1281 SAVEPOINT group_denormalize;
1282
1283 x_return_status := fnd_api.g_ret_sts_success;
1284
1285 --Standard Call to check API compatibility
1286 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1287 THEN
1288 RAISE FND_API.G_EXC_ERROR;
1289 END IF;
1290
1291 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1292 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1293 THEN
1294 FND_MSG_PUB.Initialize;
1295 END IF;
1296
1297
1298 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1299 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1300 THEN
1301 FND_MSG_PUB.Initialize;
1302 END IF;
1303
1304 l_date := sysdate;
1305 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1306 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1307 --delete the previous hierarchy for the group
1308 for r_group_denorm IN c_group_denorm(p_group_id)
1309 loop
1310 JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
1311 P_API_VERSION => 1.0,
1312 P_GROUP_ID => r_group_denorm.group_id,
1313 P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
1314 X_RETURN_STATUS => l_return_status,
1315 X_MSG_COUNT => l_msg_count,
1316 X_MSG_DATA => l_msg_data);
1317
1318
1319
1320 IF(l_return_status <> fnd_api.g_ret_sts_success)
1321 THEN
1322 x_return_status := fnd_api.g_ret_sts_error;
1323 RAISE fnd_api.g_exc_error;
1324 END IF;
1325 jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
1326 end loop;
1327
1328
1329
1330 --delete the hiearchy of all the child records of the group
1331 FOR r_child IN c_child(p_group_id)
1332 LOOP
1333
1334 for r_group_denorm IN c_group_denorm(r_child.group_id)
1335 loop
1336 JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
1337 P_API_VERSION => 1.0,
1338 P_GROUP_ID => r_group_denorm.group_id,
1339 P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
1340 X_RETURN_STATUS => l_return_status,
1341 X_MSG_COUNT => l_msg_count,
1342 X_MSG_DATA => l_msg_data);
1343
1344 IF(l_return_status <> fnd_api.g_ret_sts_success)
1345 THEN
1346 x_return_status := fnd_api.g_ret_sts_error;
1347 RAISE fnd_api.g_exc_error;
1348 END IF;
1349
1350 jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
1351 end loop;
1352 END LOOP;
1353
1354
1355 --rebuild the hiearchy of all the child records of the group
1356 FOR r_child IN c_child(p_group_id)
1357 LOOP
1358 JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL,r_child.group_id, x_return_status, x_msg_count, x_msg_data);
1359 END LOOP;
1360
1361 --rebuild the group hiearchy again
1362 JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL,p_group_id, x_return_status, x_msg_count, x_msg_data);
1363
1364 IF fnd_api.to_boolean (p_commit)
1365 THEN
1366 COMMIT WORK;
1367 END IF;
1368
1369 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1370
1371 EXCEPTION
1372 WHEN l_connect_by_loop_error
1373 THEN
1374 ROLLBACK TO group_denormalize;
1375 BEGIN
1376 UPDATE_GROUPS_NO_CON(
1377 P_API_VERSION => cb_p_api_version,
1378 P_INIT_MSG_LIST => cb_p_init_msg_list,
1379 P_COMMIT => cb_p_commit,
1380 p_group_id => cb_p_group_id,
1381 X_RETURN_STATUS => x_return_status,
1382 X_MSG_COUNT => x_msg_count,
1383 X_MSG_DATA => x_msg_data );
1384 EXCEPTION
1385 WHEN OTHERS
1386 THEN
1387 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1388 fnd_message.set_token('P_SQLCODE',SQLCODE);
1389 fnd_message.set_token('P_SQLERRM',SQLERRM);
1390 fnd_message.set_token('P_API_NAME',l_api_name);
1391 FND_MSG_PUB.add;
1392 x_return_status := fnd_api.g_ret_sts_unexp_error;
1393 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1394 END;
1395 WHEN fnd_api.g_exc_unexpected_error
1396 THEN
1397 ROLLBACK TO group_denormalize;
1398 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
1399 --FND_MSG_PUB.add;
1400 --x_return_status := fnd_api.g_ret_sts_unexp_error;
1401 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1402 WHEN fnd_api.g_exc_error
1403 THEN
1404 ROLLBACK TO group_denormalize;
1405 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1406
1407 WHEN OTHERS
1408 THEN
1409 ROLLBACK TO group_denormalize;
1410 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1411 fnd_message.set_token('P_SQLCODE',SQLCODE);
1412 fnd_message.set_token('P_SQLERRM',SQLERRM);
1413 fnd_message.set_token('P_API_NAME',l_api_name);
1414 FND_MSG_PUB.add;
1415 x_return_status := fnd_api.g_ret_sts_unexp_error;
1416 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1417
1418 END UPDATE_GROUPS;
1419
1420
1421
1422 PROCEDURE DELETE_GRP_RELATIONS(
1423 P_API_VERSION IN NUMBER,
1424 P_INIT_MSG_LIST IN VARCHAR2,
1425 P_COMMIT IN VARCHAR2,
1426 p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1427 p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1428 p_related_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1429 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1430 X_MSG_COUNT OUT NOCOPY NUMBER,
1431 X_MSG_DATA OUT NOCOPY VARCHAR2)
1432 is
1433
1434 cursor c_child(l_group_id number)
1435 is
1436 select group_id,
1437 related_group_id,
1438 start_date_active,
1439 end_date_active
1440 from jtf_rs_grp_relations
1441 where relation_type = 'PARENT_GROUP'
1442 connect by related_group_id = prior group_id
1443 and nvl(delete_flag, 'N') <> 'Y'
1444 -- and group_id <> l_group_id
1445 start with related_group_id = l_group_id
1446 and nvl(delete_flag, 'N') <> 'Y';
1447
1448 r_child c_child%rowtype;
1449
1450 TYPE CHILD_TYPE IS RECORD
1451 ( p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1452 p_related_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1453 p_start_date_active DATE,
1454 p_end_date_active DATE);
1455
1456
1457 TYPE child_table IS TABLE OF CHILD_type INDEX BY BINARY_INTEGER;
1458 l_child_tab child_table;
1459
1460 i BINARY_INTEGER := 0;
1461
1462 CURSOR check_parent_cur(l_group_id number,
1463 l_related_group_id number)
1464 IS
1465 SELECT rel.group_id,
1466 rel.related_group_id,
1467 rel.start_date_active,
1468 rel.end_date_active
1469 FROM jtf_rs_grp_relations rel
1470 WHERE relation_type = 'PARENT_GROUP'
1471 AND related_group_id = l_related_group_id
1472 CONNECT BY rel.group_id = prior rel.related_group_id
1473 AND NVL(rel.delete_flag, 'N') <> 'Y'
1474 --AND rel.related_group_id <> p_related_group_id
1475 START WITH rel.group_id = l_group_id
1476 AND NVL(rel.delete_flag, 'N') <> 'Y';
1477
1478 check_parent_rec check_parent_cur%rowtype;
1479
1480 CURSOR c_parent(l_group_id number)
1481 IS
1482 SELECT rel.group_id,
1483 rel.related_group_id,
1484 rel.start_date_active,
1485 rel.end_date_active
1486 FROM jtf_rs_grp_relations rel
1487 WHERE relation_type = 'PARENT_GROUP'
1488 CONNECT BY rel.group_id = prior rel.related_group_id
1489 AND NVL(rel.delete_flag, 'N') <> 'Y'
1490 --AND rel.related_group_id <> p_related_group_id
1491 START WITH rel.group_id = p_related_group_id
1492 AND NVL(rel.delete_flag, 'N') <> 'Y';
1493
1494
1495 r_parent c_parent%rowtype;
1496
1497 TYPE parent_TYPE IS RECORD
1498 ( p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1499 p_related_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1500 p_start_date_active DATE,
1501 p_end_date_active DATE);
1502
1503
1504 TYPE parent_table IS TABLE OF parent_type INDEX BY BINARY_INTEGER;
1505 l_parent_tab parent_table;
1506
1507 j BINARY_INTEGER := 0;
1508
1509 TYPE role_relate_TYPE IS RECORD
1510 ( role_relate_id NUMBER,
1511 group_id NUMBER);
1512
1513 TYPE child_rol_rel_table IS TABLE OF role_relate_TYPE INDEX BY BINARY_INTEGER;
1514 l_child_rol_rel_tab child_rol_rel_table;
1515
1516 k BINARY_INTEGER := 0;
1517
1518 TYPE par_rol_rel_table IS TABLE OF role_relate_TYPE INDEX BY BINARY_INTEGER;
1519 l_par_rol_rel_tab par_rol_rel_table;
1520
1521 l BINARY_INTEGER := 0;
1522
1523
1524 cursor rr_cur(l_no number)
1525 is
1526 select rel.role_relate_id,
1527 mem.group_id
1528 from jtf_rs_group_members mem,
1529 jtf_rs_role_relations rel
1530 where mem.group_id = l_no
1531 and nvl(mem.delete_flag , 'N') <> 'Y'
1532 and mem.group_member_id = rel.role_resource_id
1533 and rel.role_resource_type = 'RS_GROUP_MEMBER'
1534 and nvl(rel.delete_flag, 'N') <> 'Y';
1535
1536 role_rel_rec rr_cur%rowtype;
1537
1538 cursor rr_mgr_cur(l_group_id number)
1539 is
1540 select rel.role_relate_id,
1541 mem.group_id
1542 from jtf_rs_group_members mem,
1543 jtf_rs_role_relations rel,
1544 jtf_rs_roles_b rol
1545 where mem.group_id = l_group_id
1546 and nvl(mem.delete_flag , 'N') <> 'Y'
1547 and mem.group_member_id = rel.role_resource_id
1548 and rel.role_resource_type = 'RS_GROUP_MEMBER'
1549 and nvl(rel.delete_flag, 'N') <> 'Y'
1550 and rel.role_id = rol.role_id
1551 and (
1552 nvl(rol.manager_flag, 'N') = 'Y'
1553 or
1554 nvl(rol.admin_flag, 'N') = 'Y'
1555 );
1556
1557 role_rel_mgr_rec rr_mgr_cur%rowtype;
1558
1559 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GRP_RELATIONS';
1560 l_api_version CONSTANT NUMBER :=1.0;
1561 l_date Date;
1562 l_user_id Number;
1563 l_login_id Number;
1564
1565 l_return_status VARCHAR2(200) := fnd_api.g_ret_sts_success;
1566 l_msg_count NUMBER;
1567 l_found BOOLEAN := FALSE;
1568
1569 ---------------------------------------------------------
1570 -- This is added on 12/24/2002 to fix connect by loop error for customer
1571 -- bug. In case of connect by loop exception, a new procedure will be called
1572 -- This way, the existing proccedure is not disturbed. But any code change in
1573 -- this procedure will need a modification in new parallel code.
1574 l_connect_by_loop_error EXCEPTION;--exception to handle connect by loop error
1575 PRAGMA EXCEPTION_INIT(l_connect_by_loop_error, -1436 );
1576
1577 cb_p_api_version number := p_api_version;
1578 cb_p_init_msg_list varchar2(10) := P_INIT_MSG_LIST;
1579 cb_p_commit varchar2(10) := P_COMMIT;
1580 cb_p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_group_id;
1581 cb_p_group_relate_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_group_relate_id;
1582 cb_p_related_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_related_group_id;
1583 ---------------------------------------------------------
1584
1585 begin
1586
1587 --Standard Start of API SAVEPOINT
1588 SAVEPOINT group_denormalize;
1589
1590 x_return_status := fnd_api.g_ret_sts_success;
1591
1592 --Standard Call to check API compatibility
1593 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1594 THEN
1595 RAISE FND_API.G_EXC_ERROR;
1596 END IF;
1597
1598 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1599 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1600 THEN
1601 FND_MSG_PUB.Initialize;
1602 END IF;
1603
1604
1605 l_date := sysdate;
1606 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1607 --to add the grooup_id as child as this will not be included in cursor
1608 k := 0;
1609 i := i + 1;
1610 l_child_tab(i).p_group_id := p_group_id;
1611 l_child_tab(i).p_related_group_id := p_related_group_id;
1612
1613 -- get the role relate ids for this group
1614 open rr_cur(p_group_id);
1615 fetch rr_cur into role_rel_rec;
1616 while (rr_cur%found)
1617 loop
1618 k := K + 1;
1619 l_child_rol_rel_tab(k).role_relate_id := role_rel_rec.role_relate_id;
1620 l_child_rol_rel_tab(k).group_id := role_rel_rec.group_id;
1621
1622 fetch rr_cur into role_rel_rec;
1623 end loop; -- end of role relate cur
1624 close rr_cur;
1625 --get all the child groups for this group
1626 open c_child(p_group_id);
1627 fetch c_child INTO r_child;
1628
1629 while(c_child%found)
1630 loop
1631
1632 i := i + 1;
1633 l_child_tab(i).p_group_id := r_child.group_id;
1634 l_child_tab(i).p_related_group_id := r_child.related_group_id;
1635 l_child_tab(i).p_start_date_active := r_child.start_date_active;
1636 l_child_tab(i).p_end_date_active := r_child.end_date_active;
1637
1638 -- get the role relate ids for this group
1639 open rr_cur(r_child.group_id);
1640 fetch rr_cur into role_rel_rec;
1641 while (rr_cur%found)
1642 loop
1643 k := K + 1;
1644 l_child_rol_rel_tab(k).role_relate_id := role_rel_rec.role_relate_id;
1645 l_child_rol_rel_tab(k).group_id := role_rel_rec.group_id;
1646
1647 fetch rr_cur into role_rel_rec;
1648 end loop; -- end of role relate cur
1649 close rr_cur;
1650
1651 FETCH c_child INTO r_child;
1652 END LOOP; --end of child_grp_cur
1653 CLOSE c_child;
1654
1655 -- insert the parent group in the table as the parent cursor does not fetch this record
1656 l := 0;
1657 j := j + 1;
1658
1659 l_parent_tab(j).p_group_id := p_group_id;
1660 l_parent_tab(j).p_related_group_id := p_related_group_id;
1661
1662 -- get the role relate ids for this group
1663 open rr_mgr_cur(p_related_group_id);
1664 fetch rr_mgr_cur into role_rel_mgr_rec;
1665 while (rr_mgr_cur%found)
1666 loop
1667 l :=l + 1;
1668 l_par_rol_rel_tab(l).role_relate_id := role_rel_mgr_rec.role_relate_id;
1669 l_par_rol_rel_tab(l).group_id := role_rel_mgr_rec.group_id;
1670
1671 fetch rr_mgr_cur into role_rel_mgr_rec;
1672 end loop;
1673 close rr_mgr_cur;
1674
1675 open c_parent(p_group_id);
1676 fetch c_parent INTO r_parent;
1677 while(c_parent%found)
1678 loop
1679 j := j + 1;
1680 l_parent_tab(j).p_group_id := r_parent.group_id;
1681 l_parent_tab(j).p_related_group_id := r_parent.related_group_id;
1682 l_parent_tab(j).p_start_date_active := r_parent.start_date_active;
1683 l_parent_tab(j).p_end_date_active := r_parent.end_date_active;
1684
1685 -- get the role relate ids for this group
1686 open rr_mgr_cur(r_parent.related_group_id);
1687 fetch rr_mgr_cur into role_rel_mgr_rec;
1688 while (rr_mgr_cur%found)
1689 loop
1690 l :=l + 1;
1691 l_par_rol_rel_tab(l).role_relate_id := role_rel_mgr_rec.role_relate_id;
1692 l_par_rol_rel_tab(l).group_id := role_rel_mgr_rec.group_id;
1693
1694 fetch rr_mgr_cur into role_rel_mgr_rec;
1695 end loop; -- end of role relate cur
1696 close rr_mgr_cur;
1697
1698 FETCH c_parent INTO r_parent;
1699
1700
1701 END LOOP; --end of par_grp_cur
1702 CLOSE c_parent;
1703
1704 --DELETE GROUP DENORM
1705 FOR j IN 1 .. l_parent_tab.COUNT
1706 LOOP
1707 FOR i IN 1 .. l_child_tab.COUNT
1708 LOOP
1709 --delete group denorm
1710 begin
1711 delete jtf_rs_groups_denorm
1712 where group_id = l_child_tab(i).p_group_id
1713 and parent_group_id = l_parent_tab(j).p_related_group_id;
1714 exception
1715 when others then
1716 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1717 fnd_message.set_token('P_SQLCODE',SQLCODE);
1718 fnd_message.set_token('P_SQLERRM',SQLERRM);
1719 fnd_message.set_token('P_API_NAME', l_api_name);
1720 FND_MSG_PUB.add;
1721 x_return_status := fnd_api.g_ret_sts_unexp_error;
1722 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1723 RAISE fnd_api.g_exc_unexpected_error;
1724
1725 end;
1726
1727 end loop; -- end of child
1728 end loop; -- end of parent
1729
1730
1731 --DELETE REP MANAGER
1732 FOR l IN 1 .. l_par_rol_rel_tab.COUNT
1733 LOOP
1734 FOR k IN 1 .. l_child_rol_rel_tab.COUNT
1735 LOOP
1736 --delete rep mgr
1737 begin
1738 delete jtf_rs_rep_managers
1739 where par_role_relate_id = l_par_rol_rel_tab(l).role_relate_id
1740 and child_role_relate_id = l_child_rol_rel_tab(k).role_relate_id;
1741
1742 exception
1743 when others then
1744 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1745 fnd_message.set_token('P_SQLCODE',SQLCODE);
1746 fnd_message.set_token('P_SQLERRM',SQLERRM);
1747 fnd_message.set_token('P_API_NAME', l_api_name);
1748 FND_MSG_PUB.add;
1749 x_return_status := fnd_api.g_ret_sts_unexp_error;
1750 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1751 RAISE fnd_api.g_exc_unexpected_error;
1752 end;
1753
1754 end loop; -- end of child
1755 end loop; -- end of parent
1756
1757
1758
1759 --now recreate hierarchy in case same parent existed for child through some diff branch
1760
1761 FOR i IN 1 .. l_child_tab.COUNT
1762 LOOP
1763 l_found := FALSE;
1764
1765 FOR j IN 1 .. l_parent_tab.COUNT
1766 LOOP
1767 open check_parent_cur(l_child_tab(i).p_group_id,
1768 l_parent_tab(j).p_related_group_id);
1769 fetch check_parent_cur into check_parent_rec;
1770 if (check_parent_cur%found)
1771 then
1772 l_found := TRUE;
1773 jtf_rs_group_denorm_pvt.insert_groups_parent(
1774 p_api_version => 1.0,
1775 p_commit => 'T',
1776 p_group_id => l_child_tab(i).p_group_id,
1777 x_return_status => x_return_status,
1778 x_msg_count => x_msg_count,
1779 x_msg_data => x_msg_data);
1780
1781 IF(x_return_status <> fnd_api.g_ret_sts_success)
1782 THEN
1783 x_return_status := fnd_api.g_ret_sts_error;
1784 RAISE fnd_api.g_exc_error;
1785 END IF;
1786
1787 else
1788 close check_parent_cur;
1789 end if;
1790 if l_found
1791 then
1792 --since the entire parent hierarchy for the group has been built no point checking for further parents
1793 exit;
1794 end if;
1795 END LOOP; -- end of parent tab loop
1796 if(check_parent_cur%isopen)
1797 then
1798 close check_parent_cur;
1799 end if;
1800
1801 /* this has been moved to jtf_rs_groups_denorm.insert_groups_parent
1802 if(l_found)
1803 then
1804 --rebuild the parent rep managers for the parent role relate ids only
1805 FOR k IN 1 .. l_child_rol_rel_tab.COUNT
1806 LOOP
1807 if(l_child_rol_rel_tab(k).group_id = l_child_tab(i).p_group_id)
1808 then
1809 jtf_rs_rep_mgr_denorm_pvt.insert_rep_mgr_parent(
1810 p_api_version => 1.0,
1811 p_commit => 'T',
1812 p_role_relate_id => l_child_rol_rel_tab(k).role_relate_id,
1813 x_return_status => x_return_status,
1814 x_msg_count => x_msg_count,
1815 x_msg_data => x_msg_data);
1816
1817 end if; -- end of if group id same check
1818 END LOOP; -- end of loop for child role relate tab
1819 end if;-- end if l_found true check
1820 */
1821 END LOOP; -- end of child tab loop
1822 EXCEPTION
1823 WHEN l_connect_by_loop_error
1824 THEN
1825 ROLLBACK TO group_denormalize;
1826 BEGIN
1827 DELETE_GRP_RELATIONS_NO_CON(
1828 P_API_VERSION => cb_p_api_version,
1829 P_INIT_MSG_LIST => cb_p_init_msg_list,
1830 P_COMMIT => cb_p_commit,
1831 p_group_id => cb_p_group_id,
1832 p_group_relate_id => cb_p_group_relate_id,
1833 p_related_group_id => cb_p_related_group_id,
1834 X_RETURN_STATUS => x_return_status,
1835 X_MSG_COUNT => x_msg_count,
1836 X_MSG_DATA => x_msg_data );
1837 EXCEPTION
1838 WHEN OTHERS
1839 THEN
1840 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1841 fnd_message.set_token('P_SQLCODE',SQLCODE);
1842 fnd_message.set_token('P_SQLERRM',SQLERRM);
1843 fnd_message.set_token('P_API_NAME',l_api_name);
1844 FND_MSG_PUB.add;
1845 x_return_status := fnd_api.g_ret_sts_unexp_error;
1846 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1847 END;
1848 WHEN fnd_api.g_exc_unexpected_error
1849 THEN
1850
1851 ROLLBACK TO group_denormalize;
1852 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1853 WHEN fnd_api.g_exc_error
1854 THEN
1855 ROLLBACK TO group_denormalize;
1856 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1857
1858 WHEN OTHERS
1859 THEN
1860 ROLLBACK TO group_denormalize;
1861 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1862 fnd_message.set_token('P_SQLCODE',SQLCODE);
1863 fnd_message.set_token('P_SQLERRM',SQLERRM);
1864 fnd_message.set_token('P_API_NAME',l_api_name);
1865 FND_MSG_PUB.add;
1866 x_return_status := fnd_api.g_ret_sts_unexp_error;
1867 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1868
1869 end delete_grp_relations;
1870
1871 PROCEDURE INSERT_GROUPS_PARENT(
1872 P_API_VERSION IN NUMBER,
1873 P_INIT_MSG_LIST IN VARCHAR2,
1874 P_COMMIT IN VARCHAR2,
1875 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
1876 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1877 X_MSG_COUNT OUT NOCOPY NUMBER,
1878 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1879 IS
1880 CURSOR c_parents(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
1881 IS
1882 SELECT rel.group_id,
1883 rel.related_group_id,
1884 rel.start_date_active,
1885 rel.end_date_active,
1886 rel.delete_flag,
1887 level
1888 FROM jtf_rs_grp_relations rel
1889 WHERE relation_type = 'PARENT_GROUP'
1890 CONNECT BY rel.group_id = prior rel.related_group_id
1891 AND NVL(rel.delete_flag, 'N') <> 'Y'
1892 AND rel.related_group_id <> x_group_id
1893 START WITH rel.group_id = x_group_id
1894 AND NVL(rel.delete_flag, 'N') <> 'Y';
1895
1896 r_parents c_parents%rowtype;
1897
1898 CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
1899 IS
1900 SELECT grp.start_date_active,
1901 grp.end_date_active
1902 FROM jtf_rs_groups_b grp
1903 WHERE group_id = x_group_id;
1904
1905 CURSOR c_dup(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1906 x_parent_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
1907 l_start_date_active date,
1908 l_end_date_active date)
1909 IS
1910 SELECT den.group_id
1911 FROM jtf_rs_groups_denorm den
1912 WHERE den.group_id = x_group_id
1913 AND den.parent_group_id = x_parent_group_id
1914 --AND start_date_active = l_start_date_active
1915 AND ((l_start_date_active between den.start_date_active and
1916 nvl(den.end_date_active,l_start_date_active+1))
1917 OR (l_end_date_active between den.start_date_active
1918 and nvl(den.end_date_active,l_end_date_active+1))
1919 OR ((l_start_date_active <= den.start_date_active)
1920 AND (l_end_date_active >= den.end_date_active
1921 OR l_end_date_active IS NULL)));
1922
1923 ---------------------------------------------------------
1924 -- This is added on 12/24/2002 to fix connect by loop error for customer
1925 -- bug. In case of connect by loop exception, a new procedure will be called
1926 -- This way, the existing proccedure is not disturbed. But any code change in
1927 -- this procedure will need a modification in new parallel code.
1928 l_connect_by_loop_error EXCEPTION;--exception to handle connect by loop error
1929 PRAGMA EXCEPTION_INIT(l_connect_by_loop_error, -1436 );
1930
1931 cb_p_api_version number := p_api_version;
1932 cb_p_init_msg_list varchar2(10) := P_INIT_MSG_LIST;
1933 cb_p_commit varchar2(10) := P_COMMIT;
1934 cb_p_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE := p_group_id;
1935 ---------------------------------------------------------
1936
1937
1938 --Declare the variables
1939 --
1940 dup c_dup%ROWTYPE;
1941 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_PARENT';
1942 l_api_version CONSTANT NUMBER :=1.0;
1943 l_immediate_parent_flag VARCHAR2(1) := 'N';
1944 l_date Date;
1945 l_user_id Number;
1946 l_login_id Number;
1947 l_start_date Date;
1948 l_end_date Date;
1949 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1950 l_msg_count number;
1951 l_msg_data varchar2(2000);
1952
1953 l_start_date_active Date;
1954 l_end_date_active Date;
1955
1956 l_start_date_1 Date;
1957 l_end_date_1 Date;
1958 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
1959 x_row_id varchar2(24) := null;
1960
1961 l_actual_parent_id NUMBER := null;
1962
1963 l_prev_level number := 0;
1964
1965 TYPE LEVEL_INFO IS RECORD
1966 ( level NUMBER,
1967 start_date date,
1968 end_date date);
1969
1970 TYPE level_table IS TABLE OF level_info INDEX BY BINARY_INTEGER;
1971
1972 level_value_table level_table;
1973
1974 i BINARY_INTEGER := 0;
1975
1976 procedure populate_table(p_level in number,
1977 p_start_date in date,
1978 p_end_date in date)
1979 is
1980 i BINARY_INTEGER;
1981 begin
1982 i := 0;
1983 i := level_value_table.count;
1984 i := i + 1;
1985 level_value_table(i).level := p_level;
1986 level_value_table(i).start_date := p_start_date;
1987 level_value_table(i).end_date := p_end_date;
1988
1989 end populate_table;
1990
1991 procedure delete_table(p_level in number)
1992 is
1993 k BINARY_INTEGER;
1994 j BINARY_INTEGER;
1995
1996 begin
1997 IF level_value_table.COUNT > 0 THEN
1998 k := level_value_table.FIRST;
1999 LOOP
2000 IF level_value_table(k).level >= p_level THEN
2001 j := k;
2002 IF k = level_value_table.LAST THEN
2003 level_value_table.DELETE(j);
2004 EXIT;
2005 ELSE
2006 k:= level_value_table.NEXT(k);
2007 level_value_table.DELETE(j);
2008 END IF;
2009 ELSE
2010 exit when k = level_value_table.LAST;
2011 k:= level_value_table.NEXT(k);
2012 END IF;
2013 END LOOP;
2014
2015 END IF;
2016
2017 end delete_table;
2018
2019 procedure get_table_date(p_level in number,
2020 p_start_date out NOCOPY date,
2021 p_end_date out NOCOPY date)
2022 is
2023
2024 k BINARY_INTEGER := 0;
2025
2026 begin
2027 for k in 1..level_value_table.COUNT
2028 loop
2029
2030 if level_value_table(k).level = p_level
2031 then
2032 p_start_date := level_value_table(k).start_date;
2033 p_end_date := level_value_table(k).end_date;
2034 exit;
2035 end if;
2036 end loop;
2037 end get_table_date;
2038
2039
2040 BEGIN
2041
2042 --Standard Start of API SAVEPOINT
2043 SAVEPOINT group_denormalize;
2044
2045 x_return_status := fnd_api.g_ret_sts_success;
2046
2047 --Standard Call to check API compatibility
2048 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2049 THEN
2050 RAISE FND_API.G_EXC_ERROR;
2051 END IF;
2052
2053 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2054 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2055 THEN
2056 FND_MSG_PUB.Initialize;
2057 END IF;
2058
2059 l_date := sysdate;
2060 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2061 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2062
2063 -- if no group id is passed in then raise error
2064 IF p_group_id IS NULL
2065 THEN
2066 x_return_status := fnd_api.g_ret_sts_error;
2067 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
2068 FND_MSG_PUB.add;
2069 RAISE fnd_api.g_exc_error;
2070 RETURN;
2071 END IF;
2072
2073 l_date := sysdate;
2074 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2075 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2076
2077
2078 --fetch the start date and the end date for the group
2079 OPEN c_date(p_group_id);
2080 FETCH c_date INTO l_start_date, l_end_date;
2081 CLOSE c_date;
2082
2083 OPEN c_parents(p_group_id);
2084 FETCH c_parents INTO r_parents;
2085
2086 --FOR r_parents IN c_parents(p_group_id)
2087 WHILE(c_parents%FOUND)
2088 LOOP
2089
2090 IF(r_parents.delete_flag <> 'Y')
2091 THEN
2092 l_start_date := r_parents.start_date_active;
2093 l_end_date := r_parents.end_date_active;
2094 IF (r_parents.related_group_id IS NOT NULL)
2095 THEN
2096 --if parent group id is null then this group has no upward hierarchy structure, hence no records
2097 --are to be inserted in the denormalized table
2098 IF(l_prev_level >= r_parents.level)
2099 THEN
2100 get_table_date(r_parents.level - 1, l_start_date_1, l_end_date_1);
2101 delete_table(r_parents.level);
2102 END IF; -- end of level check
2103
2104
2105
2106 IF r_parents.GROUP_ID = P_GROUP_ID
2107 THEN
2108 l_immediate_parent_flag := 'Y';
2109 l_start_date_1 := r_parents.start_date_active;
2110 l_end_date_1 := r_parents.end_date_active;
2111
2112 ELSE
2113 l_immediate_parent_flag := 'N';
2114 if((l_start_date_1 < l_start_date)
2115 OR (l_start_date_1 is null))
2116 then
2117 l_start_date_1 := l_start_date;
2118 end if;
2119 if(l_end_date < l_end_date_1)
2120 then
2121 l_end_date_1 := l_end_date;
2122 elsif(l_end_date_1 is null)
2123 then
2124 l_end_date_1 := l_end_date;
2125 end if;
2126
2127 END IF;
2128
2129 if(l_start_date_1 <= nvl(l_end_date_1, l_start_date_1))
2130 then
2131 OPEN c_dup(p_group_id, r_parents.related_group_id, l_start_date_1, l_end_date_1);
2132
2133 FETCH c_dup into dup;
2134 IF (c_dup%NOTFOUND)
2135 THEN
2136
2137 SELECT jtf_rs_groups_denorm_s.nextval
2138 INTO l_denorm_grp_id
2139 FROM dual;
2140
2141 l_actual_parent_id := getDirectParent(p_group_id,
2142 r_parents.level,
2143 r_parents.related_group_id,
2144 trunc(l_start_date_1),
2145 trunc(l_end_date_1));
2146
2147 jtf_rs_groups_denorm_pkg.insert_row(
2148 X_ROWID => x_row_id,
2149 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
2150 X_GROUP_ID => p_group_id,
2151 X_PARENT_GROUP_ID => r_parents.related_group_id,
2152 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
2153 X_ACTUAL_PARENT_ID => l_actual_parent_id,
2154 X_START_DATE_ACTIVE => trunc(l_start_date_1),
2155 X_END_DATE_ACTIVE => trunc(l_end_date_1),
2156 X_ATTRIBUTE2 => null,
2157 X_ATTRIBUTE3 => null,
2158 X_ATTRIBUTE4 => null,
2159 X_ATTRIBUTE5 => null,
2160 X_ATTRIBUTE6 => null,
2161 X_ATTRIBUTE7 => null,
2162 X_ATTRIBUTE8 => null,
2163 X_ATTRIBUTE9 => null,
2164 X_ATTRIBUTE10 => null,
2165 X_ATTRIBUTE11 => null,
2166 X_ATTRIBUTE12 => null,
2167 X_ATTRIBUTE13 => null,
2168 X_ATTRIBUTE14 => null,
2169 X_ATTRIBUTE15 => null,
2170 X_ATTRIBUTE_CATEGORY => null,
2171 X_ATTRIBUTE1 => null,
2172 X_CREATION_DATE => l_date,
2173 X_CREATED_BY => l_user_id,
2174 X_LAST_UPDATE_DATE => l_date,
2175 X_LAST_UPDATED_BY => l_user_id,
2176 X_LAST_UPDATE_LOGIN => l_login_id,
2177 X_DENORM_LEVEL => r_parents.level );
2178
2179
2180 --call rep manager insert
2181 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
2182 P_API_VERSION => 1.0,
2183 P_GROUP_DENORM_ID => l_denorm_grp_id,
2184 P_GROUP_ID => p_group_id ,
2185 P_PARENT_GROUP_ID => r_parents.related_group_id ,
2186 P_START_DATE_ACTIVE => l_start_date_1 ,
2187 P_END_DATE_ACTIVE => l_end_date_1 ,
2188 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
2189 P_DENORM_LEVEL => r_parents.level,
2190 X_RETURN_STATUS => l_return_status,
2191 X_MSG_COUNT => l_msg_count,
2192 X_MSG_DATA => l_msg_data ) ;
2193
2194 IF(l_return_status <> fnd_api.g_ret_sts_success)
2195 THEN
2196 x_return_status := fnd_api.g_ret_sts_error;
2197 RAISE fnd_api.g_exc_error;
2198 END IF;
2199 END IF;
2200 CLOSE c_dup;
2201 END IF; -- end of st dt check
2202
2203 END IF; --end of group id check
2204 --populating the plsql table
2205 l_prev_level := r_parents.level;
2206 populate_table(l_prev_level, l_start_date_1, l_end_date_1);
2207
2208 END IF; -- end of delete flag check
2209 FETCH c_parents INTO r_parents;
2210 END LOOP;
2211 CLOSE c_parents;
2212
2213
2214
2215 IF fnd_api.to_boolean (p_commit)
2216 THEN
2217 COMMIT WORK;
2218 END IF;
2219
2220
2221 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2222
2223 EXCEPTION
2224 WHEN l_connect_by_loop_error
2225 THEN
2226 ROLLBACK TO group_denormalize;
2227 BEGIN
2228 INSERT_GROUPS_PARENT_NO_CON(
2229 P_API_VERSION => cb_p_api_version,
2230 P_INIT_MSG_LIST => cb_p_init_msg_list,
2231 P_COMMIT => cb_p_commit,
2232 p_group_id => cb_p_group_id,
2233 X_RETURN_STATUS => x_return_status,
2234 X_MSG_COUNT => x_msg_count,
2235 X_MSG_DATA => x_msg_data );
2236 EXCEPTION
2237 WHEN OTHERS
2238 THEN
2239 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2240 fnd_message.set_token('P_SQLCODE',SQLCODE);
2241 fnd_message.set_token('P_SQLERRM',SQLERRM);
2242 fnd_message.set_token('P_API_NAME',l_api_name);
2243 FND_MSG_PUB.add;
2244 x_return_status := fnd_api.g_ret_sts_unexp_error;
2245 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2246 END;
2247 WHEN fnd_api.g_exc_unexpected_error
2248 THEN
2249 ROLLBACK TO group_denormalize;
2250
2251 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
2252 --ND_MSG_PUB.add;
2253 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2254 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2255 WHEN fnd_api.g_exc_error
2256 THEN
2257 ROLLBACK TO group_denormalize;
2258 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2259
2260 WHEN OTHERS
2261 THEN
2262 ROLLBACK TO group_denormalize;
2263 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2264 fnd_message.set_token('P_SQLCODE',SQLCODE);
2265 fnd_message.set_token('P_SQLERRM',SQLERRM);
2266 fnd_message.set_token('P_API_NAME',l_api_name);
2267 FND_MSG_PUB.add;
2268 x_return_status := fnd_api.g_ret_sts_unexp_error;
2269 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2270 END INSERT_GROUPS_PARENT;
2271
2272 --Start of procedure Body
2273 --FOR DELETE
2274 --no being used after 23rd april changes
2275
2276 PROCEDURE DELETE_GROUPS(
2277 P_API_VERSION IN NUMBER,
2278 P_INIT_MSG_LIST IN VARCHAR2,
2279 P_COMMIT IN VARCHAR2,
2280 p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
2281 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2282 X_MSG_COUNT OUT NOCOPY NUMBER,
2283 X_MSG_DATA OUT NOCOPY VARCHAR2)
2284 IS
2285 CURSOR c_group_denorm(l_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
2286 IS
2287 SELECT denorm_grp_id,
2288 parent_group_id
2289 FROM JTF_RS_GROUPS_DENORM
2290 WHERE group_id = l_group_id
2291 AND parent_group_id <> l_group_id; --added this
2292
2293 CURSOR c_child_denorm(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
2294 l_parent_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE )
2295 IS
2296 SELECT denorm_grp_id
2297 FROM JTF_RS_GROUPS_DENORM
2298 WHERE group_id = l_group_id
2299 AND parent_group_id = l_parent_group_id;
2300
2301
2302 CURSOR c_child(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
2303 IS
2304 SELECT rel.group_id,
2305 rel.related_group_id,
2306 rel.start_date_active,
2307 rel.end_date_active
2308 FROM jtf_rs_grp_relations rel
2309 WHERE relation_type = 'PARENT_GROUP'
2310 CONNECT BY rel.related_group_id = prior rel.group_id
2311 AND NVL(rel.delete_flag, 'N') <> 'Y'
2312 AND rel.group_id <> x_group_id
2313 START WITH rel.related_group_id = x_group_id
2314 and nvl(rel.delete_flag,'N') <> 'Y';
2315
2316 --AND rel.start_date_active between l_start_date and nvl(l_end_date, rel.start_date_active +1);
2317
2318
2319 r_child c_child%rowtype;
2320
2321 TYPE CHILD_TYPE IS RECORD
2322 ( p_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
2323 p_related_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
2324 p_start_date_active DATE,
2325 p_end_date_active DATE);
2326
2327
2328 TYPE child_table IS TABLE OF CHILD_type INDEX BY BINARY_INTEGER;
2329 l_child_tab child_table;
2330
2331 i BINARY_INTEGER := 0;
2332
2333 --Declare the variables
2334 --
2335
2336 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUPS';
2337 l_api_version CONSTANT NUMBER :=1.0;
2338 l_date Date;
2339 l_user_id Number;
2340 l_login_id Number;
2341
2342 l_return_status VARCHAR2(200) := fnd_api.g_ret_sts_success;
2343 l_msg_count NUMBER;
2344 l_msg_data VARCHAR2(200);
2345 BEGIN
2346
2347 --Standard Start of API SAVEPOINT
2348 SAVEPOINT group_denormalize;
2349
2350 x_return_status := fnd_api.g_ret_sts_success;
2351
2352 --Standard Call to check API compatibility
2353 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2354 THEN
2355 RAISE FND_API.G_EXC_ERROR;
2356 END IF;
2357
2358 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2359 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2360 THEN
2361 FND_MSG_PUB.Initialize;
2362 END IF;
2363
2364
2365 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2366 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2367 THEN
2368 FND_MSG_PUB.Initialize;
2369 END IF;
2370
2371 l_date := sysdate;
2372 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2373 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2374
2375 --get all the child groups for this group
2376 open c_child(p_group_id);
2377 fetch c_child INTO r_child;
2378 while(c_child%found)
2379 loop
2380
2381 i := i + 1;
2382 l_child_tab(i).p_group_id := r_child.group_id;
2383 l_child_tab(i).p_related_group_id := r_child.related_group_id;
2384 l_child_tab(i).p_start_date_active := r_child.start_date_active;
2385 l_child_tab(i).p_end_date_active := r_child.end_date_active;
2386
2387 FETCH c_child INTO r_child;
2388 END LOOP; --end of par_mgr_cur
2389 CLOSE c_child;
2390
2391
2392
2393
2394 --delete the previous hierarchy for the group
2395 for r_group_denorm IN c_group_denorm(p_group_id)
2396 loop
2397
2398 --call to DELETt records in jtf_rs_rep_managers
2399 JTF_RS_REP_MGR_DENORM_PVT.DELETE_GROUP_DENORM
2400 ( P_API_VERSION => 1.0,
2401 P_INIT_MSG_LIST => p_init_msg_list,
2402 P_COMMIT => null,
2403 P_DENORM_GRP_ID => r_group_denorm.denorm_grp_id,
2404 X_RETURN_STATUS => l_return_status,
2405 X_MSG_COUNT => l_msg_count,
2406 X_MSG_DATA => l_msg_data);
2407 IF(l_return_status <> fnd_api.g_ret_sts_success)
2408 THEN
2409 x_return_status := fnd_api.g_ret_sts_error;
2410 RAISE fnd_api.g_exc_error;
2411 END IF;
2412 IF(r_group_denorm.parent_group_id <> p_group_id)
2413 THEN
2414
2415 --delete hierarchy for child groups
2416 i := 0;
2417 FOR I IN 1 .. l_child_tab.COUNT
2418 LOOP
2419 --fetch the child denorm records
2420 if (l_child_tab(I).p_group_id <> r_group_denorm.parent_group_id)
2421 then
2422 for r_child_denorm IN c_child_denorm(l_child_tab(i).p_group_id,
2423 r_group_denorm.parent_group_id)
2424 loop
2425
2426 --call to DELETE records in jtf_rs_rep_managers
2427 JTF_RS_REP_MGR_DENORM_PVT.DELETE_GROUP_DENORM
2428 ( P_API_VERSION => 1.0,
2429 P_INIT_MSG_LIST => p_init_msg_list,
2430 P_COMMIT => null,
2431 P_DENORM_GRP_ID => r_child_denorm.denorm_grp_id,
2432 X_RETURN_STATUS => l_return_status,
2433 X_MSG_COUNT => l_msg_count,
2434 X_MSG_DATA => l_msg_data);
2435 IF(l_return_status <> fnd_api.g_ret_sts_success)
2436 THEN
2437 x_return_status := fnd_api.g_ret_sts_error;
2438 RAISE fnd_api.g_exc_error;
2439 END IF;
2440
2441
2442
2443 --removing this and calling this in JTF_RS_REP_MGR_DENORM_PVT.DELETE_GROUP_DENORM
2444 --jtf_rs_groups_denorm_pkg.delete_row(r_child_denorm.denorm_grp_id);
2445 end loop;
2446 end if;
2447 END LOOP;
2448 END IF;
2449 --removing this and calling this in JTF_RS_REP_MGR_DENORM_PVT.DELETE_GROUP_DENORM
2450 --jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.denorm_grp_id);
2451
2452
2453 end loop;
2454
2455
2456 --rebuild the group hiearchy again
2457 JTF_RS_GROUP_DENORM_PVT.CREATE_RES_Groups(1.0,NULL, NULL,p_group_id, x_return_status, x_msg_count, x_msg_data);
2458
2459 JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL, p_group_id, x_return_status, x_msg_count, x_msg_data);
2460
2461 --rebuild the hierarchy for reporting managers
2462
2463 IF fnd_api.to_boolean (p_commit)
2464 THEN
2465 COMMIT WORK;
2466 END IF;
2467
2468 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2469
2470 EXCEPTION
2471 WHEN fnd_api.g_exc_unexpected_error
2472 THEN
2473
2474 ROLLBACK TO group_denormalize;
2475 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
2476 --FND_MSG_PUB.add;
2477 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2478 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2479 WHEN fnd_api.g_exc_error
2480 THEN
2481 ROLLBACK TO group_denormalize;
2482 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2483
2484 WHEN OTHERS
2485 THEN
2486 ROLLBACK TO group_denormalize;
2487 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2488 fnd_message.set_token('P_SQLCODE',SQLCODE);
2489 fnd_message.set_token('P_SQLERRM',SQLERRM);
2490 fnd_message.set_token('P_API_NAME',l_api_name);
2491 FND_MSG_PUB.add;
2492 x_return_status := fnd_api.g_ret_sts_unexp_error;
2493 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2494
2495 END DELETE_GROUPS;
2496 ------ CONNECT BY PRIOR - SECTION - Ends
2497
2498
2499
2500 ------ NO CONNECT BY - SECTION - Starts
2501 ------ The new procedures that are using Populate_Parent_Table and
2502 ------- Populate_Child_Table procedures to get results similar to
2503 ------- Connect By Clause used in prior section.
2504 /* This procedure traverse recursively thru the parent
2505 hierarchy of a group and populates g_parent_tab table with
2506 records which are within the date range. This procedure
2507 emulates the connect by prior cursor for finding parent groups. */
2508 PROCEDURE POPULATE_PARENT_TABLE(P_GROUP_ID IN NUMBER,
2509 P_GREATEST_START_DATE IN DATE,
2510 P_LEAST_END_DATE IN DATE,
2511 p_level IN NUMBER)
2512 IS
2513 CURSOR c_parents
2514 IS
2515 SELECT rel.group_id,
2516 rel.related_group_id,
2517 trunc(greatest(rel.start_date_active,
2518 nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
2519 /* Logic : end_date_active, p_least_end_date
2520 NULL , NULL = NULL
2521 NULL , Value = Value
2522 Value , NULL = Value
2523 Value1 , Value2 = least(value1, value2) */
2524 trunc(least(nvl(rel.end_date_active, p_least_end_date),
2525 nvl(p_least_end_date, rel.end_date_active))) least_end_date
2526 FROM jtf_rs_grp_relations rel
2527 WHERE relation_type = 'PARENT_GROUP'
2528 AND rel.group_id = p_group_id
2529 AND NVL(rel.delete_flag, 'N') <> 'Y'
2530 AND least(nvl(end_date_active, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR')),
2531 nvl(p_least_end_date, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR'))) >=
2532 trunc(greatest(start_date_active,
2533 nvl(p_greatest_start_date, start_date_active)));
2534 i INTEGER := 0;
2535 BEGIN
2536 FOR r_parent IN c_parents LOOP
2537 i := g_parent_tab.COUNT+1;
2538 g_parent_tab(i).p_group_id := r_parent.group_id;
2539 g_parent_tab(i).p_related_group_id := r_parent.related_group_id;
2540 g_parent_tab(i).p_start_date_active := r_parent.greatest_start_date;
2541 g_parent_tab(i).p_end_date_active := r_parent.least_end_date;
2542 g_parent_tab(i).level := p_level;
2543 populate_parent_table(g_parent_tab(i).p_related_group_id,
2544 g_parent_tab(i).p_start_date_active,
2545 g_parent_tab(i).p_end_date_active,
2546 p_level+1);
2547 END LOOP;
2548 END;
2549
2550 /* This procedure traverse recursively thru the parent
2551 hierarchy of a group and populates g_parent_tab table with
2552 records which are within the date range. This procedure
2553 emulates the connect by prior cursor for finding parent groups. */
2554 PROCEDURE POPULATE_PARENT_TABLE(P_GROUP_ID IN NUMBER)
2555 IS
2556 BEGIN
2557 g_parent_tab.delete;
2558 populate_parent_table(p_group_id, null, null, 1);
2559 END;
2560
2561 /* This procedure traverse recursively thru the child
2562 hierarchy of a group and populates g_child_tab table with
2563 records which are within the date range. This procedure
2564 emulates the connect by prior cursor for finding parent groups. */
2565 PROCEDURE POPULATE_CHILD_TABLE(P_GROUP_ID IN NUMBER,
2566 P_GREATEST_START_DATE IN DATE,
2567 P_LEAST_END_DATE IN DATE,
2568 P_LEVEL IN NUMBER)
2569 IS
2570 CURSOR c_children
2571 IS
2572 SELECT rel.group_id,
2573 rel.related_group_id,
2574 trunc(greatest(rel.start_date_active,
2575 nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
2576 /* Logic : end_date_active, p_least_end_date
2577 NULL , NULL = NULL
2578 NULL , Value = Value
2579 Value , NULL = Value
2580 Value1 , Value2 = least(value1, value2) */
2581 trunc(least(nvl(rel.end_date_active, p_least_end_date),
2582 nvl(p_least_end_date, rel.end_date_active))) least_end_date
2583 FROM jtf_rs_grp_relations rel
2584 WHERE relation_type = 'PARENT_GROUP'
2585 AND rel.related_group_id = p_group_id
2586 AND NVL(rel.delete_flag, 'N') <> 'Y'
2587 AND least(nvl(end_date_active, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR')),
2588 nvl(p_least_end_date, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR'))) >=
2589
2590 trunc(greatest(start_date_active,
2591 nvl(p_greatest_start_date, start_date_active)));
2592 i INTEGER := 0;
2593 BEGIN
2594 FOR r_child IN c_children LOOP
2595 i := g_child_tab.COUNT+1;
2596 g_child_tab(i).p_group_id := r_child.group_id;
2597 g_child_tab(i).p_related_group_id := r_child.related_group_id;
2598 g_child_tab(i).p_start_date_active := r_child.greatest_start_date;
2599 g_child_tab(i).p_end_date_active := r_child.least_end_date;
2600 g_child_tab(i).level := p_level;
2601 populate_child_table(g_child_tab(i).p_group_id,
2602 g_child_tab(i).p_start_date_active,
2603 g_child_tab(i).p_end_date_active,
2604 p_level+1);
2605 END LOOP;
2606 END;
2607
2608 /* This procedure traverse recursively thru the child
2609 hierarchy of a group and populates g_child_tab table with
2610 records which are within the date range. This procedure
2611 emulates the connect by prior cursor for finding parent groups. */
2612 PROCEDURE POPULATE_CHILD_TABLE(P_GROUP_ID IN NUMBER)
2613 IS
2614 BEGIN
2615 g_child_tab.delete;
2616 populate_child_table(p_group_id, null, null, 1);
2617 END;
2618
2619
2620 /* These are the procedures which are clones of correponding
2621 procedures with no "_NO_CON". These procedures have the same
2622 processing logic as their respective no "_NO_CON" procedures
2623 except that they use POPULATE_PARENT_TABLE and
2624 POPULATE_CHILD_TABLE procedures to get same result as connect
2625 by loop in the no "_NO_CON" procedures.
2626 These procedures were created due to escalations and
2627 urgent one off requirement for Bug # 2140655, 2428389 and 2716624,
2628 which were due to connect by error, for which there was no plausible
2629 solution possible, other than simulating connect by thru PL/SQL.
2630 These procedures are called by respective no "_NO_CON" procedures
2631 when there is connect by loop exception.
2632 Due to the major repeation of processing logic code changes
2633 must be repelated in both "_NO_CON" and no "_NO_CON" procedures.
2634 Hari, Nimit, Nishant. */
2635 PROCEDURE INSERT_GROUPS_NO_CON(
2636 P_API_VERSION IN NUMBER,
2637 P_INIT_MSG_LIST IN VARCHAR2,
2638 P_COMMIT IN VARCHAR2,
2639 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
2640 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2641 X_MSG_COUNT OUT NOCOPY NUMBER,
2642 X_MSG_DATA OUT NOCOPY VARCHAR2 )
2643 IS
2644 CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
2645 IS
2646 SELECT grp.start_date_active,
2647 grp.end_date_active
2648 FROM jtf_rs_groups_b grp
2649 WHERE group_id = x_group_id;
2650
2651 CURSOR c_dup(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
2652 x_parent_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
2653 l_start_date_active date,
2654 l_end_date_active date)
2655 IS
2656 SELECT den.group_id
2657 FROM jtf_rs_groups_denorm den
2658 WHERE den.group_id = x_group_id
2659 AND den.parent_group_id = x_parent_group_id
2660 --AND start_date_active = l_start_date_active
2661 AND ((l_start_date_active between den.start_date_active and
2662 nvl(den.end_date_active,l_start_date_active+1))
2663 OR (l_end_date_active between den.start_date_active
2664 and nvl(den.end_date_active,l_end_date_active+1))
2665 OR ((l_start_date_active <= den.start_date_active)
2666 AND (l_end_date_active >= den.end_date_active
2667 OR l_end_date_active IS NULL)));
2668
2669 i BINARY_INTEGER := 0;
2670 j BINARY_INTEGER := 0;
2671 l_child_tab rel_table;
2672 l_parent_tab rel_table;
2673
2674 --Declare the variables
2675 --
2676 dup c_dup%ROWTYPE;
2677 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_NO_CON';
2678 l_api_version CONSTANT NUMBER :=1.0;
2679 l_immediate_parent_flag VARCHAR2(1) := 'N';
2680 l_date Date;
2681 l_user_id Number;
2682 l_login_id Number;
2683 l_start_date Date;
2684 l_end_date Date;
2685 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2686 l_msg_count number;
2687 l_msg_data varchar2(2000);
2688
2689 l_start_date_active Date;
2690 l_end_date_active Date;
2691
2692 l_start_date_1 Date;
2693 l_end_date_1 Date;
2694 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
2695 x_row_id varchar2(24) := null;
2696
2697
2698 l_prev_level number := 0;
2699 l_prev_par_level number := 0;
2700
2701 TYPE LEVEL_INFO IS RECORD
2702 ( level NUMBER,
2703 start_date date,
2704 end_date date);
2705
2706 TYPE level_table IS TABLE OF level_info INDEX BY BINARY_INTEGER;
2707
2708 level_child_table level_table;
2709 level_par_table level_table;
2710 l_actual_parent_id NUMBER := null;
2711
2712
2713
2714 procedure populate_table(p_level in number,
2715 p_start_date in date,
2716 p_end_date in date,
2717 l_flag in varchar2)
2718 is
2719 l BINARY_INTEGER;
2720 begin
2721 if(l_flag = 'C')
2722 THEN
2723 l := 0;
2724 l := level_child_table.count;
2725 l := l + 1;
2726 level_child_table(l).level := p_level;
2727 level_child_table(l).start_date := p_start_date;
2728 level_child_table(l).end_date := p_end_date;
2729 ELSE
2730
2731 l := 0;
2732 l := level_par_table.count;
2733 l := l + 1;
2734 level_par_table(l).level := p_level;
2735 level_par_table(l).start_date := p_start_date;
2736 level_par_table(l).end_date := p_end_date;
2737
2738
2739 END IF;
2740
2741 end populate_table;
2742
2743 procedure delete_table(p_level in number,
2744 l_flag in varchar2)
2745 is
2746 k BINARY_INTEGER;
2747 j BINARY_INTEGER;
2748
2749 begin
2750 IF (l_flag = 'C')
2751 THEN
2752 IF level_child_table.COUNT > 0 THEN
2753 k := level_child_table.FIRST;
2754 LOOP
2755 IF level_child_table(k).level >= p_level THEN
2756 j := k;
2757 IF k = level_child_table.LAST THEN
2758 level_child_table.DELETE(j);
2759 EXIT;
2760 ELSE
2761 k:= level_child_table.NEXT(k);
2762 level_child_table.DELETE(j);
2763 END IF;
2764 ELSE
2765 exit when k = level_child_table.LAST;
2766 k:= level_child_table.NEXT(k);
2767 END IF;
2768 END LOOP;
2769
2770 END IF;
2771 ELSE
2772 IF level_par_table.COUNT > 0 THEN
2773 k := level_par_table.FIRST;
2774 LOOP
2775 IF level_par_table(k).level >= p_level THEN
2776 j := k;
2777 IF k = level_par_table.LAST THEN
2778 level_par_table.DELETE(j);
2779 EXIT;
2780 ELSE
2781 k:= level_par_table.NEXT(k);
2782 level_par_table.DELETE(j);
2783 END IF;
2784 ELSE
2785 exit when k = level_par_table.LAST;
2786 k:= level_par_table.NEXT(k);
2787 END IF;
2788 END LOOP;
2789
2790 END IF;
2791 END IF;
2792
2793 end delete_table;
2794
2795 procedure get_table_date(p_level in number,
2796 p_start_date out NOCOPY date,
2797 p_end_date out NOCOPY date,
2798 l_flag in varchar2)
2799 is
2800
2801 k BINARY_INTEGER := 0;
2802
2803 begin
2804 IF(l_flag = 'C')
2805 THEN
2806 for k in 1..level_child_table.COUNT
2807 loop
2808 if level_child_table(k).level = p_level
2809 then
2810 p_start_date := level_child_table(k).start_date;
2811 p_end_date := level_child_table(k).end_date;
2812 exit;
2813 end if;
2814 end loop;
2815
2816 ELSE
2817 for k in 1..level_par_table.COUNT
2818 loop
2819
2820 if level_par_table(k).level = p_level
2821 then
2822 p_start_date := level_par_table(k).start_date;
2823 p_end_date := level_par_table(k).end_date;
2824 exit;
2825 end if;
2826 end loop;
2827 END IF;
2828 end get_table_date;
2829
2830
2831 BEGIN
2832
2833 --Standard Start of API SAVEPOINT
2834 SAVEPOINT group_denormalize_no_con;
2835
2836 x_return_status := fnd_api.g_ret_sts_success;
2837
2838 --Standard Call to check API compatibility
2839 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2840 THEN
2841 RAISE FND_API.G_EXC_ERROR;
2842 END IF;
2843
2844 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2845 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2846 THEN
2847 FND_MSG_PUB.Initialize;
2848 END IF;
2849
2850 l_date := sysdate;
2851 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2852 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2853
2854 -- if no group id is passed in then raise error
2855 IF p_group_id IS NULL
2856 THEN
2857 x_return_status := fnd_api.g_ret_sts_error;
2858 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
2859 FND_MSG_PUB.add;
2860 RAISE fnd_api.g_exc_error;
2861 RETURN;
2862 END IF;
2863
2864 l_date := sysdate;
2865 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2866 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2867
2868
2869 --fetch the start date and the end date for the group
2870 OPEN c_date(p_group_id);
2871 FETCH c_date INTO l_start_date, l_end_date;
2872 CLOSE c_date;
2873
2874
2875
2876 --get all the child groups for this group
2877 g_child_tab.delete;
2878 POPULATE_CHILD_TABLE(p_group_id, l_start_date, l_end_date, 1);
2879 l_child_tab := g_child_tab;
2880
2881 IF(l_child_tab.COUNT > 0)
2882 THEN
2883 --changed l_start_date to l_start_date_active
2884 l_start_date_active := l_child_tab(1).p_start_date_active;
2885 l_end_date_active := l_child_tab(1).p_end_date_active;
2886 END IF;
2887 --insert a record with this group for the child group also
2888 i := 0;
2889
2890
2891 FOR I IN 1 .. l_child_tab.COUNT
2892 LOOP
2893 IF(l_child_tab(i).level = 1)
2894 THEN
2895 l_start_date_active := l_child_tab(i).p_start_date_active;
2896 l_end_date_active := l_child_tab(i).p_end_date_active;
2897 delete_table(l_child_tab(i).level, 'C');
2898 ELSIF(l_prev_level >= l_child_tab(i).level)
2899 THEN
2900 get_table_date(l_child_tab(i).level - 1, l_start_date_active, l_end_date_active,'C');
2901 delete_table(l_child_tab(i).level, 'C');
2902 END IF; -- end of level check
2903
2904
2905 --assign start date and end date for which this relation is valid
2906
2907
2908 IF(l_start_date_active < l_child_tab(i).p_start_date_active)
2909 THEN
2910 l_start_date_active := l_child_tab(i).p_start_date_active;
2911 ELSIF(l_start_date_active is null)
2912 THEN
2913 l_start_date_active := l_child_tab(i).p_start_date_active;
2914 ELSE
2915 l_start_date_active := l_start_date_active;
2916 END IF;
2917
2918 IF(l_end_date_active > l_child_tab(i).p_end_date_active)
2919 THEN
2920 l_end_date_active := l_child_tab(i).p_end_date_active;
2921 ELSIF(l_child_tab(i).p_end_date_active IS NULL)
2922 THEN
2923 l_end_date_active := l_end_date_active;
2924 ELSIF(l_end_date_active IS NULL)
2925 THEN
2926 l_end_date_active := l_child_tab(i).p_end_date_active;
2927 END IF;
2928
2929
2930 IF (l_child_tab(i).p_related_group_id = P_GROUP_ID)
2931 THEN
2932 l_immediate_parent_flag := 'Y';
2933 ELSE
2934 l_immediate_parent_flag := 'N';
2935 END IF;
2936 if(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
2937 THEN
2938 OPEN c_dup(l_child_tab(i).p_group_id, p_group_id, l_start_date_active, l_end_date_active);
2939 FETCH c_dup into dup;
2940 IF (c_dup%NOTFOUND)
2941 THEN
2942
2943 SELECT jtf_rs_groups_denorm_s.nextval
2944 INTO l_denorm_grp_id
2945 FROM dual;
2946
2947
2948 l_actual_parent_id := getDirectParent(l_child_tab(i).p_group_id,
2949 l_child_tab(i).level,
2950 p_group_id,
2951 trunc(l_start_date_active),
2952 trunc(l_end_date_active));
2953
2954 jtf_rs_groups_denorm_pkg.insert_row(
2955 X_ROWID => x_row_id,
2956 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
2957 X_GROUP_ID => l_child_tab(i).p_group_id,
2958 X_PARENT_GROUP_ID => p_group_id,
2959 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
2960 X_ACTUAL_PARENT_ID => l_actual_parent_id,
2961 X_START_DATE_ACTIVE => trunc(l_start_date_active),
2962 X_END_DATE_ACTIVE => trunc(l_end_date_active),
2963 X_ATTRIBUTE2 => null,
2964 X_ATTRIBUTE3 => null,
2965 X_ATTRIBUTE4 => null,
2966 X_ATTRIBUTE5 => null,
2967 X_ATTRIBUTE6 => null,
2968 X_ATTRIBUTE7 => null,
2969 X_ATTRIBUTE8 => null,
2970 X_ATTRIBUTE9 => null,
2971 X_ATTRIBUTE10 => null,
2972 X_ATTRIBUTE11 => null,
2973 X_ATTRIBUTE12 => null,
2974 X_ATTRIBUTE13 => null,
2975 X_ATTRIBUTE14 => null,
2976 X_ATTRIBUTE15 => null,
2977 X_ATTRIBUTE_CATEGORY => null,
2978 X_ATTRIBUTE1 => null,
2979 X_CREATION_DATE => l_date,
2980 X_CREATED_BY => l_user_id,
2981 X_LAST_UPDATE_DATE => l_date,
2982 X_LAST_UPDATED_BY => l_user_id,
2983 X_LAST_UPDATE_LOGIN => l_login_id,
2984 X_DENORM_LEVEL => l_child_tab(i).level);
2985
2986 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
2987 P_API_VERSION => 1.0,
2988 P_GROUP_DENORM_ID => l_denorm_grp_id,
2989 P_GROUP_ID => l_child_tab(i).p_group_id ,
2990 P_PARENT_GROUP_ID => p_group_id ,
2991 P_START_DATE_ACTIVE => l_start_date_active ,
2992 P_END_DATE_ACTIVE => l_end_date_active ,
2993 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
2994 P_DENORM_LEVEL => l_child_tab(i).level,
2995 X_RETURN_STATUS => l_return_status,
2996 X_MSG_COUNT => l_msg_count,
2997 X_MSG_DATA => l_msg_data ) ;
2998
2999 IF(l_return_status <> fnd_api.g_ret_sts_success)
3000 THEN
3001 x_return_status := fnd_api.g_ret_sts_error;
3002 RAISE fnd_api.g_exc_error;
3003 END IF;
3004
3005 END IF; -- end of duplicate check
3006 CLOSE c_dup;
3007 END IF; -- end of start date < end date check
3008
3009 --populating the plsql table
3010 l_prev_level := l_child_tab(i).level;
3011 populate_table(l_prev_level, l_start_date_active, l_end_date_active, 'C');
3012
3013
3014 END LOOP;
3015
3016 -- delete all rows from pl/sql table for level
3017 -- delete_table(1, 'C');
3018
3019
3020
3021 l_prev_par_level := 0;
3022 POPULATE_PARENT_TABLE(p_group_id);
3023 l_parent_tab := g_parent_tab;
3024
3025
3026 FOR J IN 1 .. l_parent_tab.COUNT
3027 LOOP
3028 --dbms_output.put_line('444');
3029 l_start_date := l_parent_tab(j).p_start_date_active;
3030 l_end_date := l_parent_tab(j).p_end_date_active;
3031 IF (l_parent_tab(j).p_related_group_id IS NOT NULL)
3032 THEN
3033 IF(l_prev_par_level >= l_parent_tab(j).level)
3034 THEN
3035 get_table_date(l_parent_tab(j).level - 1, l_start_date_1, l_end_date_1, 'P');
3036 delete_table(l_parent_tab(j).level, 'P');
3037 END IF; -- end of level check
3038
3039 --if parent group id is null then this group has no upward hierarchy structure, hence no records
3040 --are to be inserted in the denormalized table
3041 IF l_parent_tab(j).p_GROUP_ID = P_GROUP_ID
3042 THEN
3043 l_immediate_parent_flag := 'Y';
3044 l_start_date_1 := l_parent_tab(j).p_start_date_active;
3045 l_end_date_1 := l_parent_tab(j).p_end_date_active;
3046
3047 ELSE
3048 l_immediate_parent_flag := 'N';
3049 if((l_start_date_1 < l_start_date)
3050 OR (l_start_date_1 is null))
3051 then
3052 l_start_date_1 := l_start_date;
3053 end if;
3054 if(l_end_date < l_end_date_1)
3055 then
3056 l_end_date_1 := l_end_date;
3057 elsif(l_end_date_1 is null)
3058 then
3059 l_end_date_1 := l_end_date;
3060 end if;
3061 END IF;
3062 IF(l_start_date_1 <= nvl(l_end_date_1, l_start_date_1))
3063 THEN
3064 OPEN c_dup(p_group_id, l_parent_tab(j).p_related_group_id, l_start_date_1, l_end_date_1);
3065
3066 FETCH c_dup into dup;
3067 IF (c_dup%NOTFOUND)
3068 THEN
3069
3070 SELECT jtf_rs_groups_denorm_s.nextval
3071 INTO l_denorm_grp_id
3072 FROM dual;
3073
3074 l_actual_parent_id := getDirectParent(p_group_id,
3075 l_parent_tab(j).level,
3076 l_parent_tab(j).p_related_group_id,
3077 trunc(l_start_date_1),
3078 trunc(l_end_date_1));
3079 jtf_rs_groups_denorm_pkg.insert_row(
3080 X_ROWID => x_row_id,
3081 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
3082 X_GROUP_ID => p_group_id,
3083 X_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id,
3084 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
3085 X_ACTUAL_PARENT_ID => l_actual_parent_id,
3086 X_START_DATE_ACTIVE => trunc(l_start_date_1),
3087 X_END_DATE_ACTIVE => trunc(l_end_date_1),
3088 X_ATTRIBUTE2 => null,
3089 X_ATTRIBUTE3 => null,
3090 X_ATTRIBUTE4 => null,
3091 X_ATTRIBUTE5 => null,
3092 X_ATTRIBUTE6 => null,
3093 X_ATTRIBUTE7 => null,
3094 X_ATTRIBUTE8 => null,
3095 X_ATTRIBUTE9 => null,
3096 X_ATTRIBUTE10 => null,
3097 X_ATTRIBUTE11 => null,
3098 X_ATTRIBUTE12 => null,
3099 X_ATTRIBUTE13 => null,
3100 X_ATTRIBUTE14 => null,
3101 X_ATTRIBUTE15 => null,
3102 X_ATTRIBUTE_CATEGORY => null,
3103 X_ATTRIBUTE1 => null,
3104 X_CREATION_DATE => l_date,
3105 X_CREATED_BY => l_user_id,
3106 X_LAST_UPDATE_DATE => l_date,
3107 X_LAST_UPDATED_BY => l_user_id,
3108 X_LAST_UPDATE_LOGIN => l_login_id,
3109 X_DENORM_LEVEL => l_parent_tab(j).level);
3110
3111
3112
3113 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
3114 P_API_VERSION => 1.0,
3115 P_GROUP_DENORM_ID => l_denorm_grp_id,
3116 P_GROUP_ID => p_group_id ,
3117 P_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id ,
3118 P_START_DATE_ACTIVE => l_start_date_1 ,
3119 P_END_DATE_ACTIVE => l_end_date_1 ,
3120 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
3121 P_DENORM_LEVEL => l_parent_tab(j).level,
3122 X_RETURN_STATUS => l_return_status,
3123 X_MSG_COUNT => l_msg_count,
3124 X_MSG_DATA => l_msg_data ) ;
3125
3126 IF(l_return_status <> fnd_api.g_ret_sts_success)
3127 THEN
3128 x_return_status := fnd_api.g_ret_sts_error;
3129 RAISE fnd_api.g_exc_error;
3130 END IF;
3131 END IF;
3132 CLOSE c_dup;
3133
3134
3135 --insert a record with this parent group for the child group also
3136 l_prev_level := 0;
3137 i := 0;
3138 --initialize dates
3139 FOR i IN 1 .. l_child_tab.COUNT
3140 LOOP
3141 IF(l_child_tab(i).level = 1)
3142 THEN
3143 l_start_date_active := l_start_date_1;
3144 l_end_date_active := l_end_date_1;
3145 delete_table(l_child_tab(i).level, 'C');
3146 ELSIF(l_prev_level >= l_child_tab(i).level)
3147 THEN
3148 get_table_date(l_child_tab(i).level - 1, l_start_date_active, l_end_date_active,'C');
3149 delete_table(l_child_tab(i).level, 'C');
3150 END IF; -- end of level check
3151 --dbms_output.put_line('group..'||to_char(l_child_tab(i).p_group_id));
3152 --dbms_output.put_line(to_char(l_start_date_active, 'dd-mon-yyyy')||'..'|| to_char(l_end_date_active, 'dd-mon-yyyy'));
3153 --dbms_output.put_line(to_char(l_child_tab(i).p_start_date_active, 'dd-mon-yyyy') ||'..'||to_char(l_child_tab(i).p_end_date_active, 'dd-mon-yyyy'));
3154
3155 --assign start date and end date for which this relation is valid
3156 IF(l_start_date_active < l_child_tab(i).p_start_date_active)
3157 THEN
3158 l_start_date_active := l_child_tab(i).p_start_date_active;
3159 ELSIF(l_start_date_active is null)
3160 THEN
3161 l_start_date_active := l_child_tab(i).p_start_date_active;
3162 ELSE
3163 l_start_date_active := l_start_date_active;
3164 END IF;
3165
3166 IF(l_end_date_active > l_child_tab(i).p_end_date_active)
3167 THEN
3168 l_end_date_active := l_child_tab(i).p_end_date_active;
3169 ELSIF(l_child_tab(i).p_end_date_active IS NULL)
3170 THEN
3171 l_end_date_active := l_end_date_active;
3172 ELSIF(l_end_date_active IS NULL)
3173 THEN
3174 l_end_date_active := l_child_tab(i).p_end_date_active;
3175 END IF;
3176
3177 l_immediate_parent_flag := 'N';
3178 IF(l_start_date_active <= nvl(l_end_date_active, l_start_date_active))
3179 THEN
3180 OPEN c_dup(l_child_tab(i).p_group_id, l_parent_tab(j).p_related_group_id, l_start_date_active, l_end_date_active);
3181 FETCH c_dup into dup;
3182 IF (c_dup%NOTFOUND)
3183 THEN
3184
3185 SELECT jtf_rs_groups_denorm_s.nextval
3186 INTO l_denorm_grp_id
3187 FROM dual;
3188
3189 l_actual_parent_id := getDirectParent(l_child_tab(i).p_group_id,
3190 l_child_tab(i).level + l_parent_tab(j).level,
3191 l_parent_tab(j).p_related_group_id,
3192 trunc(l_start_date_active),
3193 trunc(l_end_date_active));
3194 jtf_rs_groups_denorm_pkg.insert_row(
3195 X_ROWID => x_row_id,
3196 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
3197 X_GROUP_ID => l_child_tab(i).p_group_id,
3198 X_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id,
3199 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
3200 X_ACTUAL_PARENT_ID => l_actual_parent_id,
3201 X_START_DATE_ACTIVE => trunc(l_start_date_active),
3202 X_END_DATE_ACTIVE => trunc(l_end_date_active),
3203 X_ATTRIBUTE2 => null,
3204 X_ATTRIBUTE3 => null,
3205 X_ATTRIBUTE4 => null,
3206 X_ATTRIBUTE5 => null,
3207 X_ATTRIBUTE6 => null,
3208 X_ATTRIBUTE7 => null,
3209 X_ATTRIBUTE8 => null,
3210 X_ATTRIBUTE9 => null,
3211 X_ATTRIBUTE10 => null,
3212 X_ATTRIBUTE11 => null,
3213 X_ATTRIBUTE12 => null,
3214 X_ATTRIBUTE13 => null,
3215 X_ATTRIBUTE14 => null,
3216 X_ATTRIBUTE15 => null,
3217 X_ATTRIBUTE_CATEGORY => null,
3218 X_ATTRIBUTE1 => null,
3219 X_CREATION_DATE => l_date,
3220 X_CREATED_BY => l_user_id,
3221 X_LAST_UPDATE_DATE => l_date,
3222 X_LAST_UPDATED_BY => l_user_id,
3223 X_LAST_UPDATE_LOGIN => l_login_id,
3224 X_DENORM_LEVEL => l_child_tab(i).level + l_parent_tab(j).level);
3225
3226 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
3227 P_API_VERSION => 1.0,
3228 P_GROUP_DENORM_ID => l_denorm_grp_id,
3229 P_GROUP_ID => l_child_tab(i).p_group_id ,
3230 P_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id ,
3231 P_START_DATE_ACTIVE => l_start_date_active ,
3232 P_END_DATE_ACTIVE => l_end_date_active ,
3233 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
3234 P_DENORM_LEVEL => l_child_tab(i).level + l_parent_tab(j).level,
3235 X_RETURN_STATUS => l_return_status,
3236 X_MSG_COUNT => l_msg_count,
3237 X_MSG_DATA => l_msg_data ) ;
3238
3239 IF(l_return_status <> fnd_api.g_ret_sts_success)
3240 THEN
3241 x_return_status := fnd_api.g_ret_sts_error;
3242 RAISE fnd_api.g_exc_error;
3243 END IF;
3244
3245 END IF; -- end of duplicate check
3246 CLOSE c_dup;
3247
3248 END IF; -- end of start_date_active check
3249
3250 --populating the plsql table
3251 l_prev_level := l_child_tab(i).level;
3252 populate_table(l_prev_level, l_start_date_active, l_end_date_active, 'C');
3253
3254 END LOOP; -- end of child tab insert
3255 -- delete all rows from pl/sql table for level
3256 delete_table(1, 'C');
3257
3258 END IF; -- end of parent start date check
3259 --populating the plsql table
3260 l_prev_par_level := l_parent_tab(j).level;
3261 populate_table(l_prev_par_level, l_start_date_1, l_end_date_1, 'P');
3262 END IF; --end of group id check
3263
3264 END LOOP;
3265
3266 IF fnd_api.to_boolean (p_commit)
3267 THEN
3268 COMMIT WORK;
3269 END IF;
3270
3271
3272 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3273
3274 EXCEPTION
3275 WHEN fnd_api.g_exc_unexpected_error
3276 THEN
3277 ROLLBACK TO group_denormalize_no_con;
3278
3279 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
3280 --ND_MSG_PUB.add;
3281 --x_return_status := fnd_api.g_ret_sts_unexp_error;
3282 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3283 WHEN fnd_api.g_exc_error
3284 THEN
3285 ROLLBACK TO group_denormalize_no_con;
3286 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3287
3288 WHEN OTHERS
3289 THEN
3290 ROLLBACK TO group_denormalize_no_con;
3291 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3292 fnd_message.set_token('P_SQLCODE',SQLCODE);
3293 fnd_message.set_token('P_SQLERRM',SQLERRM);
3294 fnd_message.set_token('P_API_NAME',l_api_name);
3295 FND_MSG_PUB.add;
3296 x_return_status := fnd_api.g_ret_sts_unexp_error;
3297 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3298 END INSERT_GROUPS_NO_CON;
3299
3300
3301 --Start of procedure Body
3302 --FOR UPDATE
3303
3304 /* These are the procedures which are clones of correponding
3305 procedures with no "_NO_CON". These procedures have the same
3306 processing logic as their respective no "_NO_CON" procedures
3307 except that they use POPULATE_PARENT_TABLE and
3308 POPULATE_CHILD_TABLE procedures to get same result as connect
3309 by loop in the no "_NO_CON" procedures.
3310 These procedures were created due to escalations and
3311 urgent one off requirement for Bug # 2140655, 2428389 and 2716624,
3312 which were due to connect by error, for which there was no plausible
3313 solution possible, other than simulating connect by thru PL/SQL.
3314 These procedures are called by respective no "_NO_CON" procedures
3315 when there is connect by loop exception.
3316 Due to the major repeation of processing logic code changes
3317 must be repelated in both "_NO_CON" and no "_NO_CON" procedures.
3318 Hari, Nimit, Nishant. */
3319 PROCEDURE UPDATE_GROUPS_NO_CON(
3320 P_API_VERSION IN NUMBER,
3321 P_INIT_MSG_LIST IN VARCHAR2,
3322 P_COMMIT IN VARCHAR2,
3323 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3324 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3325 X_MSG_COUNT OUT NOCOPY NUMBER,
3326 X_MSG_DATA OUT NOCOPY VARCHAR2 )
3327 IS
3328
3329
3330 CURSOR c_group_denorm(l_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
3331 IS
3332 SELECT denorm_grp_id,
3333 group_id,
3334 parent_group_id
3335 FROM JTF_RS_GROUPS_DENORM
3336 WHERE group_id = l_group_id
3337 AND PARENT_GROUP_ID <> L_GROUP_ID;
3338
3339
3340 --Declare the variables
3341 --
3342
3343 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUPS_NO_CON';
3344 l_api_version CONSTANT NUMBER :=1.0;
3345
3346 l_date DATE;
3347 l_user_id NUMBER := 1;
3348 l_login_id NUMBER := 1;
3349 l_return_status VARCHAR2(200) := fnd_api.g_ret_sts_success;
3350 l_msg_count NUMBER;
3351 l_msg_data VARCHAR2(200);
3352 i BINARY_INTEGER := 0;
3353 l_child_tab rel_table;
3354 l_parent_tab rel_table;
3355 BEGIN
3356
3357 --Standard Start of API SAVEPOINT
3358 SAVEPOINT group_denormalize_no_con;
3359
3360 x_return_status := fnd_api.g_ret_sts_success;
3361
3362 --Standard Call to check API compatibility
3363 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
3364 THEN
3365 RAISE FND_API.G_EXC_ERROR;
3366 END IF;
3367
3368 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3369 IF FND_API.To_boolean(P_INIT_MSG_LIST)
3370 THEN
3371 FND_MSG_PUB.Initialize;
3372 END IF;
3373
3374
3375 l_date := sysdate;
3376 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3377 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
3378 --delete the previous hierarchy for the group
3379 for r_group_denorm IN c_group_denorm(p_group_id)
3380 loop
3381 JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
3382 P_API_VERSION => 1.0,
3383 P_GROUP_ID => r_group_denorm.group_id,
3384 P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
3385 X_RETURN_STATUS => l_return_status,
3386 X_MSG_COUNT => l_msg_count,
3387 X_MSG_DATA => l_msg_data);
3388
3389
3390
3391 IF(l_return_status <> fnd_api.g_ret_sts_success)
3392 THEN
3393 x_return_status := fnd_api.g_ret_sts_error;
3394 RAISE fnd_api.g_exc_error;
3395 END IF;
3396 jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
3397 end loop;
3398
3399
3400
3401 --delete the hiearchy of all the child records of the group
3402 POPULATE_CHILD_TABLE(p_group_id);
3403 l_child_tab := g_child_tab;
3404
3405 FOR I IN 1 .. l_child_tab.COUNT
3406 LOOP
3407 for r_group_denorm IN c_group_denorm(l_child_tab(i).p_group_id)
3408 loop
3409 JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
3410 P_API_VERSION => 1.0,
3411 P_GROUP_ID => r_group_denorm.group_id,
3412 P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
3413 X_RETURN_STATUS => l_return_status,
3414 X_MSG_COUNT => l_msg_count,
3415 X_MSG_DATA => l_msg_data);
3416
3417 IF(l_return_status <> fnd_api.g_ret_sts_success)
3418 THEN
3419 x_return_status := fnd_api.g_ret_sts_error;
3420 RAISE fnd_api.g_exc_error;
3421 END IF;
3422
3423 jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
3424 end loop;
3425 END LOOP;
3426
3427
3428 --rebuild the hiearchy of all the child records of the group
3429 FOR I IN 1 .. l_child_tab.COUNT
3430 LOOP
3431 JTF_RS_GROUP_DENORM_PVT.Insert_Groups_No_Con(1.0,NULL, NULL,l_child_tab(i).p_group_id, x_return_status, x_msg_count, x_msg_data);
3432 END LOOP;
3433
3434 --rebuild the group hiearchy again
3435 JTF_RS_GROUP_DENORM_PVT.insert_groups_no_con(1.0,NULL, NULL,p_group_id, x_return_status, x_msg_count, x_msg_data);
3436
3437 IF fnd_api.to_boolean (p_commit)
3438 THEN
3439 COMMIT WORK;
3440 END IF;
3441
3442 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3443
3444 EXCEPTION
3445 WHEN fnd_api.g_exc_unexpected_error
3446 THEN
3447 ROLLBACK TO group_denormalize_no_con;
3448 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
3449 --FND_MSG_PUB.add;
3450 --x_return_status := fnd_api.g_ret_sts_unexp_error;
3451 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3452 WHEN fnd_api.g_exc_error
3453 THEN
3454 ROLLBACK TO group_denormalize_no_con;
3455 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3456
3457 WHEN OTHERS
3458 THEN
3459 ROLLBACK TO group_denormalize_no_con;
3460 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3461 fnd_message.set_token('P_SQLCODE',SQLCODE);
3462 fnd_message.set_token('P_SQLERRM',SQLERRM);
3463 fnd_message.set_token('P_API_NAME',l_api_name);
3464 FND_MSG_PUB.add;
3465 x_return_status := fnd_api.g_ret_sts_unexp_error;
3466 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3467
3468 END UPDATE_GROUPS_NO_CON;
3469
3470
3471
3472 /* These are the procedures which are clones of correponding
3473 procedures with no "_NO_CON". These procedures have the same
3474 processing logic as their respective no "_NO_CON" procedures
3475 except that they use POPULATE_PARENT_TABLE and
3476 POPULATE_CHILD_TABLE procedures to get same result as connect
3477 by loop in the no "_NO_CON" procedures.
3478 These procedures were created due to escalations and
3479 urgent one off requirement for Bug # 2140655, 2428389 and 2716624,
3480 which were due to connect by error, for which there was no plausible
3481 solution possible, other than simulating connect by thru PL/SQL.
3482 These procedures are called by respective no "_NO_CON" procedures
3483 when there is connect by loop exception.
3484 Due to the major repeation of processing logic code changes
3485 must be repelated in both "_NO_CON" and no "_NO_CON" procedures.
3486 Hari, Nimit, Nishant. */
3487 PROCEDURE DELETE_GRP_RELATIONS_NO_CON(
3488 P_API_VERSION IN NUMBER,
3489 P_INIT_MSG_LIST IN VARCHAR2,
3490 P_COMMIT IN VARCHAR2,
3491 p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
3492 p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
3493 p_related_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
3494 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3495 X_MSG_COUNT OUT NOCOPY NUMBER,
3496 X_MSG_DATA OUT NOCOPY VARCHAR2)
3497 is
3498
3499 i BINARY_INTEGER := 0;
3500
3501 l_child_tab rel_table;
3502 l_parent_tab rel_table;
3503
3504 CURSOR check_parent_cur(l_group_id number,
3505 l_related_group_id number)
3506 IS
3507 SELECT rel.group_id,
3508 rel.related_group_id,
3509 rel.start_date_active,
3510 rel.end_date_active
3511 FROM jtf_rs_grp_relations rel
3512 WHERE relation_type = 'PARENT_GROUP'
3513 AND related_group_id = l_related_group_id
3514 CONNECT BY rel.group_id = prior rel.related_group_id
3515 AND NVL(rel.delete_flag, 'N') <> 'Y'
3516 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
3517 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
3518 trunc(prior rel.start_date_active)) OR
3519 (rel.start_date_active > trunc(prior rel.start_date_active)
3520 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
3521 rel.start_date_active)))
3522 --AND rel.related_group_id <> p_related_group_id
3523 START WITH rel.group_id = l_group_id
3524 AND NVL(rel.delete_flag, 'N') <> 'Y';
3525
3526 check_parent_rec check_parent_cur%rowtype;
3527
3528 j BINARY_INTEGER := 0;
3529
3530 TYPE role_relate_TYPE IS RECORD
3531 ( role_relate_id NUMBER,
3532 group_id NUMBER);
3533
3534 TYPE child_rol_rel_table IS TABLE OF role_relate_TYPE INDEX BY BINARY_INTEGER;
3535 l_child_rol_rel_tab child_rol_rel_table;
3536
3537 k BINARY_INTEGER := 0;
3538
3539 TYPE par_rol_rel_table IS TABLE OF role_relate_TYPE INDEX BY BINARY_INTEGER;
3540 l_par_rol_rel_tab par_rol_rel_table;
3541
3542 l BINARY_INTEGER := 0;
3543
3544
3545 cursor rr_cur(l_no number)
3546 is
3547 select rel.role_relate_id,
3548 mem.group_id
3549 from jtf_rs_group_members mem,
3550 jtf_rs_role_relations rel
3551 where mem.group_id = l_no
3552 and nvl(mem.delete_flag , 'N') <> 'Y'
3553 and mem.group_member_id = rel.role_resource_id
3554 and rel.role_resource_type = 'RS_GROUP_MEMBER'
3555 and nvl(rel.delete_flag, 'N') <> 'Y';
3556
3557 role_rel_rec rr_cur%rowtype;
3558
3559 cursor rr_mgr_cur(l_group_id number)
3560 is
3561 select rel.role_relate_id,
3562 mem.group_id
3563 from jtf_rs_group_members mem,
3564 jtf_rs_role_relations rel,
3565 jtf_rs_roles_b rol
3566 where mem.group_id = l_group_id
3567 and nvl(mem.delete_flag , 'N') <> 'Y'
3568 and mem.group_member_id = rel.role_resource_id
3569 and rel.role_resource_type = 'RS_GROUP_MEMBER'
3570 and nvl(rel.delete_flag, 'N') <> 'Y'
3571 and rel.role_id = rol.role_id
3572 and (
3573 nvl(rol.manager_flag, 'N') = 'Y'
3574 or
3575 nvl(rol.admin_flag, 'N') = 'Y'
3576 );
3577
3578 role_rel_mgr_rec rr_mgr_cur%rowtype;
3579
3580 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GRP_RELATIONS_NO_CON';
3581 l_api_version CONSTANT NUMBER :=1.0;
3582 l_date Date;
3583 l_user_id Number;
3584 l_login_id Number;
3585
3586 l_return_status VARCHAR2(200) := fnd_api.g_ret_sts_success;
3587 l_msg_count NUMBER;
3588 l_found BOOLEAN := FALSE;
3589
3590 begin
3591
3592 --Standard Start of API SAVEPOINT
3593 SAVEPOINT group_denormalize_no_con;
3594
3595 x_return_status := fnd_api.g_ret_sts_success;
3596
3597 --Standard Call to check API compatibility
3598 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
3599 THEN
3600 RAISE FND_API.G_EXC_ERROR;
3601 END IF;
3602
3603 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3604 IF FND_API.To_boolean(P_INIT_MSG_LIST)
3605 THEN
3606 FND_MSG_PUB.Initialize;
3607 END IF;
3608
3609
3610 l_date := sysdate;
3611 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3612 --to add the grooup_id as child as this will not be included in cursor
3613 k := 0;
3614 -- get the role relate ids for this group
3615 open rr_cur(p_group_id);
3616 fetch rr_cur into role_rel_rec;
3617 while (rr_cur%found)
3618 loop
3619 k := K + 1;
3620 l_child_rol_rel_tab(k).role_relate_id := role_rel_rec.role_relate_id;
3621 l_child_rol_rel_tab(k).group_id := role_rel_rec.group_id;
3622
3623 fetch rr_cur into role_rel_rec;
3624 end loop; -- end of role relate cur
3625 close rr_cur;
3626 --get all the child groups for this group
3627 g_child_tab.delete;
3628 i := 1;
3629 g_child_tab(i).p_group_id := p_group_id;
3630 g_child_tab(i).p_related_group_id := p_related_group_id;
3631 POPULATE_CHILD_TABLE(p_group_id, null, null, 1);
3632 l_child_tab := g_child_tab;
3633
3634 FOR I IN 1 .. l_child_tab.count LOOP
3635 -- get the role relate ids for this group
3636 open rr_cur(l_child_tab(i).p_group_id);
3637 fetch rr_cur into role_rel_rec;
3638 while (rr_cur%found)
3639 loop
3640 k := K + 1;
3641 l_child_rol_rel_tab(k).role_relate_id := role_rel_rec.role_relate_id;
3642 l_child_rol_rel_tab(k).group_id := role_rel_rec.group_id;
3643
3644 fetch rr_cur into role_rel_rec;
3645 end loop; -- end of role relate cur
3646 close rr_cur;
3647
3648 END LOOP;
3649
3650 -- insert the parent group in the table as the parent cursor does not fetch this record
3651 l := 0;
3652 -- get the role relate ids for this group
3653 open rr_mgr_cur(p_related_group_id);
3654 fetch rr_mgr_cur into role_rel_mgr_rec;
3655 while (rr_mgr_cur%found)
3656 loop
3657 l :=l + 1;
3658 l_par_rol_rel_tab(l).role_relate_id := role_rel_mgr_rec.role_relate_id;
3659 l_par_rol_rel_tab(l).group_id := role_rel_mgr_rec.group_id;
3660
3661 fetch rr_mgr_cur into role_rel_mgr_rec;
3662 end loop;
3663 close rr_mgr_cur;
3664
3665 l_parent_tab.delete;
3666 j := 0;
3667 j := j + 1;
3668 g_parent_tab(j).p_group_id := p_group_id;
3669 g_parent_tab(j).p_related_group_id := p_related_group_id;
3670 populate_parent_table(p_group_id, null, null, 1);
3671 l_parent_tab := g_parent_tab;
3672
3673 FOR I IN 1 .. l_parent_tab.COUNT
3674 LOOP
3675 -- get the role relate ids for this group
3676 open rr_mgr_cur(l_parent_tab(i).p_related_group_id);
3677 fetch rr_mgr_cur into role_rel_mgr_rec;
3678 while (rr_mgr_cur%found)
3679 loop
3680 l :=l + 1;
3681 l_par_rol_rel_tab(l).role_relate_id := role_rel_mgr_rec.role_relate_id;
3682 l_par_rol_rel_tab(l).group_id := role_rel_mgr_rec.group_id;
3683
3684 fetch rr_mgr_cur into role_rel_mgr_rec;
3685 end loop; -- end of role relate cur
3686 close rr_mgr_cur;
3687
3688
3689
3690 END LOOP;
3691
3692 --DELETE GROUP DENORM
3693 FOR j IN 1 .. l_parent_tab.COUNT
3694 LOOP
3695 FOR i IN 1 .. l_child_tab.COUNT
3696 LOOP
3697 --delete group denorm
3698 begin
3699 delete jtf_rs_groups_denorm
3700 where group_id = l_child_tab(i).p_group_id
3701 and parent_group_id = l_parent_tab(j).p_related_group_id;
3702 exception
3703 when others then
3704 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3705 fnd_message.set_token('P_SQLCODE',SQLCODE);
3706 fnd_message.set_token('P_SQLERRM',SQLERRM);
3707 fnd_message.set_token('P_API_NAME', l_api_name);
3708 FND_MSG_PUB.add;
3709 x_return_status := fnd_api.g_ret_sts_unexp_error;
3710 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3711 RAISE fnd_api.g_exc_unexpected_error;
3712
3713 end;
3714
3715 end loop; -- end of child
3716 end loop; -- end of parent
3717
3718
3719 --DELETE REP MANAGER
3720 FOR l IN 1 .. l_par_rol_rel_tab.COUNT
3721 LOOP
3722 FOR k IN 1 .. l_child_rol_rel_tab.COUNT
3723 LOOP
3724 --delete rep mgr
3725 begin
3726 delete jtf_rs_rep_managers
3727 where par_role_relate_id = l_par_rol_rel_tab(l).role_relate_id
3728 and child_role_relate_id = l_child_rol_rel_tab(k).role_relate_id;
3729
3730 exception
3731 when others then
3732 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3733 fnd_message.set_token('P_SQLCODE',SQLCODE);
3734 fnd_message.set_token('P_SQLERRM',SQLERRM);
3735 fnd_message.set_token('P_API_NAME', l_api_name);
3736 FND_MSG_PUB.add;
3737 x_return_status := fnd_api.g_ret_sts_unexp_error;
3738 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3739 RAISE fnd_api.g_exc_unexpected_error;
3740 end;
3741
3742 end loop; -- end of child
3743 end loop; -- end of parent
3744
3745
3746
3747 --now recreate hierarchy in case same parent existed for child through some diff branch
3748
3749 FOR i IN 1 .. l_child_tab.COUNT
3750 LOOP
3751 l_found := FALSE;
3752
3753 FOR j IN 1 .. l_parent_tab.COUNT
3754 LOOP
3755 BEGIN
3756 open check_parent_cur(l_child_tab(i).p_group_id,
3757 l_parent_tab(j).p_related_group_id);
3758 fetch check_parent_cur into check_parent_rec;
3759 if (check_parent_cur%found)
3760 then
3761 l_found := TRUE;
3762 jtf_rs_group_denorm_pvt.insert_groups_parent_no_con(
3763 p_api_version => 1.0,
3764 p_init_msg_list => NULL,
3765 p_commit => 'T',
3766 p_group_id => l_child_tab(i).p_group_id,
3767 x_return_status => x_return_status,
3768 x_msg_count => x_msg_count,
3769 x_msg_data => x_msg_data);
3770
3771 IF(x_return_status <> fnd_api.g_ret_sts_success)
3772 THEN
3773 x_return_status := fnd_api.g_ret_sts_error;
3774 RAISE fnd_api.g_exc_error;
3775 END IF;
3776
3777 else
3778 close check_parent_cur;
3779 end if;
3780 EXCEPTION
3781 WHEN OTHERS THEN
3782 NULL;
3783 END;
3784 if l_found
3785 then
3786 --since the entire parent hierarchy for the group has been built no point checking for further parents
3787 exit;
3788 end if;
3789 END LOOP; -- end of parent tab loop
3790 if(check_parent_cur%isopen)
3791 then
3792 close check_parent_cur;
3793 end if;
3794
3795 END LOOP; -- end of child tab loop
3796 EXCEPTION
3797 WHEN fnd_api.g_exc_unexpected_error
3798 THEN
3799
3800 ROLLBACK TO group_denormalize_no_con;
3801 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3802 WHEN fnd_api.g_exc_error
3803 THEN
3804 ROLLBACK TO group_denormalize_no_con;
3805 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3806
3807 WHEN OTHERS
3808 THEN
3809 ROLLBACK TO group_denormalize_no_con;
3810 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3811 fnd_message.set_token('P_SQLCODE',SQLCODE);
3812 fnd_message.set_token('P_SQLERRM',SQLERRM);
3813 fnd_message.set_token('P_API_NAME',l_api_name);
3814 FND_MSG_PUB.add;
3815 x_return_status := fnd_api.g_ret_sts_unexp_error;
3816 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3817
3818 end delete_grp_relations_no_con;
3819
3820 /* These are the procedures which are clones of correponding
3821 procedures with no "_NO_CON". These procedures have the same
3822 processing logic as their respective no "_NO_CON" procedures
3823 except that they use POPULATE_PARENT_TABLE and
3824 POPULATE_CHILD_TABLE procedures to get same result as connect
3825 by loop in the no "_NO_CON" procedures.
3826 These procedures were created due to escalations and
3827 urgent one off requirement for Bug # 2140655, 2428389 and 2716624,
3828 which were due to connect by error, for which there was no plausible
3829 solution possible, other than simulating connect by thru PL/SQL.
3830 These procedures are called by respective no "_NO_CON" procedures
3831 when there is connect by loop exception.
3832 Due to the major repeation of processing logic code changes
3833 must be repelated in both "_NO_CON" and no "_NO_CON" procedures.
3834 Hari, Nimit, Nishant. */
3835 PROCEDURE INSERT_GROUPS_PARENT_NO_CON(
3836 P_API_VERSION IN NUMBER,
3837 P_INIT_MSG_LIST IN VARCHAR2,
3838 P_COMMIT IN VARCHAR2,
3839 p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
3840 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3841 X_MSG_COUNT OUT NOCOPY NUMBER,
3842 X_MSG_DATA OUT NOCOPY VARCHAR2 )
3843 IS
3844 CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
3845 IS
3846 SELECT grp.start_date_active,
3847 grp.end_date_active
3848 FROM jtf_rs_groups_b grp
3849 WHERE group_id = x_group_id;
3850
3851 CURSOR c_dup(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
3852 x_parent_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
3853 l_start_date_active date,
3854 l_end_date_active date)
3855 IS
3856 SELECT den.group_id
3857 FROM jtf_rs_groups_denorm den
3858 WHERE den.group_id = x_group_id
3859 AND den.parent_group_id = x_parent_group_id
3860 --AND start_date_active = l_start_date_active
3861 AND ((l_start_date_active between den.start_date_active and
3862 nvl(den.end_date_active,l_start_date_active+1))
3863 OR (l_end_date_active between den.start_date_active
3864 and nvl(den.end_date_active,l_end_date_active+1))
3865 OR ((l_start_date_active <= den.start_date_active)
3866 AND (l_end_date_active >= den.end_date_active
3867 OR l_end_date_active IS NULL)));
3868
3869
3870 --Declare the variables
3871 --
3872 dup c_dup%ROWTYPE;
3873 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_PARENT_NO_CON';
3874 l_api_version CONSTANT NUMBER :=1.0;
3875 l_immediate_parent_flag VARCHAR2(1) := 'N';
3876 l_date Date;
3877 l_user_id Number;
3878 l_login_id Number;
3879 l_start_date Date;
3880 l_end_date Date;
3881 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3882 l_msg_count number;
3883 l_msg_data varchar2(2000);
3884
3885 l_start_date_active Date;
3886 l_end_date_active Date;
3887
3888 l_start_date_1 Date;
3889 l_end_date_1 Date;
3890 l_DENORM_GRP_ID JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE;
3891 x_row_id varchar2(24) := null;
3892
3893 l_child_tab rel_table;
3894 l_parent_tab rel_table;
3895
3896 l_prev_level number := 0;
3897
3898 TYPE LEVEL_INFO IS RECORD
3899 ( level NUMBER,
3900 start_date date,
3901 end_date date);
3902
3903 TYPE level_table IS TABLE OF level_info INDEX BY BINARY_INTEGER;
3904
3905 level_value_table level_table;
3906 l_actual_parent_id NUMBER := null;
3907
3908 i BINARY_INTEGER := 0;
3909
3910 procedure populate_table(p_level in number,
3911 p_start_date in date,
3912 p_end_date in date)
3913 is
3914 i BINARY_INTEGER;
3915 begin
3916 i := 0;
3917 i := level_value_table.count;
3918 i := i + 1;
3919 level_value_table(i).level := p_level;
3920 level_value_table(i).start_date := p_start_date;
3921 level_value_table(i).end_date := p_end_date;
3922
3923 end populate_table;
3924
3925 procedure delete_table(p_level in number)
3926 is
3927 k BINARY_INTEGER;
3928 j BINARY_INTEGER;
3929
3930 begin
3931 IF level_value_table.COUNT > 0 THEN
3932 k := level_value_table.FIRST;
3933 LOOP
3934 IF level_value_table(k).level >= p_level THEN
3935 j := k;
3936 IF k = level_value_table.LAST THEN
3937 level_value_table.DELETE(j);
3938 EXIT;
3939 ELSE
3940 k:= level_value_table.NEXT(k);
3941 level_value_table.DELETE(j);
3942 END IF;
3943 ELSE
3944 exit when k = level_value_table.LAST;
3945 k:= level_value_table.NEXT(k);
3946 END IF;
3947 END LOOP;
3948
3949 END IF;
3950
3951 end delete_table;
3952
3953 procedure get_table_date(p_level in number,
3954 p_start_date out NOCOPY date,
3955 p_end_date out NOCOPY date)
3956 is
3957
3958 k BINARY_INTEGER := 0;
3959
3960 begin
3961 for k in 1..level_value_table.COUNT
3962 loop
3963
3964 if level_value_table(k).level = p_level
3965 then
3966 p_start_date := level_value_table(k).start_date;
3967 p_end_date := level_value_table(k).end_date;
3968 exit;
3969 end if;
3970 end loop;
3971 end get_table_date;
3972
3973 BEGIN
3974
3975 --Standard Start of API SAVEPOINT
3976 SAVEPOINT group_denormalize_no_con;
3977
3978 x_return_status := fnd_api.g_ret_sts_success;
3979
3980 --Standard Call to check API compatibility
3981 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
3982 THEN
3983 RAISE FND_API.G_EXC_ERROR;
3984 END IF;
3985
3986 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
3987 IF FND_API.To_boolean(P_INIT_MSG_LIST)
3988 THEN
3989 FND_MSG_PUB.Initialize;
3990 END IF;
3991
3992 l_date := sysdate;
3993 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
3994 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
3995
3996 -- if no group id is passed in then raise error
3997 IF p_group_id IS NULL
3998 THEN
3999 x_return_status := fnd_api.g_ret_sts_error;
4000 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_IS_NULL');
4001 FND_MSG_PUB.add;
4002 RAISE fnd_api.g_exc_error;
4003 RETURN;
4004 END IF;
4005
4006 l_date := sysdate;
4007 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
4008 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
4009
4010
4011 --fetch the start date and the end date for the group
4012 OPEN c_date(p_group_id);
4013 FETCH c_date INTO l_start_date, l_end_date;
4014 CLOSE c_date;
4015
4016 POPULATE_PARENT_TABLE(p_group_id);
4017 l_parent_tab := g_parent_tab;
4018
4019 FOR I IN 1 .. l_parent_tab.COUNT
4020 LOOP
4021
4022 l_start_date := l_parent_tab(i).p_start_date_active;
4023 l_end_date := l_parent_tab(i).p_end_date_active;
4024 IF (l_parent_tab(i).p_related_group_id IS NOT NULL)
4025 THEN
4026 --if parent group id is null then this group has no upward hierarchy structure, hence no records
4027 --are to be inserted in the denormalized table
4028 IF(l_prev_level >= l_parent_tab(i).level)
4029 THEN
4030 get_table_date(l_parent_tab(i).level - 1, l_start_date_1, l_end_date_1);
4031 delete_table(l_parent_tab(i).level);
4032 END IF; -- end of level check
4033
4034
4035
4036 IF l_parent_tab(i).p_GROUP_ID = P_GROUP_ID
4037 THEN
4038 l_immediate_parent_flag := 'Y';
4039 l_start_date_1 := l_parent_tab(i).p_start_date_active;
4040 l_end_date_1 := l_parent_tab(i).p_end_date_active;
4041
4042 ELSE
4043 l_immediate_parent_flag := 'N';
4044 if((l_start_date_1 < l_start_date)
4045 OR (l_start_date_1 is null))
4046 then
4047 l_start_date_1 := l_start_date;
4048 end if;
4049 if(l_end_date < l_end_date_1)
4050 then
4051 l_end_date_1 := l_end_date;
4052 elsif(l_end_date_1 is null)
4053 then
4054 l_end_date_1 := l_end_date;
4055 end if;
4056
4057 END IF;
4058
4059 if(l_start_date_1 <= nvl(l_end_date_1, l_start_date_1))
4060 then
4061 OPEN c_dup(p_group_id, l_parent_tab(i).p_related_group_id, l_start_date_1, l_end_date_1);
4062
4063 FETCH c_dup into dup;
4064 IF (c_dup%NOTFOUND)
4065 THEN
4066
4067 SELECT jtf_rs_groups_denorm_s.nextval
4068 INTO l_denorm_grp_id
4069 FROM dual;
4070
4071 l_actual_parent_id := getDirectParent(p_group_id,
4072 l_parent_tab(i).level,
4073 l_parent_tab(i).p_related_group_id,
4074 trunc(l_start_date_1),
4075 trunc(l_end_date_1));
4076
4077 jtf_rs_groups_denorm_pkg.insert_row(
4078 X_ROWID => x_row_id,
4079 X_DENORM_GRP_ID => l_DENORM_GRP_ID,
4080 X_GROUP_ID => p_group_id,
4081 X_PARENT_GROUP_ID => l_parent_tab(i).p_related_group_id,
4082 X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
4083 X_ACTUAL_PARENT_ID => l_actual_parent_id,
4084 X_START_DATE_ACTIVE => trunc(l_start_date_1),
4085 X_END_DATE_ACTIVE => trunc(l_end_date_1),
4086 X_ATTRIBUTE2 => null,
4087 X_ATTRIBUTE3 => null,
4088 X_ATTRIBUTE4 => null,
4089 X_ATTRIBUTE5 => null,
4090 X_ATTRIBUTE6 => null,
4091 X_ATTRIBUTE7 => null,
4092 X_ATTRIBUTE8 => null,
4093 X_ATTRIBUTE9 => null,
4094 X_ATTRIBUTE10 => null,
4095 X_ATTRIBUTE11 => null,
4096 X_ATTRIBUTE12 => null,
4097 X_ATTRIBUTE13 => null,
4098 X_ATTRIBUTE14 => null,
4099 X_ATTRIBUTE15 => null,
4100 X_ATTRIBUTE_CATEGORY => null,
4101 X_ATTRIBUTE1 => null,
4102 X_CREATION_DATE => l_date,
4103 X_CREATED_BY => l_user_id,
4104 X_LAST_UPDATE_DATE => l_date,
4105 X_LAST_UPDATED_BY => l_user_id,
4106 X_LAST_UPDATE_LOGIN => l_login_id,
4107 X_DENORM_LEVEL => l_parent_tab(i).level );
4108
4109
4110 --call rep manager insert
4111 JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
4112 P_API_VERSION => 1.0,
4113 P_GROUP_DENORM_ID => l_denorm_grp_id,
4114 P_GROUP_ID => p_group_id ,
4115 P_PARENT_GROUP_ID => l_parent_tab(i).p_related_group_id ,
4116 P_START_DATE_ACTIVE => l_start_date_1 ,
4117 P_END_DATE_ACTIVE => l_end_date_1 ,
4118 P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
4119 P_DENORM_LEVEL => l_parent_tab(i).level,
4120 X_RETURN_STATUS => l_return_status,
4121 X_MSG_COUNT => l_msg_count,
4122 X_MSG_DATA => l_msg_data ) ;
4123
4124 IF(l_return_status <> fnd_api.g_ret_sts_success)
4125 THEN
4126 x_return_status := fnd_api.g_ret_sts_error;
4127 RAISE fnd_api.g_exc_error;
4128 END IF;
4129 END IF;
4130 CLOSE c_dup;
4131 END IF; -- end of st dt check
4132
4133 END IF; --end of group id check
4134 --populating the plsql table
4135 l_prev_level := l_parent_tab(i).level;
4136 populate_table(l_prev_level, l_start_date_1, l_end_date_1);
4137
4138 END LOOP;
4139
4140
4141
4142 IF fnd_api.to_boolean (p_commit)
4143 THEN
4144 COMMIT WORK;
4145 END IF;
4146
4147
4148 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4149
4150 EXCEPTION
4151 WHEN fnd_api.g_exc_unexpected_error
4152 THEN
4153 ROLLBACK TO group_denormalize_no_con;
4154
4155 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
4156 --ND_MSG_PUB.add;
4157 --x_return_status := fnd_api.g_ret_sts_unexp_error;
4158 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4159 WHEN fnd_api.g_exc_error
4160 THEN
4161 ROLLBACK TO group_denormalize_no_con;
4162 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4163
4164 WHEN OTHERS
4165 THEN
4166 ROLLBACK TO group_denormalize_no_con;
4167 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
4168 fnd_message.set_token('P_SQLCODE',SQLCODE);
4169 fnd_message.set_token('P_SQLERRM',SQLERRM);
4170 fnd_message.set_token('P_API_NAME',l_api_name);
4171 FND_MSG_PUB.add;
4172 x_return_status := fnd_api.g_ret_sts_unexp_error;
4173 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4174 END INSERT_GROUPS_PARENT_NO_CON;
4175 ------ NO CONNECT BY - SECTION - Ends
4176
4177 END JTF_RS_GROUP_DENORM_PVT;