[Home] [Help]
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
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: ;
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'
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:
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'
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:
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:
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,
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;