[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jqua.qual_usg_id, jqua.qual_relation_factor, jqua.org_id
FROM jtf_qual_usgs_all jqua,
jtf_qual_type_usgs jqtu
, jtf_qual_type_denorm_v v
WHERE qual_relation_factor IS NOT NULL
and jqua.org_id = -3113
and jqua.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = cl_source_id ---1001
and jqtu.qual_type_id = v.related_id
AND v.qual_type_id = cl_qual_type_id -- -1002
AND EXISTS ( SELECT iq.qual_usg_id
FROM jtf_qual_usgs_all iq
WHERE enabled_flag = 'Y'
AND iq.qual_usg_id = jqua.qual_usg_id );
/* SELECT jqua.qual_usg_id, jqua.qual_relation_factor, jqua.org_id
FROM jtf_qual_usgs_all jqua,
jtf_qual_type_usgs jqtu
WHERE
jqua.qual_type_usg_id = jqtu.qual_type_usg_id
and qual_relation_factor is not null
and jqua.org_id = -3113
and jqtu.qual_type_id in (SELECT related_id
FROM jtf_qual_type_denorm_v
WHERE qual_type_id = cl_qual_type_id);
SELECT count(*) total_count, jtqu.qual_relation_product
FROM jtf_terr_denorm_rules_all jtdr
,jtf_terr_qtype_usgs_all jtqu
,jtf_qual_type_usgs_all jqtu
WHERE 1=1
--and org_id = -3113
/* JDOCHERT: 07/29/03: BUG# :
** JTF_TAE_QUAL_PRODUCTS NOT BEING CORRECTLY POPULATED SINCE
** POPULATION OF jtdr.resource_exists_flag = 'Y' NOW TAKES
** PLACE AT TOWARDS END OF GTP BEFORE MV REFRESH.
*/
--AND resource_exists_flag = 'Y'
--
AND jtdr.source_id = p_source_id
AND jqtu.source_id = jtdr.source_id
AND jqtu.qual_type_id = cl_qual_type_id
AND jtdr.terr_id = jtqu.terr_id
AND jtdr.terr_id = jtdr.related_terr_id
AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
GROUP BY jtqu.qual_relation_product
ORDER BY total_count DESC;
select distinct qual_usg_id -- distinct not really needed
from jtf_qual_usgs_all jqua
where mod(qual_relation_product, jqua.qual_relation_factor) = 0
and org_id = -3113;
select * from jtf_qual_usgs_all
where qual_usg_id = cl_qual_usg_id
and org_id = -3113;
/* ACHANDA 03/08/04 Bug 3373687 : disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
UPDATE jtf_terr_qtype_usgs_all jtqu
SET jtqu.QUAL_RELATION_PRODUCT = 1
WHERE jtqu.qual_type_usg_id = (
SELECT jqtu.qual_type_usg_id
FROM jtf_qual_type_usgs_all jqtu
WHERE jqtu.source_id = p_source_id
AND jqtu.qual_type_id = p_trans_id
);
/* ACHANDA 03/08/04 Bug 3373687 : enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
--UPDATE jtf_terr_denorm_rules_all jtdr
-- SET jtdr.QUAL_RELATION_PRODUCT = 1
-- WHERE jtdr.terr_id = related_terr_id
-- AND jtdr.source_id = p_source_id
-- AND jtdr.qual_type_id = -1; --p_trans_id;
/* ACHANDA 03/08/04 Bug 3373687 : disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
UPDATE jtf_terr_qtype_usgs_all jtqu
SET jtqu.QUAL_RELATION_PRODUCT =
jtqu.QUAL_RELATION_PRODUCT * qual_rel.qual_relation_factor
WHERE jtqu.terr_id IN
( SELECT ijtdr.terr_id
FROM jtf_terr_denorm_rules_all ijtdr
,jtf_terr_qtype_usgs_all ijtqu
,jtf_qual_type_usgs_all ijqtu
,jtf_terr_qual_all jtq
WHERE ijtdr.source_id = l_source_id
AND ijqtu.source_id = ijtdr.source_id
AND ijqtu.qual_type_id = l_trans_id
AND ijtdr.terr_id = ijtqu.terr_id
AND ijtqu.qual_type_usg_id = ijqtu.qual_type_usg_id
AND ijtdr.related_terr_id = jtq.terr_id
AND jtq.qual_usg_id = qual_rel.qual_usg_id
)
AND jtqu.qual_type_usg_id = (
SELECT jqtu.qual_type_usg_id
FROM jtf_qual_type_usgs_all jqtu
WHERE jqtu.source_id = l_source_id
AND jqtu.qual_type_id = l_trans_id
);
/* ACHANDA 03/08/04 Bug 3373687 : enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
UPDATE jtf_terr_denorm_rules_all jtdr
SET jtdr.QUAL_RELATION_PRODUCT =
jtdr.QUAL_RELATION_PRODUCT * qual_rel.qual_relation_factor
WHERE jtdr.terr_id IN
( SELECT ijtdr.terr_id
FROM jtf_terr_denorm_rules_all ijtdr,
jtf_terr_qual_all jtq
WHERE ijtdr.source_id = l_source_id
and ijtdr.qual_type_id = -1
and ijtdr.related_terr_id = jtq.terr_id
and jtq.qual_usg_id = qual_rel.qual_usg_id )
*/
/* JDOCHERT: 07/29/03: BUG# :
** JTF_TAE_QUAL_PRODUCTS NOT BEING CORRECTLY POPULATED SINCE
** POPULATION OF jtdr.resource_exists_flag = 'Y' NOW TAKES
** PLACE AT TOWARDS END OF GTP BEFORE MV REFRESH.
*/
--AND jtdr.resource_exists_flag = 'Y'
--
/*
and jtdr.related_terr_id = jtdr.terr_id
and jtdr.qual_type_id = -1 --l_trans_id
and jtdr.source_id = l_source_id;
'UPDATE JTF_TERR_DENORM_RULES_ALL.QUAL_RELATION_PRODUCT.';
SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
INTO l_terr_analyze_id
FROM dual;
DELETE FROM jtf_tae_qual_prod_factors
where qual_product_id in
(select qual_product_id from jtf_tae_qual_products
where source_id = l_source_id
and trans_object_type_id = l_trans_id);
delete from JTF_TAE_QUAL_products
where source_id = l_source_id and trans_object_type_id = l_trans_id;
DELETE FROM jtf_tae_qual_factors o
WHERE NOT EXISTS ( SELECT NULL
FROM jtf_tae_qual_products i
WHERE MOD(i.relation_product, o.relation_factor) = 0 );
SELECT JTF_TAE_QUAL_PRODUCTS_S.NEXTVAL
INTO l_qual_product_id
FROM dual;
INSERT INTO JTF_TAE_QUAL_products
( QUAL_PRODUCT_ID,
RELATION_PRODUCT,
SOURCE_ID,
TRANS_OBJECT_TYPE_ID,
INDEX_NAME,
FIRST_CHAR_FLAG,
BUILD_INDEX_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ANALYZE_ID
)
VALUES
( l_qual_product_id, --QUAL_PRODUCT_ID,
rel_set.qual_relation_product, --RELATION_PRODUCT,
l_source_id, --SOURCE_ID,
l_trans_id, --TRANS_OBJECT_TYPE_ID,
'JTF_TAE_TN' || l_char_tx_id || '_DYN_N'|| TO_CHAR(l_counter), --INDEX_NAME,
'N', --FIRST_CHAR,
'Y', --BUILD_INDEX_FLAG,
sysdate, --LAST_UPDATE_DATE,
1, --LAST_UPDATED_BY,
sysdate, --CREATION_DATE,
1, --CREATED_BY,
1, --LAST_UPDATE_LOGIN)
l_terr_analyze_id --TERR_ANALYZE_ID,
);
select count(*) into l_exist_qual_detail_count
from JTF_TAE_QUAL_factors
where qual_usg_id = q_detail.qual_usg_id;
SELECT JTF_TAE_QUAL_factors_s.NEXTVAL
INTO l_qual_factor_id
FROM dual;
INSERT INTO JTF_TAE_QUAL_factors
( QUAL_FACTOR_ID ,
RELATION_FACTOR ,
QUAL_USG_ID ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
TERR_ANALYZE_ID ,
TAE_COL_MAP ,
TAE_REC_MAP ,
USE_TAE_COL_IN_INDEX_FLAG,
UPDATE_SELECTIVITY_FLAG ,
INPUT_SELECTIVITY ,
INPUT_ORDINAL_SELECTIVITY,
INPUT_DEVIATION ,
ORG_ID ,
OBJECT_VERSION_NUMBER
)
VALUES
( l_qual_factor_id, -- QUAL_FACTOR_ID
q_detail.qual_relation_factor, -- RELATION_FACTOR
q_detail.qual_usg_id, -- QUAL_USG_ID
0, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
0, -- CREATED_BY
sysdate, -- CREATION_DATE
0, -- LAST_UPDATE_LOGIN
l_terr_analyze_id, -- TERR_ANALYZE_ID
q_detail.qual_col1, -- TAE_COL_MAP
q_detail.qual_col1_alias, -- TAE_REC_MAP
'Y', -- USE_TAE_COL_IN_INDEX_FLAG
'Y', -- UPDATE_SELECTIVITY_FLAG
null, -- INPUT_SELECTIVITY
null, -- INPUT_ORDINAL_SELECTIVITY
null, -- INPUT_DEVIATION
null, -- ORG_ID
null -- OBJECT_VERSION_NUMBER
);
end loop; -- should only be one record: insert details to qual_factor table
select qual_factor_id into l_qual_factor_id
from JTF_TAE_QUAL_factors
where qual_usg_id = qual_name.qual_usg_id
AND rownum < 2;
SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
INTO l_qual_prod_factor_id
FROM dual;
INSERT INTO JTF_TAE_QUAL_prod_factors
( QUAL_PROD_FACTOR_ID,
QUAL_PRODUCT_ID,
QUAL_FACTOR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ANALYZE_ID,
ORG_ID,
OBJECT_VERSION_NUMBER
)
VALUES
( l_qual_prod_factor_id, --QUAL_PROD_FACTOR_ID,
l_qual_product_id, --QUAL_PRODUCT_ID,
l_qual_factor_id, --QUAL_FACTOR_ID
sysdate, --LAST_UPDATE_DATE,
0, --LAST_UPDATED_BY,
sysdate, --CREATION_DATE,
0, --CREATED_BY,
0, --LAST_UPDATE_LOGIN,
l_terr_analyze_id, --TERR_ANALYZE_ID,
null, --ORG_ID,
null --OBJECT_VERSION_NUMBER
);
select p.trans_object_type_id, count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf,
JTF_TAE_QUAL_factors f
where p.qual_product_id = pf.qual_product_id
and pf.qual_factor_id = f.qual_factor_id
and f.tae_col_map is not null
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_id
group by p.trans_object_type_id, p.qual_product_id, p.relation_product
order by p.relation_product;
/* select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf
where p.qual_product_id = pf.qual_product_id
group by p.qual_product_id, p.relation_product
order by 1;
select * from (
select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf
where p.qual_product_id = pf.qual_product_id
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_id
group by p.qual_product_id, p.relation_product
)
where num_components >= cl_size
and relation_product > cl_relation_product
order by 1 DESC, qual_product_id ASC;
select p.trans_object_type_id, p.qual_product_id, p.relation_product
from JTF_TAE_QUAL_products p
where not exists (select *
from JTF_TAE_QUAL_products ip,
JTF_TAE_QUAL_prod_factors ipf,
JTF_TAE_QUAL_factors ifc
where use_tae_col_in_index_flag = 'Y'
and ip.qual_product_id = ipf.qual_product_id
and ipf.qual_factor_id = ifc.qual_factor_id
and ip.qual_product_id = p.qual_product_id)
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_id;
select COUNT(*) into S_element_ord_subset_L_count
from (
select rownum row_count, tae_col_map, input_selectivity
from (
select distinct p.relation_product, f.tae_col_map, f.input_selectivity
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf,
JTF_TAE_QUAL_factors f
where f.qual_factor_id = pf.qual_factor_id
and pf.qual_product_id = p.qual_product_id
and p.relation_product = cl_set_S.relation_product
and f.tae_col_map is not null
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_id
order by input_selectivity
)
) S,
(
select rownum row_count, tae_col_map, input_selectivity
from (
select distinct p.relation_product, f.tae_col_map, f.input_selectivity
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf,
JTF_TAE_QUAL_factors f
where f.qual_factor_id = pf.qual_factor_id
and pf.qual_product_id = p.qual_product_id
and p.relation_product = cl_set_L.relation_product
and f.tae_col_map is not null
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_id
order by input_selectivity
)
) L
where S.tae_col_map = L.tae_col_map
and S.row_count = L.row_count;
select count(*) into l_first_char_flag_count
from
(select qual_usg_id, tae_col_map, rownum row_count
from ( select f.qual_usg_id, f.relation_factor, f.tae_col_map
from JTF_TAE_QUAL_prod_factors pf,
JTF_TAE_QUAL_factors f
where pf.qual_factor_id = f.qual_factor_id
and pf.qual_product_id = cl_set_S.qual_product_id
order by f.input_selectivity
)
) ilv1,
(select qual_usg_id, 1 row_count
from jtf_qual_usgs_all
where org_id = -3113
and seeded_qual_id = -1012
) ilv2
where ilv1.qual_usg_id = ilv2.qual_usg_id
and ilv1.row_count = ilv2.row_count;
UPDATE JTF_TAE_QUAL_PRODUCTS
SET BUILD_INDEX_FLAG = 'N', FIRST_CHAR_FLAG = l_first_char_flag
WHERE qual_product_id = cl_set_S.qual_product_id
/* JDOCHERT: 10/12/03: INDEX SHOULD ALWAYS BE BUILT
** ON THESE COMBINATIONS: ASSUMES THAT IN PROCEDURE,
** Classify_Territories, THAT JTF_QUAL_PRODUCTS.BUILD_INDEX_FLAG
** IS ALWAYS INITIALIZED TO 'Y'.
*/
AND RELATION_PRODUCT NOT IN (4841, 324347);
UPDATE JTF_TAE_QUAL_PRODUCTS
SET FIRST_CHAR_FLAG = l_first_char_flag
WHERE qual_product_id = cl_set_S.qual_product_id
AND RELATION_PRODUCT IN (4841, 324347);
UPDATE JTF_TAE_QUAL_PRODUCTS
SET BUILD_INDEX_FLAG = 'Y', FIRST_CHAR_FLAG = l_first_char_flag
WHERE qual_product_id = cl_set_S.qual_product_id;
UPDATE JTF_TAE_QUAL_PRODUCTS
SET FIRST_CHAR_FLAG = 'Y'
WHERE qual_product_id = cl_set_S.qual_product_id
AND RELATION_PRODUCT = 353393;
update JTF_TAE_QUAL_PRODUCTS p
set BUILD_INDEX_FLAG = 'N'
where p.qual_product_id = empty_column_index.qual_product_id;
l_selectivity_return_val NUMBER;
update JTF_TAE_QUAL_factors
set UPDATE_SELECTIVITY_FLAG = 'N', USE_TAE_COL_IN_INDEX_FLAG = 'N'
where TAE_COL_MAP is null;
l_selectivity_return_val := jtf_tae_index_creation_pvt.selectivity(l_trans_input_target);
IF l_selectivity_return_val <> 1 THEN
RAISE FND_API.G_EXC_ERROR;