[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Modified By NSINGHAI 19-DEC-2003 Added Latest_relationship_flag update
statements for ER # 3013916
NSINGHAI 22-MAR-2004 Added new insert statement for self relationships
for bug # 3522542
NSINGHAI 08-JUL-2004 Removed Histogram creation statements from
this file and created seperate file jtfrsc29.sql
for histogram creation (Perf Bug 3742472)
NSINGHAI 07-DEC-2004 Removed hints from 2 subqueries of Insert stmt (2nd)
for performance reasons (Bug 3951752)
NSINGHAI 14-JAN-2005 Modifying hints after review of perf team(Bug 3951752)
NSINGHAI 20-JAN-2005 Modified exception handling to return ERROR (retcode=2)
instead of WARNING (retcode=1) (Bug 4099782)
NSINGHAI 29-SEP-2005 Bug 4642145: removing APPEND from 2nd INSERT stmt of
JTF_RS_GRP_DEN_LTST_REL_2_TMP table because
of ORA-08176 error in GSI env. Doing it after
confirming with APPS PERF team (29-SEP-2005)
RGOKAVAR 13-AUG-2008 Bug 6800249: Changing Insert statement into
jtf_rs_grp_den_ltst_rel_1_tmp Table to Improve
Performance.
RGOKAVAR 06-FEB-2009 Bug 8220580: Revert the changes in
FND_STATS.GATHER_COLUMN_STATS, which are added
in Bug7587760.
Usage Note: For table JTF_RS_GROUPS_DENORM, there are 2 columns populated
by this concurrent prog. If teams require only active relationships,
they should, go against ACTIVE_FLAG = 'Y' check. If teams wants
active groups as well as the last active relationship, they should
go against LATEST_RELATIONSHIP_FLAG = 'Y'
***************************************************************************/
-- stubbed out procedure because of Bug # 3074562
-- new procedure populate_active_flags will do exactly what this was doing
PROCEDURE populate_active_flag
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
BEGIN
NULL;
l_update_count number;
l_den_update_count number;
l_role_update_count number;
l_data_to_update VARCHAR2(10);
SELECT i.tablespace, i.index_tablespace, u.oracle_username
FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
WHERE a.application_short_name = 'JTF'
AND a.application_id = i.application_id
AND u.oracle_id = i.oracle_id;
l_update_count := 0;
l_den_update_count := 0;
l_role_update_count := 0;
UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
SET gd.latest_relationship_flag = 'Y'
WHERE gd.active_flag = 'Y'
AND gd.latest_relationship_flag IS NULL
;
l_den_update_count := SQL%ROWCOUNT ;
UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
SET gd.active_flag = 'Y'
,gd.latest_relationship_flag = 'Y'
WHERE l_sysdate BETWEEN gd.start_date_active
AND NVL(gd.end_date_active, l_sysdate + 1)
AND gd.active_flag IS NULL
;
l_update_count := SQL%ROWCOUNT ;
l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
UPDATE /*+ PARALLEL(gd) */ jtf_rs_groups_denorm gd
SET gd.active_flag = NULL
,gd.latest_relationship_flag = NULL
WHERE l_sysdate NOT BETWEEN gd.start_date_active
AND NVL(gd.end_date_active, l_sysdate + 1)
AND gd.active_flag = 'Y'
;
l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_1_tmp a
(child_group_id, parent_group_id, start_date_active, end_date_active)
select child_group_id, parent_group_id, start_date_active, end_date_active from (
SELECT /*+ parallel(grp1) */
grp1.group_id child_group_id, grp1.related_group_id parent_group_id,
grp1.start_date_active start_date_active, grp1.end_date_active end_date_active,
MAX(end_date_active) OVER (partition by GROUP_ID,DELETE_FLAG) max_end_date
FROM jtf_rs_grp_relations grp1
WHERE grp1.end_date_active <= l_sysdate -- if it is null it is OK
AND grp1.delete_flag = 'N'
and grp1.group_id NOT IN (
-- check if they have any other parent
SELECT /*+ hash_aj parallel(grp2) */ grp2.group_id
FROM jtf_rs_grp_relations grp2
WHERE NVL(grp2.end_date_active, l_sysdate) >= l_sysdate
AND grp2.delete_flag = 'N'
)
)
where end_date_active = max_end_date ;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
(denorm_grp_id)
SELECT /*+ use_hash(x den2 den3) PARALLEL(x) PARALLEL(den2) PARALLEL(den3)*/
distinct den3.denorm_grp_id
FROM
jtf_rs_grp_den_ltst_rel_1_tmp x
,jtf_rs_groups_denorm den2 -- fetch all children of x.child_group_id
,jtf_rs_groups_denorm den3 -- fetch all parent of den2.group_id
WHERE den2.parent_group_id = x.child_group_id
AND (
x.start_date_active BETWEEN den2.start_date_active
AND NVL(den2.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
OR
den2.start_date_active BETWEEN x.start_date_active AND x.end_date_active
)
AND den3.group_id = den2.group_id
AND (
x.start_date_active BETWEEN den3.start_date_active
AND NVL(den3.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
OR
den3.start_date_active BETWEEN x.start_date_active AND x.end_date_active
)
AND den3.latest_relationship_flag IS NULL
AND NOT EXISTS ( -- check if child groups have any other active parent
-- apart from existing active parent in hierarchy in consideration
SELECT /*+ use_hash(grp1) PARALLEL(grp1) */ '1'
FROM jtf_rs_grp_relations grp1
WHERE grp1.start_date_active <= l_sysdate
AND NVL(grp1.end_date_active, TO_DATE('12/31/4712','MM/DD/RRRR')) > x.end_date_active
AND grp1.delete_flag = 'N'
AND grp1.group_id = den3.group_id
AND grp1.related_group_id <> den3.actual_parent_id
)
AND NOT EXISTS ( -- if anywhere on top, same parent is appearing twice, we want only the
-- latest path to have latest relationship_flag = 'Y'
-- for ex. A->B->C->D, for later date range, if the hierarchy is
-- A->B->D, we should not fetch rows A-D for old relation.
SELECT /*+ full(den4) parallel(den4) */ '1'
FROM jtf_rs_groups_denorm den4
WHERE den4.group_id = den3.group_id
AND den4.parent_group_id = den3.parent_group_id
AND den4.START_DATE_active > den3.start_date_active
)
AND NOT EXISTS (-- only 1 value should be valid for each denorm level
SELECT /*+ full(den5) parallel(den5) */ '1'
FROM jtf_rs_groups_denorm den5
WHERE den5.group_id = den3.group_id
AND den5.denorm_level = den3.denorm_level
AND den5.denorm_grp_id <> den3.denorm_grp_id
AND den5.start_date_active > den3.start_date_active
)
;
INSERT /*+ PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
(a.denorm_grp_id)
SELECT /*+ PARALLEL(den) */
den.denorm_grp_id
FROM jtf_rs_groups_denorm den
WHERE den.denorm_level = 0
AND den.latest_relationship_flag IS NULL
AND NOT EXISTS (SELECT 1 FROM jtf_rs_grp_den_ltst_rel_2_tmp tmp
WHERE tmp.denorm_grp_id = den.denorm_grp_id)
;
SELECT 'Y'
INTO l_data_to_update
FROM jtf_rs_grp_den_ltst_rel_2_tmp
WHERE ROWNUM = 1;
IF (l_data_to_update IS NOT NULL) THEN
UPDATE ( SELECT /*+ PARALLEL(gd) PARALLEL(x) */ gd.latest_relationship_flag
FROM jtf_rs_groups_denorm gd, jtf_rs_grp_den_ltst_rel_2_tmp x
WHERE x.denorm_grp_id = gd.denorm_grp_id
)
SET latest_relationship_flag = 'Y'
;
l_update_count := l_update_count + SQL%ROWCOUNT ;
l_den_update_count := l_den_update_count + SQL%ROWCOUNT ;
fnd_message.set_token('P_ROWCOUNT', TO_CHAR(l_update_count));
UPDATE jtf_rs_role_relations
SET active_flag = NULL
WHERE delete_flag = 'Y'
AND active_flag = 'Y'
;
l_update_count := SQL%ROWCOUNT ;
l_role_update_count := SQL%ROWCOUNT ;
UPDATE jtf_rs_role_relations
SET active_flag = NULL
WHERE l_sysdate NOT BETWEEN start_date_active
AND NVL(end_date_active, l_sysdate + 1)
AND delete_flag = 'N'
AND active_flag = 'Y'
;
l_update_count := l_update_count + SQL%ROWCOUNT ;
l_role_update_count := l_role_update_count + SQL%ROWCOUNT ;
fnd_message.set_token('P_ROWCOUNT', to_char(l_update_count));
UPDATE jtf_rs_role_relations
SET active_flag = 'Y'
WHERE l_sysdate BETWEEN start_date_active
AND NVL(end_date_active, l_sysdate + 1)
AND delete_flag = 'N'
AND active_flag IS NULL
;
l_role_update_count := l_role_update_count + SQL%ROWCOUNT ;
so that it performs faster, that too only if columns are updated.
As per perf team guidelines "percent" should be 10% */
--FND_STATS.GATHER_TABLE_STATS(ownname=> l_jtfu
-- ,tabname=>'JTF_RS_GROUPS_DENORM'
-- );
IF (l_den_update_count > 0) THEN
/* FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
tabname => 'JTF_RS_GROUPS_DENORM',
colname => 'LATEST_RELATIONSHIP_FLAG',
percent => 10,
degree => null,
hsize => null,
backup_flag => null,
partname => null,
hmode => 'FULL'
);
IF (l_role_update_count > 0) THEN
/* FND_STATS.GATHER_COLUMN_STATS(ownname => l_jtfu,
tabname => 'JTF_RS_ROLE_RELATIONS',
colname => 'ACTIVE_FLAG',
percent => 10,
degree => null,
hsize => null,
backup_flag => null,
partname => null,
hmode => 'FULL'
);