1 PACKAGE BODY jtf_rs_active_rec_conc_pub AS
2 /* $Header: jtfrsbab.pls 120.2.12010000.7 2009/02/06 11:50:08 rgokavar ship $ */
3
4 /****************************************************************************
5 This is a concurrent program to populate ACTIVE_FLAG and LATEST_RELATIONSSHIP_FLAG
6 column in JTF_RS_GROUPS_DENORM and ACTIVE_FLAG in JTF_RS_ROLE_RELATIONS table.
7 This program will be used from concurrrent program "Maintain Current Groups and Roles".
8
9 Create By NSINGHAI 06-MAY-2003
10 Modified By NSINGHAI 19-DEC-2003 Added Latest_relationship_flag update
11 statements for ER # 3013916
12 NSINGHAI 22-MAR-2004 Added new insert statement for self relationships
13 for bug # 3522542
14 NSINGHAI 08-JUL-2004 Removed Histogram creation statements from
15 this file and created seperate file jtfrsc29.sql
16 for histogram creation (Perf Bug 3742472)
17 NSINGHAI 07-DEC-2004 Removed hints from 2 subqueries of Insert stmt (2nd)
18 for performance reasons (Bug 3951752)
19 NSINGHAI 14-JAN-2005 Modifying hints after review of perf team(Bug 3951752)
20 NSINGHAI 20-JAN-2005 Modified exception handling to return ERROR (retcode=2)
21 instead of WARNING (retcode=1) (Bug 4099782)
22 NSINGHAI 29-SEP-2005 Bug 4642145: removing APPEND from 2nd INSERT stmt of
23 JTF_RS_GRP_DEN_LTST_REL_2_TMP table because
24 of ORA-08176 error in GSI env. Doing it after
25 confirming with APPS PERF team (29-SEP-2005)
26 RGOKAVAR 13-AUG-2008 Bug 6800249: Changing Insert statement into
27 jtf_rs_grp_den_ltst_rel_1_tmp Table to Improve
28 Performance.
29 RGOKAVAR 06-FEB-2009 Bug 8220580: Revert the changes in
30 FND_STATS.GATHER_COLUMN_STATS, which are added
31 in Bug7587760.
32
33
34 Usage Note: For table JTF_RS_GROUPS_DENORM, there are 2 columns populated
35 by this concurrent prog. If teams require only active relationships,
36 they should, go against ACTIVE_FLAG = 'Y' check. If teams wants
37 active groups as well as the last active relationship, they should
38 go against LATEST_RELATIONSHIP_FLAG = 'Y'
39 ***************************************************************************/
40
41
42 -- stubbed out procedure because of Bug # 3074562
43 -- new procedure populate_active_flags will do exactly what this was doing
44 PROCEDURE populate_active_flag
45 (ERRBUF OUT NOCOPY VARCHAR2,
46 RETCODE OUT NOCOPY VARCHAR2)
47 IS
48 BEGIN
49 NULL;
50 END;
51
52 -- created on 29-July-2003
53 -- new procedure to do exactly what populate_active_flag was doing
54 -- the concurrant program is JTFRSBAF and the executable is JTFRSBAF
55 PROCEDURE populate_active_flags
56 (ERRBUF OUT NOCOPY VARCHAR2,
57 RETCODE OUT NOCOPY VARCHAR2)
58 IS
59
60 l_sysdate date ;
61 l_update_count number;
62 l_den_update_count number;
63 l_role_update_count number;
64
65 l_jtfu varchar2(60);
66 l_jtfx varchar2(60);
67 l_jtft varchar2(60);
68
69 l_data_to_update VARCHAR2(10);
70
71 CURSOR c_product_info IS
72 SELECT i.tablespace, i.index_tablespace, u.oracle_username
73 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
74 WHERE a.application_short_name = 'JTF'
75 AND a.application_id = i.application_id
76 AND u.oracle_id = i.oracle_id;
77
78 BEGIN
79 -- EXECUTE IMMEDIATE 'alter session set sql_trace=true';
80
81 l_sysdate := trunc(sysdate);
82 l_update_count := 0;
83 l_den_update_count := 0;
84 l_role_update_count := 0;
85
86 retcode := 0;
87
88 --fetch user name for JTF product
89 OPEN c_product_info;
90 FETCH c_product_info INTO l_jtft,l_jtfx,l_jtfu;
91 CLOSE c_product_info;
92
93 -- Following updates are on JTF_RS_GROUPS_DENORM table
94
95 -- Update latest relationship flag to 'Y' in groups denorm
96 -- for all rows which has active_flag = 'Y'
97 -- needed for backward compatibility
98 UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
99 SET gd.latest_relationship_flag = 'Y'
100 WHERE gd.active_flag = 'Y'
101 AND gd.latest_relationship_flag IS NULL
102 ;
103
104 l_den_update_count := SQL%ROWCOUNT ;
105
106 COMMIT;
107
108 -- Update active_flag for new grp relationships in groups denorm
109 -- if a group is active, latest_relationship_flag is active
110 UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
111 SET gd.active_flag = 'Y'
112 ,gd.latest_relationship_flag = 'Y'
113 WHERE l_sysdate BETWEEN gd.start_date_active
114 AND NVL(gd.end_date_active, l_sysdate + 1)
115 AND gd.active_flag IS NULL
116 ;
117
118 l_update_count := SQL%ROWCOUNT ;
119 l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
120
121 COMMIT;
122
123 -- update inactive group relations to null. Also update latest relationsip
124 -- to null. We will make them 'Y' for appropriate groups in next update stmt
125 UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
126 SET gd.active_flag = NULL
127 ,gd.latest_relationship_flag = NULL
128 WHERE l_sysdate NOT BETWEEN gd.start_date_active
129 AND NVL(gd.end_date_active, l_sysdate + 1)
130 AND gd.active_flag = 'Y'
131 ;
132
133 l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
134
135 fnd_message.set_name('JTF', 'JTF_RS_ACTIVE_TO_INACT_COUNT');
136 fnd_message.set_token('P_TABLE_NAME', 'JTF_RS_GROUPS_DENORM');
137 fnd_message.set_token('P_ROWCOUNT', TO_CHAR(SQL%ROWCOUNT));
138 fnd_file.put_line(fnd_file.log, fnd_message.get);
139 fnd_file.new_line(fnd_file.log,1);
140
141 COMMIT;
142
143 -- now update the latest relationship flag for group relations which are inactive
144 -- and do not have any parent group attached to it but in past were a part of
145 -- some group hierarchy.
146
147 -- this query gives all those groups (and their last parent group)
148 -- who do not have active parent groups now. But had it in past.
149
150 -- before that truncate the table since it has unique index
151 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_jtfu||'.JTF_RS_GRP_DEN_LTST_REL_1_TMP';
152 --DELETE FROM JTF_RS_GRP_DEN_LTST_REL_1_TMP;
153
154 -- Insert data into 1st temporary table (session based)
155 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_1_tmp a
156 (child_group_id, parent_group_id, start_date_active, end_date_active)
157 select child_group_id, parent_group_id, start_date_active, end_date_active from (
158 SELECT /*+ parallel(grp1) */
159 grp1.group_id child_group_id, grp1.related_group_id parent_group_id,
160 grp1.start_date_active start_date_active, grp1.end_date_active end_date_active,
161 MAX(end_date_active) OVER (partition by GROUP_ID,DELETE_FLAG) max_end_date
162 FROM jtf_rs_grp_relations grp1
163 WHERE grp1.end_date_active <= l_sysdate -- if it is null it is OK
164 AND grp1.delete_flag = 'N'
165 and grp1.group_id NOT IN (
166 -- check if they have any other parent
167 SELECT /*+ hash_aj parallel(grp2) */ grp2.group_id
168 FROM jtf_rs_grp_relations grp2
169 WHERE NVL(grp2.end_date_active, l_sysdate) >= l_sysdate
170 AND grp2.delete_flag = 'N'
171 )
172 )
173 where end_date_active = max_end_date ;
174
175 COMMIT;
176
177 -- Insert data into 2nd temporary table (session based)
178 -- these are groups which are hanging by themself without any parent.
179 -- so it becomes difficult to rollup their cost. So we need to find
180 -- the last group to which they reported.
181
182 -- before that truncate the table since it has unique index
183 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_jtfu||'.JTF_RS_GRP_DEN_LTST_REL_2_TMP';
184 -- DELETE FROM JTF_RS_GRP_DEN_LTST_REL_2_TMP;
185
186 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
187 (denorm_grp_id)
188 SELECT /*+ use_hash(x den2 den3) PARALLEL(x) PARALLEL(den2) PARALLEL(den3)*/
189 distinct den3.denorm_grp_id
190 FROM
191 jtf_rs_grp_den_ltst_rel_1_tmp x
192 ,jtf_rs_groups_denorm den2 -- fetch all children of x.child_group_id
193 ,jtf_rs_groups_denorm den3 -- fetch all parent of den2.group_id
194 WHERE den2.parent_group_id = x.child_group_id
195 AND (
196 x.start_date_active BETWEEN den2.start_date_active
197 AND NVL(den2.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
198 OR
199 den2.start_date_active BETWEEN x.start_date_active AND x.end_date_active
200 )
201 AND den3.group_id = den2.group_id
202 AND (
203 x.start_date_active BETWEEN den3.start_date_active
204 AND NVL(den3.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
205 OR
206 den3.start_date_active BETWEEN x.start_date_active AND x.end_date_active
207 )
208 AND den3.latest_relationship_flag IS NULL
209 AND NOT EXISTS ( -- check if child groups have any other active parent
210 -- apart from existing active parent in hierarchy in consideration
211 SELECT /*+ use_hash(grp1) PARALLEL(grp1) */ '1'
212 FROM jtf_rs_grp_relations grp1
213 WHERE grp1.start_date_active <= l_sysdate
214 AND NVL(grp1.end_date_active, TO_DATE('12/31/4712','MM/DD/RRRR')) > x.end_date_active
215 AND grp1.delete_flag = 'N'
216 AND grp1.group_id = den3.group_id
217 AND grp1.related_group_id <> den3.actual_parent_id
218 )
219 AND NOT EXISTS ( -- if anywhere on top, same parent is appearing twice, we want only the
220 -- latest path to have latest relationship_flag = 'Y'
221 -- for ex. A->B->C->D, for later date range, if the hierarchy is
222 -- A->B->D, we should not fetch rows A-D for old relation.
223 SELECT /*+ full(den4) parallel(den4) */ '1'
224 FROM jtf_rs_groups_denorm den4
225 WHERE den4.group_id = den3.group_id
226 AND den4.parent_group_id = den3.parent_group_id
227 AND den4.START_DATE_active > den3.start_date_active
228 )
229 AND NOT EXISTS (-- only 1 value should be valid for each denorm level
230 SELECT /*+ full(den5) parallel(den5) */ '1'
231 FROM jtf_rs_groups_denorm den5
232 WHERE den5.group_id = den3.group_id
233 AND den5.denorm_level = den3.denorm_level
234 AND den5.denorm_grp_id <> den3.denorm_grp_id
235 AND den5.start_date_active > den3.start_date_active
236 )
237 ;
238
239 COMMIT;
240
241 -- for self groups, we need to keep latest relationship flag = 'Y', even
242 -- for end dated groups. It is because a group will always have latest relationship
243 -- with itself. This is for bug 3522542. Added by nsinghai on 03/22/2004.
244 -- there is alternate way of directly updating groups denorm table, but performance wise
245 -- it seems doing 1 insert and 1 update is faster than doing 2 updates.
246
247 -- Bug 4642145: removing APPEND from INSERT stmt because of ORA-08176 error in GSI env.
248 -- doing it after confirming with APPS PERF team (29-SEP-2005)
249
250 INSERT /*+ PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
251 (a.denorm_grp_id)
252 SELECT /*+ PARALLEL(den) */
253 den.denorm_grp_id
254 FROM jtf_rs_groups_denorm den
255 WHERE den.denorm_level = 0
256 AND den.latest_relationship_flag IS NULL
257 AND NOT EXISTS (SELECT 1 FROM jtf_rs_grp_den_ltst_rel_2_tmp tmp
258 WHERE tmp.denorm_grp_id = den.denorm_grp_id)
259 ;
260
261 COMMIT;
262
263 -- check if there is any data to be updated
264 BEGIN
265 SELECT 'Y'
266 INTO l_data_to_update
267 FROM jtf_rs_grp_den_ltst_rel_2_tmp
268 WHERE ROWNUM = 1;
269 EXCEPTION
270 WHEN NO_DATA_FOUND
271 THEN NULL;
272 END;
273
274 -- commenting out code on 02/18/2004 because 8i does not support gathering stats
275 -- on Temporary Tables.
276 --FND_STATS.GATHER_TABLE_STATS(ownname=>'JTF'
277 -- ,tabname=>'JTF_RS_GRP_DEN_LTST_REL_2_TMP'
278 -- );
279
280 -- do the real update of table if anything is there to be updated
281 -- have put this check to avoid unnecessary update operation improve performance.
282 IF (l_data_to_update IS NOT NULL) THEN
283
284 UPDATE ( SELECT /*+ PARALLEL(gd) PARALLEL(x) */ gd.latest_relationship_flag
285 FROM jtf_rs_groups_denorm gd, jtf_rs_grp_den_ltst_rel_2_tmp x
286 WHERE x.denorm_grp_id = gd.denorm_grp_id
287 )
288 SET latest_relationship_flag = 'Y'
289 ;
290
291 l_update_count := l_update_count + SQL%ROWCOUNT ;
292 l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
293
294 END IF;
295
296 fnd_message.set_name('JTF', 'JTF_RS_INACT_TO_ACTIVE_COUNT');
297 fnd_message.set_token('P_TABLE_NAME', 'JTF_RS_GROUPS_DENORM');
298 fnd_message.set_token('P_ROWCOUNT', TO_CHAR(l_update_count));
299 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
300 fnd_file.new_line(fnd_file.LOG,1);
301
302 COMMIT;
303
304 ---------------------------------------------------------
305 -- from here it is active_flag update for role_relations.
306 UPDATE jtf_rs_role_relations
307 SET active_flag = NULL
308 WHERE delete_flag = 'Y'
309 AND active_flag = 'Y'
310 ;
311
312 -- l_update_count is reinitialized here with another value
313 l_update_count := SQL%ROWCOUNT ;
314 l_role_update_count := SQL%ROWCOUNT ;
315
316 COMMIT;
317
318 UPDATE jtf_rs_role_relations
319 SET active_flag = NULL
320 WHERE l_sysdate NOT BETWEEN start_date_active
321 AND NVL(end_date_active, l_sysdate + 1)
322 AND delete_flag = 'N'
323 AND active_flag = 'Y'
324 ;
325
326 l_update_count := l_update_count + SQL%ROWCOUNT ;
327 l_role_update_count := l_role_update_count + SQL%ROWCOUNT ;
328
329 fnd_message.set_name('JTF', 'JTF_RS_ACTIVE_TO_INACT_COUNT');
330 fnd_message.set_token('P_TABLE_NAME', 'JTF_RS_ROLE_RELATIONS');
331 fnd_message.set_token('P_ROWCOUNT', to_char(l_update_count));
332 fnd_file.put_line(fnd_file.log, fnd_message.get);
333 fnd_file.new_line(fnd_file.log,1);
334
335 COMMIT;
336
337 UPDATE jtf_rs_role_relations
338 SET active_flag = 'Y'
339 WHERE l_sysdate BETWEEN start_date_active
340 AND NVL(end_date_active, l_sysdate + 1)
341 AND delete_flag = 'N'
342 AND active_flag IS NULL
343 ;
344
345 l_role_update_count := l_role_update_count + SQL%ROWCOUNT ;
346
347 fnd_message.set_name('JTF', 'JTF_RS_INACT_TO_ACTIVE_COUNT');
348 fnd_message.set_token('P_TABLE_NAME', 'JTF_RS_ROLE_RELATIONS');
349 fnd_message.set_token('P_ROWCOUNT', to_char(SQL%ROWCOUNT));
350 fnd_file.put_line(fnd_file.log, fnd_message.get);
351 fnd_file.new_line(fnd_file.log,1);
352
353 COMMIT;
354
355 /* Following lines are add for the performance bug # 3119586 */
356 /* This is for Histogram stats updation */
357
358 /* Commented out stats gathering on TABLE on 28-MAR-2005 for Bug # 4253821
359 Instead of gathering stats on tables, will do it directly for column,
360 so that it performs faster, that too only if columns are updated.
361 As per perf team guidelines "percent" should be 10% */
362
363 --FND_STATS.GATHER_TABLE_STATS(ownname=> l_jtfu
364 -- ,tabname=>'JTF_RS_GROUPS_DENORM'
365 -- );
366
367 --FND_STATS.GATHER_TABLE_STATS(ownname=> l_jtfu
368 -- ,tabname=>'JTF_RS_ROLE_RELATIONS'
369 -- );
370
371 IF (l_den_update_count > 0) THEN
372
373 /* FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
374 tabname => 'JTF_RS_GROUPS_DENORM',
375 colname => 'LATEST_RELATIONSHIP_FLAG',
376 percent => 10,
377 degree => null,
378 hsize => null,
379 backup_flag => null,
380 partname => null,
381 hmode => 'FULL'
382 );
383 */
384 FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
385 tabname => 'JTF_RS_GROUPS_DENORM',
386 colname => 'LATEST_RELATIONSHIP_FLAG',
387 percent => 10
388 );
389 END IF;
390
391 IF (l_role_update_count > 0) THEN
392 /* FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
393 tabname => 'JTF_RS_ROLE_RELATIONS',
394 colname => 'ACTIVE_FLAG',
395 percent => 10,
396 degree => null,
397 hsize => null,
398 backup_flag => null,
399 partname => null,
400 hmode => 'FULL'
401 );
402 */
403 FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
404 tabname => 'JTF_RS_ROLE_RELATIONS',
405 colname => 'ACTIVE_FLAG',
406 percent => 10
407 );
408 END IF;
409
410 --EXECUTE IMMEDIATE 'alter session set sql_trace=false';
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 ROLLBACK;
415 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
416 -- Even though Error return warning using retcode = 1
417 -- because we want other programs in request set to continue.
418 --retcode := 1;
419 -- Changed on 20-Jan-2005 for Bug 4099782 to return Error instead of Warning
420 retcode := 2;
421 errbuf := sqlerrm;
422 --dbms_output.put_line('Error : '||sqlcode||':'||sqlerrm);
423
424 END populate_active_flags;
425 END jtf_rs_active_rec_conc_pub ; -- end package body
[Home] [Help]