[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT A.terr_values_idx_header_id,
A.index_name,
A.qual_usg_id
FROM jty_terr_values_idx_header A
WHERE A.source_id = p_source_id
AND A.build_index_flag = 'Y'
-- the condition below is necessary for incremental mode where the index may be already present
AND NOT EXISTS (
SELECT 1
FROM dba_indexes B
WHERE B.index_name = A.index_name
AND B.owner = l_ora_username)
ORDER BY A.index_name;
SELECT DISTINCT B.VALUES_COL_MAP, B.INPUT_SELECTIVITY
FROM jty_terr_values_idx_details B
WHERE B.terr_values_idx_header_id = cl_tvhidpid
AND B.values_col_map is not null
ORDER BY input_selectivity;
SELECT A.dea_values_idx_header_id,
A.index_name
FROM jty_dea_values_idx_header A
WHERE A.source_id = p_source_id
AND A.build_index_flag = 'Y';
SELECT DISTINCT B.VALUES_COL_MAP, B.INPUT_SELECTIVITY
FROM jty_dea_values_idx_details B
WHERE B.dea_values_idx_header_id = cl_tvhidpid
AND B.values_col_map is not null
ORDER BY input_selectivity;
SELECT A.index_name,
B.owner
FROM jty_terr_values_idx_header A,
dba_indexes B
WHERE A.source_id = p_source_id
AND A.build_index_flag = 'N'
AND A.index_name = B.index_name
AND B.owner = cl_owner;
SELECT A.index_name,
B.owner
FROM jty_dea_values_idx_header A,
dba_indexes B
WHERE A.source_id = p_source_id
AND A.build_index_flag = 'N'
AND A.index_name = B.index_name
AND B.owner = cl_owner;
UPDATE jty_terr_values_idx_header a
SET a.delete_flag = 'N'
WHERE a.source_id = p_source_id
AND a.delete_flag = 'Y'
AND a.relation_product in (
SELECT relation_product
FROM jtf_tae_qual_products
WHERE source_id = p_source_id );
UPDATE jty_dea_values_idx_header a
SET a.delete_flag = 'N'
WHERE a.source_id = p_source_id
AND a.delete_flag = 'Y'
AND a.relation_product in (
SELECT attr_relation_product
FROM jty_dea_attr_products
WHERE source_id = p_source_id );
/* Calculate the selectivity of the columns in the denorm value table */
jty_tae_index_creation_pvt.SELECTIVITY(p_TABLE_NAME => p_table_name,
p_mode => p_mode,
p_source_id => p_source_id,
x_return_status => x_return_status);
'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.selectivity',
'API jty_tae_index_creation_pvt.SELECTIVITY has failed');
SELECT MIN(TO_NUMBER(v.value))
INTO l_dop
FROM v$parameter v
WHERE v.name = 'parallel_max_servers'
OR v.name = 'cpu_count';
SELECT i.tablespace, i.index_tablespace, u.oracle_username
INTO l_table_tablespace, l_idx_tablespace, l_ora_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;
UPDATE jty_terr_values_idx_header
SET BUILD_INDEX_FLAG = 'N'
WHERE terr_values_idx_header_id = prd.terr_values_idx_header_id;
DELETE jty_terr_values_idx_details dtl
WHERE EXISTS (
SELECT 1
FROM jty_terr_values_idx_header hdr
WHERE dtl.terr_values_idx_header_id = hdr.terr_values_idx_header_id
AND hdr.source_id = p_source_id
AND hdr.delete_flag = 'Y');
DELETE jty_terr_values_idx_header hdr
WHERE hdr.source_id = p_source_id
AND hdr.delete_flag = 'Y';
UPDATE jty_dea_values_idx_header
SET BUILD_INDEX_FLAG = 'N'
WHERE dea_values_idx_header_id = prd.dea_values_idx_header_id;
DELETE jty_dea_values_idx_details dtl
WHERE EXISTS (
SELECT 1
FROM jty_dea_values_idx_header hdr
WHERE dtl.dea_values_idx_header_id = hdr.dea_values_idx_header_id
AND hdr.source_id = p_source_id
AND hdr.delete_flag = 'Y');
DELETE jty_dea_values_idx_header hdr
WHERE hdr.source_id = p_source_id
AND hdr.delete_flag = 'Y';
select max(level)
into l_level
from jtf_terr_all
START WITH terr_id = p_terr_id
CONNECT BY PRIOR parent_territory_id = terr_id AND terr_id <> 1;
/* This procedure inserts the denormalized territory hierarchy informations */
/* into the tables jtf_terr_denorm_rules_all, for total and incremental mode */
/* and the table jty_denorm_dea_rules_all for date effective mode */
PROCEDURE update_denorm_table (
p_source_id IN NUMBER,
p_mode IN VARCHAR2,
p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_related_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_top_level_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_num_winners_tbl IN OUT NOCOPY jtf_terr_number_list,
p_level_from_root_tbl IN OUT NOCOPY jtf_terr_number_list,
p_level_from_parent_tbl IN OUT NOCOPY jtf_terr_number_list,
p_terr_rank_tbl IN OUT NOCOPY jtf_terr_number_list,
p_immediate_parent_flag_tbl IN OUT NOCOPY jtf_terr_char_1list,
p_org_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_start_date_tbl IN OUT NOCOPY jtf_terr_date_list,
p_end_date_tbl IN OUT NOCOPY jtf_terr_date_list,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2)
IS
l_no_of_records NUMBER;
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.start',
'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_denorm_table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
/* if mode is total or incremental, insert the denormalized */
/* hierarchy information into jtf_terr_denorm_rules_all */
IF (p_mode IN ('TOTAL', 'INCREMENTAL')) THEN
FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
INSERT INTO jtf_terr_denorm_rules_all(
source_id
, qual_type_id
, terr_id
, rank
, level_from_root
, level_from_parent
, related_terr_id
, top_level_terr_id
, num_winners
, immediate_parent_flag
, start_date
, end_date
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ORG_ID
, RESOURCE_EXISTS_FLAG
-- , absolute_rank
)
VALUES (
p_source_id
, -1
, p_terr_id_tbl(i)
, p_terr_rank_tbl(i)
, p_level_from_root_tbl(i)
, p_level_from_parent_tbl(i)
, p_related_terr_id_tbl(i)
, p_top_level_terr_id_tbl(i)
, p_num_winners_tbl(i)
, p_immediate_parent_flag_tbl(i)
, p_start_date_tbl(i)
, p_end_date_tbl(i)
, G_SYSDATE
, G_USER_ID
, G_SYSDATE
, G_USER_ID
, G_USER_ID
, G_REQUEST_ID
, G_PROGRAM_APPL_ID
, G_PROGRAM_ID
, G_SYSDATE
, p_org_id_tbl(i)
, 'N'
-- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
'Number of records inserted into jtf_terr_denorm_rules_all : ' || l_no_of_records);
/* if mode is date effective incremental , insert the denormalized */
/* hierarchy information into jty_denorm_dea_rules_all */
FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
INSERT INTO jty_denorm_dea_rules_all(
source_id
, terr_id
, rank
, level_from_root
, level_from_parent
, related_terr_id
, top_level_terr_id
, num_winners
, immediate_parent_flag
, start_date
, end_date
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ORG_ID
--, absolute_rank
)
VALUES (
p_source_id
, p_terr_id_tbl(i)
, p_terr_rank_tbl(i)
, p_level_from_root_tbl(i)
, p_level_from_parent_tbl(i)
, p_related_terr_id_tbl(i)
, p_top_level_terr_id_tbl(i)
, p_num_winners_tbl(i)
, p_immediate_parent_flag_tbl(i)
, p_start_date_tbl(i)
, p_end_date_tbl(i)
, G_SYSDATE
, G_USER_ID
, G_SYSDATE
, G_USER_ID
, G_USER_ID
, G_REQUEST_ID
, G_PROGRAM_APPL_ID
, G_PROGRAM_ID
, G_SYSDATE
, p_org_id_tbl(i)
-- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
'Number of records inserted into jty_denorm_dea_rules_all : ' || l_no_of_records);
/* if mode is date effective, insert the denormalized */
/* hierarchy information into jty_denorm_dea_rules_all */
FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
INSERT INTO jty_denorm_dea_rules_all(
source_id
, terr_id
, rank
, level_from_root
, level_from_parent
, related_terr_id
, top_level_terr_id
, num_winners
, immediate_parent_flag
, start_date
, end_date
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ORG_ID
--, absolute_rank
)
VALUES (
p_source_id
, p_terr_id_tbl(i)
, p_terr_rank_tbl(i)
, p_level_from_root_tbl(i)
, p_level_from_parent_tbl(i)
, p_related_terr_id_tbl(i)
, p_top_level_terr_id_tbl(i)
, p_num_winners_tbl(i)
, p_immediate_parent_flag_tbl(i)
, p_start_date_tbl(i)
, p_end_date_tbl(i)
, G_SYSDATE
, G_USER_ID
, G_SYSDATE
, G_USER_ID
, G_USER_ID
, G_REQUEST_ID
, G_PROGRAM_APPL_ID
, G_PROGRAM_ID
, G_SYSDATE
, p_org_id_tbl(i)
-- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
'Number of records inserted into jty_denorm_dea_rules_all : ' || l_no_of_records);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.end',
'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_denorm_table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.others',
substr(errbuf, 1, 4000));
END update_denorm_table;
/* This procedure updates the relative rank in the table jtf_terr_all */
PROCEDURE update_relative_rank (
p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_relative_rank_tbl IN OUT NOCOPY jtf_terr_number_list,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2)
IS
l_no_of_records NUMBER;
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.start',
'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_relative_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
/* update the relative rank of the territory in jtf_terr_all */
IF (l_no_of_records > 0) THEN
/* disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
UPDATE jtf_terr_all
SET relative_rank = p_relative_rank_tbl(i)
WHERE terr_id = p_terr_id_tbl(i);
/* enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.num_rows_updated',
'Number of records updated in jtf_terr_all for relative rank : ' || l_no_of_records);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.end',
'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_relative_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.others',
substr(errbuf, 1, 4000));
END update_relative_rank;
/* This procedure updates the absolute rank in the table jtf_terr_all */
PROCEDURE update_absolute_rank (
p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
p_mode IN VARCHAR2,
p_table_name IN VARCHAR2,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2)
IS
l_dyn_str VARCHAR2(1000);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.start',
'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_absolute_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
/* update the relative rank of the territory in jtf_terr_all */
IF (l_no_of_records > 0) THEN
/* disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
UPDATE jtf_terr_all jta1
SET jta1.ABSOLUTE_RANK = (
SELECT SUM(jta2.relative_rank)
FROM jtf_terr_all jta2
WHERE jta2.terr_id IN (
SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = p_terr_id_tbl(i))),
jta1.last_update_date = g_sysdate
WHERE jta1.terr_id = p_terr_id_tbl(i);
'UPDATE ' || p_table_name || ' ' ||
'SET absolute_rank = ( ' ||
' SELECT absolute_rank ' ||
' FROM jtf_terr_all ' ||
' WHERE terr_id = :1 ) ' ||
'WHERE terr_id = :2 ';
/* enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.num_rows_updated',
'Number of records updated in jtf_terr_all for absolute rank : ' || l_no_of_records);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.end',
'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_absolute_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.others',
substr(errbuf, 1, 4000));
END update_absolute_rank;
/* This procedure updates the denormalized territory qualifier value informations */
PROCEDURE process_attr_values (
p_source_id IN NUMBER,
p_mode IN VARCHAR2,
p_table_name IN VARCHAR2,
p_terr_change_tab IN JTY_TERR_ENGINE_GEN_PVT.terr_change_type,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2 )
IS
CURSOR c_qual_types(cl_source_id in number, cl_terr_id in number) IS
SELECT a.qual_type_id
FROM jtf_qual_type_usgs_all a,
jtf_terr_qtype_usgs_all b
WHERE b.terr_id = cl_terr_id
AND b.qual_type_usg_id = a.qual_type_usg_id
AND a.source_id = cl_source_id;
SELECT jtqa.qual_usg_id,
nvl(jqua.qual_relation_factor, 1),
jtva.comparison_operator,
jtva.low_value_char_id,
decode(cl_source_id, -1001, upper(jtva.low_value_char), -1600, upper(jtva.low_value_char), jtva.low_value_char),
decode(cl_source_id, -1001, upper(jtva.high_value_char), -1600, upper(jtva.high_value_char), jtva.high_value_char),
jtva.low_value_number,
jtva.high_value_number,
jtva.interest_type_id,
jtva.primary_interest_code_id,
jtva.secondary_interest_code_id,
jtva.currency_code,
jtva.value1_id,
jtva.value2_id,
jtva.value3_id,
jtva.value4_id,
jtva.first_char,
jqua.update_attr_val_stmt,
jqua.insert_attr_val_stmt,
jtdr.top_level_terr_id,
jta.absolute_rank,
jtdr.start_date,
jtdr.end_date,
count(*) over(partition by jtqa.qual_usg_id)
FROM jtf_terr_all jta,
jtf_terr_denorm_rules_all jtdr,
jtf_terr_qual_all jtqa,
jtf_terr_values_all jtva,
jtf_qual_usgs_all jqua,
jtf_qual_type_usgs_all jqtu,
jtf_qual_type_denorm_v inlv
WHERE jta.terr_id = cl_terr_id
AND jtdr.terr_id = jta.terr_id
AND jtdr.related_terr_id = jtqa.terr_id
AND jtdr.source_id = cl_source_id
AND jtqa.terr_qual_id = jtva.terr_qual_id
AND jtqa.qual_usg_id = jqua.qual_usg_id
AND jqua.org_id = -3113
AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = cl_source_id
AND jqtu.qual_type_id = inlv.related_id
AND inlv.qual_type_id = cl_qual_type_id
AND jtqa.qual_usg_id <> -1102 -- eliminate CNRG
AND EXISTS
(SELECT 1
FROM jtf_terr_rsc_all jtr,
jtf_terr_rsc_access_all jtra,
jtf_qual_types_all jqta
WHERE jtr.terr_id = jta.terr_id
AND jtr.end_date_active >= sysdate
AND jtr.start_date_active <= sysdate
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jqta.qual_type_id = cl_qual_type_id
AND jtra.trans_access_code <> 'NONE')
UNION ALL
SELECT jtqa.qual_usg_id,
nvl(jqua.qual_relation_factor, 1),
cnrgv.comparison_operator,
null,
upper(cnrgv.low_value_char),
upper(cnrgv.high_value_char),
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
CAST( SUBSTR(UPPER(cnrgv.low_value_char), 1, 1) AS VARCHAR2(3) ),
jqua.update_attr_val_stmt,
jqua.insert_attr_val_stmt,
jtdr.top_level_terr_id,
jta.absolute_rank,
jtdr.start_date,
jtdr.end_date,
count(*) over(partition by jtqa.qual_usg_id)
FROM jtf_terr_all jta,
jtf_terr_denorm_rules_all jtdr,
jtf_terr_qual_all jtqa,
jtf_terr_values_all jtva,
jtf_qual_usgs_all jqua,
jtf_qual_type_usgs_all jqtu,
jtf_qual_type_denorm_v inlv,
jtf_terr_cnr_groups cnrg,
jtf_terr_cnr_group_values cnrgv
WHERE jta.terr_id = cl_terr_id
AND jtdr.terr_id = jta.terr_id
AND jtdr.related_terr_id = jtqa.terr_id
AND jtdr.source_id = cl_source_id
AND jtqa.terr_qual_id = jtva.terr_qual_id
AND jtqa.qual_usg_id = jqua.qual_usg_id
AND jqua.org_id = -3113
AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = cl_source_id
AND jqtu.qual_type_id = inlv.related_id
AND inlv.qual_type_id = cl_qual_type_id
AND jtqa.qual_usg_id = -1102 -- include CNRG
AND cnrg.cnr_group_id = jtva.low_value_char_id
AND cnrg.cnr_group_id = cnrgv.cnr_group_id
AND EXISTS
(SELECT 1
FROM jtf_terr_rsc_all jtr,
jtf_terr_rsc_access_all jtra,
jtf_qual_types_all jqta
WHERE jtr.terr_id = jta.terr_id
AND jtr.end_date_active >= sysdate
AND jtr.start_date_active <= sysdate
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jqta.qual_type_id = cl_qual_type_id
AND jtra.trans_access_code <> 'NONE')
ORDER BY 1;
SELECT /*+ leading(JTA) index(JTA JTF_TERR_U1) */ jtqa.qual_usg_id,
nvl(jqua.qual_relation_factor, 1),
jtva.comparison_operator,
jtva.low_value_char_id,
decode(cl_source_id, -1001, upper(jtva.low_value_char), -1600, upper(jtva.low_value_char), jtva.low_value_char),
decode(cl_source_id, -1001, upper(jtva.high_value_char), -1600, upper(jtva.high_value_char), jtva.high_value_char),
jtva.low_value_number,
jtva.high_value_number,
jtva.interest_type_id,
jtva.primary_interest_code_id,
jtva.secondary_interest_code_id,
jtva.currency_code,
jtva.value1_id,
jtva.value2_id,
jtva.value3_id,
jtva.value4_id,
jtva.first_char,
jqua.update_attr_val_stmt,
jqua.insert_attr_val_stmt,
jtdr.top_level_terr_id,
jta.absolute_rank,
jtdr.start_date,
jtdr.end_date,
count(*) over(partition by jtqa.qual_usg_id)
FROM jtf_terr_all jta,
jty_denorm_dea_rules_all jtdr,
jtf_terr_qual_all jtqa,
jtf_terr_values_all jtva,
jtf_qual_usgs_all jqua,
jtf_qual_type_usgs_all jqtu,
jtf_qual_type_denorm_v inlv
WHERE jta.terr_id = cl_terr_id
AND jtdr.terr_id = jta.terr_id
AND jtdr.related_terr_id = jtqa.terr_id
AND jtqa.terr_qual_id = jtva.terr_qual_id
AND jtqa.qual_usg_id = jqua.qual_usg_id
AND jqua.org_id = -3113
AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = cl_source_id
AND jqtu.qual_type_id = inlv.related_id
AND inlv.qual_type_id = cl_qual_type_id
AND jtqa.qual_usg_id <> -1102 -- eliminate CNRG
AND EXISTS
(SELECT 1
FROM jtf_terr_rsc_all jtr,
jtf_terr_rsc_access_all jtra,
jtf_qual_types_all jqta
WHERE jtr.terr_id = jta.terr_id
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jqta.qual_type_id + 0 = cl_qual_type_id
AND jtra.trans_access_code <> 'NONE')
UNION ALL
SELECT /*+ leading(JTA) index(JTA JTF_TERR_U1) */ jtqa.qual_usg_id,
nvl(jqua.qual_relation_factor, 1),
cnrgv.comparison_operator,
null,
upper(cnrgv.low_value_char),
upper(cnrgv.high_value_char),
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
CAST( SUBSTR(UPPER(cnrgv.low_value_char), 1, 1) AS VARCHAR2(3) ),
jqua.update_attr_val_stmt,
jqua.insert_attr_val_stmt,
jtdr.top_level_terr_id,
jta.absolute_rank,
jtdr.start_date,
jtdr.end_date,
count(*) over(partition by jtqa.qual_usg_id)
FROM jtf_terr_all jta,
jty_denorm_dea_rules_all jtdr,
jtf_terr_qual_all jtqa,
jtf_terr_values_all jtva,
jtf_qual_usgs_all jqua,
jtf_qual_type_usgs_all jqtu,
jtf_qual_type_denorm_v inlv,
jtf_terr_cnr_groups cnrg,
jtf_terr_cnr_group_values cnrgv
WHERE jta.terr_id = cl_terr_id
AND jtdr.terr_id = jta.terr_id
AND jtdr.related_terr_id = jtqa.terr_id
AND jtqa.terr_qual_id = jtva.terr_qual_id
AND jtqa.qual_usg_id = jqua.qual_usg_id
AND jqua.org_id = -3113
AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = cl_source_id
AND jqtu.qual_type_id = inlv.related_id
AND inlv.qual_type_id = cl_qual_type_id
AND jtqa.qual_usg_id = -1102 -- include CNRG
AND cnrg.cnr_group_id = jtva.low_value_char_id
AND cnrg.cnr_group_id = cnrgv.cnr_group_id
AND EXISTS
(SELECT 1
FROM jtf_terr_rsc_all jtr,
jtf_terr_rsc_access_all jtra,
jtf_qual_types_all jqta
WHERE jtr.terr_id = jta.terr_id
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jqta.qual_type_id + 0 = cl_qual_type_id
AND jtra.trans_access_code <> 'NONE')
ORDER BY 1;
SELECT column_name
FROM all_tab_columns
WHERE table_name = p_table_name
AND owner = p_owner
AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'LAST_UPDATE_DATE',
'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'DENORM_TERR_ATTR_VALUES_ID',
'DENORM_TERR_DEA_VALUES_ID');
SELECT COUNT(*)
FROM JTF_TERR_QUAL_ALL jtqa, JTF_TERR_USGS_ALL jtua, JTF_TERR_ALL jta
WHERE jtqa.QUAL_USG_ID NOT IN
(-1040, -1041, -1042, -1044, -1744, -1734, -1048, -1096, -1038, -1061, -1060, -1043,
-1095, -1051, -1050, -1037, -1210, -1045, -1046, -1206, -1213, -1039, -1218)
AND JTQA.TERR_ID = JTUA.TERR_ID
AND JTUA.SOURCE_ID = -1002
AND JTA.TERR_ID = JTUA.TERR_ID
AND SYSDATE BETWEEN JTA.START_DATE_ACTIVE AND JTA.END_DATE_ACTIVE;
TYPE l_update_stmt_tbl_type IS TABLE OF jtf_qual_usgs_all.update_attr_val_stmt%TYPE;
TYPE l_insert_stmt_tbl_type IS TABLE OF jtf_qual_usgs_all.insert_attr_val_stmt%TYPE;
l_update_stmt_tbl l_update_stmt_tbl_type;
l_insert_stmt_tbl l_insert_stmt_tbl_type;
l_delete_stmt VARCHAR2(200);
l_update_stmt VARCHAR2(3000);
l_rowid_update_stmt VARCHAR2(3000);
l_rowid_insert_stmt VARCHAR2(3000);
l_insert_stmt VARCHAR2(10000);
l_select_stmt VARCHAR2(10000);
/* delete the old data from global temp table */
DELETE jty_denorm_terr_attr_values_gt;
/* if mode is incremental, delete the old entries of the territory from the denorm table */
IF (p_mode = 'INCREMENTAL') THEN
l_delete_stmt := 'DELETE ' || l_table_name || ' where terr_id = :1 and :2 IN (''I'', ''D'') ';
execute immediate l_delete_stmt USING p_terr_change_tab.terr_id(i), p_terr_change_tab.attr_processing_flag(i);
l_delete_stmt := 'DELETE ' || l_table_name || ' where terr_id = :1 and :2 IN (''I'', ''D'') ';
execute immediate l_delete_stmt USING p_terr_change_tab.terr_id(i), p_terr_change_tab.attr_processing_flag(i);
,l_update_stmt_tbl
,l_insert_stmt_tbl
,l_top_lvl_terr_id_tbl
,l_abs_rank_tbl
,l_start_date_tbl
,l_end_date_tbl
,l_no_of_val_tbl;
,l_update_stmt_tbl
,l_insert_stmt_tbl
,l_top_lvl_terr_id_tbl
,l_abs_rank_tbl
,l_start_date_tbl
,l_end_date_tbl
,l_no_of_val_tbl;
,l_update_stmt_tbl
,l_insert_stmt_tbl
,l_top_lvl_terr_id_tbl
,l_abs_rank_tbl
,l_start_date_tbl
,l_end_date_tbl
,l_no_of_val_tbl;
/* update the global temp table with the qualifier values, insert if no data found */
IF l_actual_tab_reference > 0 THEN
l_update_stmt := replace(l_update_stmt_tbl(l_terr_qval_counter),
'UPDATE', 'UPDATE /*+ index(JTY_DENORM_TERR_ATTR_VALUES_GT jty_dnm_terr_values_gt_n1) */ ');
EXECUTE IMMEDIATE l_update_stmt USING
l_cop_tbl(l_terr_qval_counter)
,l_lvc_id_tbl(l_terr_qval_counter)
,l_lvc_tbl(l_terr_qval_counter)
,l_hvc_tbl(l_terr_qval_counter)
,l_lvn_tbl(l_terr_qval_counter)
,l_hvn_tbl(l_terr_qval_counter)
,l_it_id_tbl(l_terr_qval_counter)
,l_pic_id_tbl(l_terr_qval_counter)
,l_sic_id_tbl(l_terr_qval_counter)
,l_curr_tbl(l_terr_qval_counter)
,l_value1_id_tbl(l_terr_qval_counter)
,l_value2_id_tbl(l_terr_qval_counter)
,l_value3_id_tbl(l_terr_qval_counter)
,l_value4_id_tbl(l_terr_qval_counter)
,l_fc_tbl(l_terr_qval_counter)
,p_terr_change_tab.terr_id(i)
,p_source_id
,l_qual_type_id_tbl(j);
EXECUTE IMMEDIATE l_insert_stmt_tbl(l_terr_qval_counter) USING
p_terr_change_tab.terr_id(i)
,l_start_date_tbl(l_terr_qval_counter)
,l_end_date_tbl(l_terr_qval_counter)
,p_source_id
,l_qual_type_id_tbl(j)
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,l_abs_rank_tbl(l_terr_qval_counter)
,l_top_lvl_terr_id_tbl(l_terr_qval_counter)
,G_PROGRAM_ID
,G_USER_ID
,G_PROGRAM_APPL_ID
,G_REQUEST_ID
,G_SYSDATE
,l_cop_tbl(l_terr_qval_counter)
,l_lvc_id_tbl(l_terr_qval_counter)
,l_lvc_tbl(l_terr_qval_counter)
,l_hvc_tbl(l_terr_qval_counter)
,l_lvn_tbl(l_terr_qval_counter)
,l_hvn_tbl(l_terr_qval_counter)
,l_it_id_tbl(l_terr_qval_counter)
,l_pic_id_tbl(l_terr_qval_counter)
,l_sic_id_tbl(l_terr_qval_counter)
,l_curr_tbl(l_terr_qval_counter)
,l_value1_id_tbl(l_terr_qval_counter)
,l_value2_id_tbl(l_terr_qval_counter)
,l_value3_id_tbl(l_terr_qval_counter)
,l_value4_id_tbl(l_terr_qval_counter)
,l_fc_tbl(l_terr_qval_counter);
l_rowid_update_stmt := replace(l_update_stmt_tbl(l_terr_qval_counter),
'UPDATE', 'UPDATE /*+ index(JTY_DENORM_TERR_ATTR_VALUES_GT jty_dnm_terr_values_gt_n1) */ ') ||
' returning rowid into :19 ';
/* for the first value, update the existing rows with the qualifier values */
/* if there is no row, insert a row for the qualifier values */
EXECUTE IMMEDIATE l_rowid_update_stmt USING
l_cop_tbl(l_terr_qval_counter)
,l_lvc_id_tbl(l_terr_qval_counter)
,l_lvc_tbl(l_terr_qval_counter)
,l_hvc_tbl(l_terr_qval_counter)
,l_lvn_tbl(l_terr_qval_counter)
,l_hvn_tbl(l_terr_qval_counter)
,l_it_id_tbl(l_terr_qval_counter)
,l_pic_id_tbl(l_terr_qval_counter)
,l_sic_id_tbl(l_terr_qval_counter)
,l_curr_tbl(l_terr_qval_counter)
,l_value1_id_tbl(l_terr_qval_counter)
,l_value2_id_tbl(l_terr_qval_counter)
,l_value3_id_tbl(l_terr_qval_counter)
,l_value4_id_tbl(l_terr_qval_counter)
,l_fc_tbl(l_terr_qval_counter)
,p_terr_change_tab.terr_id(i)
,p_source_id
,l_qual_type_id_tbl(j)
RETURNING BULK COLLECT INTO l_rowid_tbl;
l_rowid_insert_stmt := l_insert_stmt_tbl(l_terr_qval_counter) ||
' returning rowid into :33 ';
EXECUTE IMMEDIATE l_rowid_insert_stmt USING
p_terr_change_tab.terr_id(i)
,l_start_date_tbl(l_terr_qval_counter)
,l_end_date_tbl(l_terr_qval_counter)
,p_source_id
,l_qual_type_id_tbl(j)
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,l_abs_rank_tbl(l_terr_qval_counter)
,l_top_lvl_terr_id_tbl(l_terr_qval_counter)
,G_PROGRAM_ID
,G_USER_ID
,G_PROGRAM_APPL_ID
,G_REQUEST_ID
,G_SYSDATE
,l_cop_tbl(l_terr_qval_counter)
,l_lvc_id_tbl(l_terr_qval_counter)
,l_lvc_tbl(l_terr_qval_counter)
,l_hvc_tbl(l_terr_qval_counter)
,l_lvn_tbl(l_terr_qval_counter)
,l_hvn_tbl(l_terr_qval_counter)
,l_it_id_tbl(l_terr_qval_counter)
,l_pic_id_tbl(l_terr_qval_counter)
,l_sic_id_tbl(l_terr_qval_counter)
,l_curr_tbl(l_terr_qval_counter)
,l_value1_id_tbl(l_terr_qval_counter)
,l_value2_id_tbl(l_terr_qval_counter)
,l_value3_id_tbl(l_terr_qval_counter)
,l_value4_id_tbl(l_terr_qval_counter)
,l_fc_tbl(l_terr_qval_counter)
RETURNING BULK COLLECT INTO l_rowid_tbl;
/* and update the existing rows with the qualifier values */
/* duplicate the existing rows and update with the qualifier values */
IF l_actual_tab_reference > 0 THEN
FORALL l IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
INSERT INTO jty_denorm_terr_attr_values_gt (
SELECT * FROM jty_denorm_terr_attr_values_gt
WHERE rowid = l_rowid_tbl(l));
l_rowid_update_stmt := l_update_stmt_tbl(l_terr_qval_counter) ||
' and rowid = :19 ';
EXECUTE IMMEDIATE l_rowid_update_stmt USING
l_cop_tbl(l_terr_qval_counter)
,l_lvc_id_tbl(l_terr_qval_counter)
,l_lvc_tbl(l_terr_qval_counter)
,l_hvc_tbl(l_terr_qval_counter)
,l_lvn_tbl(l_terr_qval_counter)
,l_hvn_tbl(l_terr_qval_counter)
,l_it_id_tbl(l_terr_qval_counter)
,l_pic_id_tbl(l_terr_qval_counter)
,l_sic_id_tbl(l_terr_qval_counter)
,l_curr_tbl(l_terr_qval_counter)
,l_value1_id_tbl(l_terr_qval_counter)
,l_value2_id_tbl(l_terr_qval_counter)
,l_value3_id_tbl(l_terr_qval_counter)
,l_value4_id_tbl(l_terr_qval_counter)
,l_fc_tbl(l_terr_qval_counter)
,p_terr_change_tab.terr_id(i)
,p_source_id
,l_qual_type_id_tbl(j)
,l_rowid_tbl(l);
/* update num_qual and qual_relation_product if # of rows > g_commit_size to avoid memory overflow */
IF (l_qtype_terr_id_tbl.COUNT >= G_COMMIT_SIZE) THEN
/* disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
/* update num_qual and qual_relation_product */
FORALL l in l_qtype_terr_id_tbl.FIRST .. l_qtype_terr_id_tbl.LAST
UPDATE jtf_terr_qtype_usgs_all
SET num_qual = l_qtype_num_qual_tbl(l),
qual_relation_product = l_qtype_qual_prd_tbl(l)
WHERE terr_id = l_qtype_terr_id_tbl(l)
AND qual_type_usg_id =
(SELECT qual_type_usg_id
FROM jtf_qual_type_usgs_all
WHERE source_id = l_qtype_source_id_tbl(l)
AND qual_type_id = l_qtype_trans_id_tbl(l));
/* enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
l_qtype_terr_id_tbl.DELETE;
l_qtype_trans_id_tbl.DELETE;
l_qtype_source_id_tbl.DELETE;
l_qtype_num_qual_tbl.DELETE;
l_qtype_qual_prd_tbl.DELETE;
/* Form the insert statement to insert the denormalized informations from global temp table to physical table */
l_insert_stmt := 'INSERT INTO ' || l_table_name || ' ( ';
l_select_stmt := '(SELECT ';
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
g_new_line || l_indent || ',CREATION_DATE ' ||
g_new_line || l_indent || ',CREATED_BY ' ||
g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
g_new_line || l_indent || ',REQUEST_ID ' ||
g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
g_new_line || l_indent || ',PROGRAM_ID ' ||
g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ) ';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',:1' ||
g_new_line || l_indent || ',:2' ||
g_new_line || l_indent || ',:3' ||
g_new_line || l_indent || ',:4' ||
g_new_line || l_indent || ',:5' ||
g_new_line || l_indent || ',:6' ||
g_new_line || l_indent || ',:7' ||
g_new_line || l_indent || ',:8' ||
g_new_line || l_indent || ',:9' ||
g_new_line || l_indent || ' FROM jty_denorm_terr_attr_values_gt) ';
'Start Insert into denormalized table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
EXECUTE IMMEDIATE l_insert_stmt || l_select_stmt USING
g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_login_id
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate;
'End Insert into denormalized table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'Number of rows inserted : ' || SQL%ROWCOUNT);
/* analyze the denorm value table to caluclate the selectivity of the columns */
IF (p_mode <> 'INCREMENTAL') THEN
JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
p_table_name => l_table_name
, p_percent => 20
, x_return_status => x_return_status );
/* disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
/* update num_qual and qual_relation_product */
FORALL l in l_qtype_terr_id_tbl.FIRST .. l_qtype_terr_id_tbl.LAST
UPDATE jtf_terr_qtype_usgs_all
SET num_qual = l_qtype_num_qual_tbl(l),
qual_relation_product = l_qtype_qual_prd_tbl(l)
WHERE terr_id = l_qtype_terr_id_tbl(l)
AND qual_type_usg_id =
(SELECT qual_type_usg_id
FROM jtf_qual_type_usgs_all
WHERE source_id = l_qtype_source_id_tbl(l)
AND qual_type_id = l_qtype_trans_id_tbl(l));
/* enable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
l_qtype_terr_id_tbl.DELETE;
l_qtype_trans_id_tbl.DELETE;
l_qtype_source_id_tbl.DELETE;
l_qtype_num_qual_tbl.DELETE;
l_qtype_qual_prd_tbl.DELETE;
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.update_num_qual',
'Done updating jtf_terr_qtype_usgs_all with num_qual and qual_relation_product');
l_rows_inserted1 INTEGER;
l_rows_inserted2 INTEGER;
SELECT qual_type_id
FROM jtf_qual_type_usgs_all
WHERE source_id = cl_source_id
AND qual_type_id <> -1001;
SELECT /*+ ORDERED */ nvl(MAX(j2.rank), 99)
INTO l_max_rank
FROM jtf_qual_type_usgs j1
, jtf_terr_qtype_usgs_all j4
, jtf_terr_all j2
WHERE j2.terr_id <> 1
AND j4.terr_id = j2.terr_id
AND j4.qual_type_usg_id = j1.qual_type_usg_id
AND j1.source_id = p_source_id;
l_rows_inserted1 := 0;
l_rows_inserted2 := 0;
/* if mode is incremental, delete all entries from denorm table for the territory */
IF ((p_terr_change_tab.hier_processing_flag(i) IN ('I', 'D')) AND (p_mode = 'INCREMENTAL')) THEN
DELETE jtf_terr_denorm_rules_all
WHERE terr_id = p_terr_change_tab.terr_id(i);
/* if mode is dea incremental, delete all entries from denorm table for the territory */
IF ((p_terr_change_tab.hier_processing_flag(i) IN ('I', 'D')) AND (p_mode = 'DEA INCREMENTAL')) THEN
DELETE jty_denorm_dea_rules_all
WHERE terr_id = p_terr_change_tab.terr_id(i);
/* if the # of rows that need to updated for relative rank exceeds */
/* g_commit_size, then update the physical table to avoid memory overflow */
IF (l_rows_inserted2 >= G_COMMIT_SIZE) THEN
update_relative_rank (
p_terr_id_tbl => l_terr_id_tbl2,
p_relative_rank_tbl => l_relative_rank_tbl,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_relative_rank',
'update_relative_rank API has failed');
update_absolute_rank (
p_terr_id_tbl => l_terr_id_tbl2,
p_mode => p_mode,
p_table_name => p_table_name,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_absolute_rank',
'update_absolute_rank API has failed');
l_terr_id_tbl2.TRIM(l_rows_inserted2);
l_relative_rank_tbl.TRIM(l_rows_inserted2);
l_rows_inserted2 := 0;
END IF; /* end IF (l_rows_inserted2 >= G_COMMIT_SIZE) */
/* if the # of rows that need to updated for denorm hier table exceeds */
/* g_commit_size, then update the physical table to avoid memory overflow */
IF (l_rows_inserted1 >= G_COMMIT_SIZE) THEN
update_denorm_table (
p_source_id => p_source_id,
p_mode => p_mode,
p_terr_id_tbl => l_terr_id_tbl1,
p_related_terr_id_tbl => l_related_terr_id_tbl,
p_top_level_terr_id_tbl => l_top_level_terr_id_tbl,
p_num_winners_tbl => l_num_winners_tbl,
p_level_from_root_tbl => l_level_from_root_tbl,
p_level_from_parent_tbl => l_level_from_parent_tbl,
p_terr_rank_tbl => l_terr_rank_tbl,
p_immediate_parent_flag_tbl => l_immediate_parent_flag_tbl,
p_org_id_tbl => l_org_id_tbl,
p_start_date_tbl => l_start_date_tbl,
p_end_date_tbl => l_end_date_tbl,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_denorm_table',
'update_denorm_table API has failed');
l_terr_id_tbl1.TRIM(l_rows_inserted1);
l_related_terr_id_tbl.TRIM(l_rows_inserted1);
l_top_level_terr_id_tbl.TRIM(l_rows_inserted1);
l_num_winners_tbl.TRIM(l_rows_inserted1);
l_level_from_root_tbl.TRIM(l_rows_inserted1);
l_level_from_parent_tbl.TRIM(l_rows_inserted1);
l_terr_rank_tbl.TRIM(l_rows_inserted1);
l_immediate_parent_flag_tbl.TRIM(l_rows_inserted1);
l_org_id_tbl.TRIM(l_rows_inserted1);
l_start_date_tbl.TRIM(l_rows_inserted1);
l_end_date_tbl.TRIM(l_rows_inserted1);
l_rows_inserted1 := 0;
l_rows_inserted2 := l_rows_inserted2 + 1;
l_terr_id_tbl2(l_rows_inserted2) := p_terr_change_tab.terr_id(i);
l_relative_rank_tbl(l_rows_inserted2) := 1/(p_terr_change_tab.terr_rank(i) * POWER(l_max_rank, l_level_from_root));
l_rows_inserted1 := l_rows_inserted1 + 1;
/* insert row for itself */
l_terr_id_tbl1.EXTEND;
l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
l_related_terr_id_tbl(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
l_num_winners_tbl(l_rows_inserted1) := 1;
l_num_winners_tbl(l_rows_inserted1) := p_terr_change_tab.num_winners(i);
SELECT jt.terr_id, NVL(jt.num_winners, 1)
INTO l_top_level_terr_id_tbl(l_rows_inserted1), l_num_winners_tbl(l_rows_inserted1)
FROM jtf_terr_all jt
WHERE jt.parent_territory_id = 1
AND (jt.org_id <> -3114 OR jt.org_id IS NULL)
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = p_terr_change_tab.terr_id(i);
l_level_from_parent_tbl(l_rows_inserted1) := 0;
l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root;
l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
l_immediate_parent_flag_tbl(l_rows_inserted1) := 'N';
l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
/* Insert row for immediate parent */
IF (p_terr_change_tab.terr_id(i) <> 1 AND p_terr_change_tab.parent_terr_id(i) <> 1 ) THEN
l_rows_inserted1 := l_rows_inserted1 + 1;
l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
l_related_terr_id_tbl(l_rows_inserted1) := p_terr_change_tab.parent_terr_id(i);
l_num_winners_tbl(l_rows_inserted1) := p_terr_change_tab.parent_num_winners(i);
l_top_level_terr_id_tbl(l_rows_inserted1) := l_top_level_terr_id_tbl(l_rows_inserted1 - 1);
l_level_from_parent_tbl(l_rows_inserted1) := l_level_from_parent_tbl(l_rows_inserted1 - 1) + 1;
l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root_tbl(l_rows_inserted1 - 1) - 1;
l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
l_immediate_parent_flag_tbl(l_rows_inserted1) := 'Y';
l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
/* insert rows for the other parents */
LOOP
SELECT /*+ index(TR1 JTF_TERR_U1) */ DISTINCT TR1.PARENT_TERRITORY_ID, TR2.NUM_WINNERS
INTO l_new_parent_territory_id, l_new_parent_num_winners
FROM jtf_terr_all TR1, jtf_terr_all TR2
WHERE TR2.terr_id = TR1.parent_territory_id
AND TR1.TERR_ID <> 1
AND TR1.TERR_ID = l_parent_terr_id;
l_rows_inserted1 := l_rows_inserted1 + 1;
l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
l_related_terr_id_tbl(l_rows_inserted1) := l_new_parent_territory_id;
l_num_winners_tbl(l_rows_inserted1) := l_new_parent_num_winners;
l_top_level_terr_id_tbl(l_rows_inserted1) := l_top_level_terr_id_tbl(l_rows_inserted1 - 1);
l_level_from_parent_tbl(l_rows_inserted1) := l_level_from_parent_tbl(l_rows_inserted1 - 1) + 1;
l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root_tbl(l_rows_inserted1 - 1) - 1;
l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
l_immediate_parent_flag_tbl(l_rows_inserted1) := 'N';
l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
/* update relative rank */
IF (l_rows_inserted2 > 0) THEN
update_relative_rank (
p_terr_id_tbl => l_terr_id_tbl2,
p_relative_rank_tbl => l_relative_rank_tbl,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_relative_rank',
'update_relative_rank API has failed');
/* update absolute rank */
update_absolute_rank (
p_terr_id_tbl => l_terr_id_tbl2,
p_mode => p_mode,
p_table_name => p_table_name,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_absolute_rank',
'update_absolute_rank API has failed');
l_terr_id_tbl2.TRIM(l_rows_inserted2);
l_relative_rank_tbl.TRIM(l_rows_inserted2);
l_rows_inserted2 := 0;
/* update denorm hier table */
IF (l_rows_inserted1 > 0) THEN
update_denorm_table (
p_source_id => p_source_id,
p_mode => p_mode,
p_terr_id_tbl => l_terr_id_tbl1,
p_related_terr_id_tbl => l_related_terr_id_tbl,
p_top_level_terr_id_tbl => l_top_level_terr_id_tbl,
p_num_winners_tbl => l_num_winners_tbl,
p_level_from_root_tbl => l_level_from_root_tbl,
p_level_from_parent_tbl => l_level_from_parent_tbl,
p_terr_rank_tbl => l_terr_rank_tbl,
p_immediate_parent_flag_tbl => l_immediate_parent_flag_tbl,
p_org_id_tbl => l_org_id_tbl,
p_start_date_tbl => l_start_date_tbl,
p_end_date_tbl => l_end_date_tbl,
errbuf => errbuf,
retcode => retcode);
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_denorm_table',
'update_denorm_table API has failed');
l_terr_id_tbl1.TRIM(l_rows_inserted1);
l_related_terr_id_tbl.TRIM(l_rows_inserted1);
l_top_level_terr_id_tbl.TRIM(l_rows_inserted1);
l_num_winners_tbl.TRIM(l_rows_inserted1);
l_level_from_root_tbl.TRIM(l_rows_inserted1);
l_level_from_parent_tbl.TRIM(l_rows_inserted1);
l_terr_rank_tbl.TRIM(l_rows_inserted1);
l_immediate_parent_flag_tbl.TRIM(l_rows_inserted1);
l_org_id_tbl.TRIM(l_rows_inserted1);
l_start_date_tbl.TRIM(l_rows_inserted1);
l_end_date_tbl.TRIM(l_rows_inserted1);
l_rows_inserted1 := 0;
/* disable the trigger before update */
/* BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
UPDATE jtf_terr_all jta1
SET jta1.ABSOLUTE_RANK = (
SELECT SUM(jta2.relative_rank)
FROM jtf_terr_all jta2
WHERE jta2.terr_id IN (
SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = l_terr_id_tbl2(i))),
jta1.last_update_date = g_sysdate
WHERE jta1.terr_id = l_terr_id_tbl2(i);
'UPDATE ' || p_table_name || ' ' ||
'SET absolute_rank = ( ' ||
' SELECT absolute_rank ' ||
' FROM jtf_terr_all ' ||
' WHERE terr_id = :1 ) ' ||
'WHERE terr_id = :2 ';
l_terr_id_tbl2.TRIM(l_rows_inserted2);*/
/* enable the trigger after update */
/*BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.rows_inserted',
'Finished inserting rows into denorm table and rank calculation');
/* update the first_char column to improve performance of LIKE op */
BEGIN
OPEN c_get_qual_type_id(p_source_id);
UPDATE /*+ INDEX (o jtf_terr_values_n1) */ jtf_terr_values_all o
SET o.first_char = SUBSTR(o.low_value_char, 1, 1)
WHERE o.terr_qual_id IN (
SELECT /*+ INDEX (i2 jtf_qual_usgs_n3) */
i1.terr_qual_id
FROM jtf_terr_qual_all i1, jtf_qual_usgs_all i2, jtf_qual_type_usgs_all i3
WHERE i1.qual_usg_id = i2.qual_usg_id
AND i2.display_type = 'CHAR'
AND i2.lov_sql IS NULL
AND i2.org_id = -3113
AND i2.qual_type_usg_id = i3.qual_type_usg_id
AND i3.source_id = p_source_id
AND i3.qual_type_id in (SELECT related_id
FROM jtf_qual_type_denorm_v
WHERE qual_type_id = l_qual_type_id_tbl(i)));
SELECT aidx.owner, aidx.INDEX_NAME
FROM DBA_INDEXES aidx
WHERE aidx.table_name = cl_table_name
AND aidx.table_owner = cl_owner
AND aidx.index_name like 'JTY_DNM_ATTR_VAL%';
SELECT aidx.owner, aidx.INDEX_NAME
FROM DBA_INDEXES aidx
WHERE aidx.table_name = cl_table_name
AND aidx.table_owner = cl_owner
AND aidx.index_name like 'JTY_DEA_ATTR_VAL%';
SELECT ORACLE_USERNAME
FROM FND_ORACLE_USERID
WHERE ORACLE_ID=b_APP_ID;
SELECT denorm_dea_value_table_name
INTO l_table_name
FROM jtf_sources_all
WHERE source_id = p_source_id;
SELECT denorm_dea_value_table_name
INTO l_table_name
FROM jtf_sources_all
WHERE source_id = p_source_id;
SELECT denorm_value_table_name
INTO l_table_name
FROM jtf_sources_all
WHERE source_id = p_source_id;
/* delete the old records from denormalized tables */
IF (p_mode = 'TOTAL') THEN
DELETE jtf_terr_denorm_rules_all
WHERE source_id = p_source_id;
DELETE jty_terr_values_idx_details dtl
WHERE EXISTS (
SELECT 1
FROM jty_terr_values_idx_header hdr
WHERE dtl.terr_values_idx_header_id = hdr.terr_values_idx_header_id
AND hdr.source_id = p_source_id );
DELETE jty_terr_values_idx_header hdr
WHERE hdr.source_id = p_source_id;
DELETE jty_denorm_dea_rules_all
WHERE source_id = p_source_id;
DELETE jty_dea_values_idx_details dtl
WHERE EXISTS (
SELECT 1
FROM jty_dea_values_idx_header hdr
WHERE dtl.dea_values_idx_header_id = hdr.dea_values_idx_header_id
AND hdr.source_id = p_source_id );
DELETE jty_dea_values_idx_header hdr
WHERE hdr.source_id = p_source_id;
EXECUTE IMMEDIATE 'delete ' || l_table_name || ' where source_id = :1 and (start_date > :2 or end_date < :3) ' USING p_source_id, g_sysdate, g_sysdate;
DELETE jtf_terr_denorm_rules_all
WHERE source_id = p_source_id
AND (start_date > g_sysdate
OR end_date < g_sysdate);
/* mark all the records to be deleted */
/* delete_flag will be updated to 'N' for qualifiers that are used by active territories while generating real time matching sql */
/* delete_flag will be updated to 'N' for qualifier combinations used by active territories after updating jtf_tae_qual_products */
UPDATE jty_terr_values_idx_header
SET delete_flag = 'Y'
WHERE source_id = p_source_id;
SELECT max(start_date) into l_dea_incr_start_date
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = to_char(p_source_id)
AND a.param2 = 'DATE EFFECTIVE'
AND a.retcode = 0 ;
SELECT max(end_date) into l_dea_incr_end_date
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = to_char(p_source_id)
AND a.param2 = 'DATE EFFECTIVE'
AND a.retcode = 0 ;
EXECUTE IMMEDIATE 'delete ' || l_table_name || ' where source_id = :1 and (start_date > :2 or end_date < :3) ' USING p_source_id, l_dea_incr_end_date, l_dea_incr_start_date;
DELETE jty_denorm_dea_rules_all
WHERE source_id = p_source_id
AND (start_date > l_dea_incr_end_date
OR end_date < l_dea_incr_end_date);
/* mark all the records to be deleted */
/* delete_flag will be updated to 'N' for qualifiers that are used by active territories while generating real time matching sql */
/* delete_flag will be updated to 'N' for qualifier combinations used by active territories after updating jtf_tae_qual_products */
UPDATE jty_dea_values_idx_header
SET delete_flag = 'Y'
WHERE source_id = p_source_id;