[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_combinations
( p_source_id IN NUMBER,
p_trans_id IN NUMBER,
p_mode IN VARCHAR2,
x_Return_Status OUT NOCOPY VARCHAR2,
x_Msg_Count OUT NOCOPY NUMBER,
x_Msg_Data OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2 )
IS
TYPE l_qual_prd_id_tbl_type IS TABLE OF jtf_tae_qual_products.qual_product_id%TYPE;
'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.begin',
'Start of the procedure JTY_TAE_CONTROL_PVT.delete_combinations ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
DELETE FROM jtf_tae_qual_prod_factors
WHERE qual_product_id IN
( SELECT qual_product_id
FROM jtf_tae_qual_products
WHERE source_id = p_source_id
AND trans_object_type_id = p_trans_id);
DELETE FROM jtf_tae_qual_products
WHERE source_id = p_source_id
AND trans_object_type_id = p_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 );
DELETE FROM jty_tae_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND keep_flag <> 'Y';
DELETE FROM jty_dea_attr_prod_factors
WHERE dea_attr_products_id IN
( SELECT dea_attr_products_id
FROM jty_dea_attr_products
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id);
DELETE FROM jty_dea_attr_products
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id;
DELETE FROM jty_dea_attr_factors o
WHERE NOT EXISTS
( SELECT NULL
FROM jty_dea_attr_products i
WHERE MOD(i.attr_relation_product, o.relation_factor) = 0 );
DELETE FROM jty_dea_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND keep_flag <> 'Y';
DELETE FROM jtf_tae_qual_products
WHERE source_id = p_source_id
AND trans_object_type_id = p_trans_id
AND relation_product not in (
SELECT qual_relation_product
FROM jtf_terr_qtype_usgs_all a,
jtf_qual_type_usgs_all b
WHERE a.qual_type_usg_id = b.qual_type_usg_id
AND b.source_id = p_source_id
AND b.qual_type_id = p_trans_id)
RETURNING qual_product_id, relation_product BULK COLLECT INTO l_qual_prd_id_tbl, l_rel_prd_tbl;
DELETE FROM jtf_tae_qual_prod_factors
WHERE qual_product_id = l_qual_prd_id_tbl(i);
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 );
DELETE FROM jty_tae_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND attr_relation_product = l_rel_prd_tbl(i)
AND keep_flag <> 'Y';
DELETE FROM jty_dea_attr_products
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND attr_relation_product not in (
SELECT qual_relation_product
FROM jtf_terr_qtype_usgs_all a,
jtf_qual_type_usgs_all b
WHERE a.qual_type_usg_id = b.qual_type_usg_id
AND b.source_id = p_source_id
AND b.qual_type_id = p_trans_id)
RETURNING dea_attr_products_id, attr_relation_product BULK COLLECT INTO l_qual_prd_id_tbl, l_rel_prd_tbl;
DELETE FROM jty_dea_attr_prod_factors
WHERE dea_attr_products_id = l_qual_prd_id_tbl(i);
DELETE FROM jty_dea_attr_factors o
WHERE NOT EXISTS
( SELECT NULL
FROM jty_dea_attr_products i
WHERE MOD(i.attr_relation_product, o.relation_factor) = 0 );
DELETE FROM jty_dea_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND attr_relation_product = l_rel_prd_tbl(i)
AND keep_flag <> 'Y';
'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.end',
'End of the procedure JTY_TAE_CONTROL_PVT.delete_combinations ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'jtf.plsql.jtf_tae_cpntrol_pvt.delete_combinations.g_exc_unexpected_error',
x_msg_data);
'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.other',
substr(x_msg_data, 1, 4000));
END delete_combinations;
select qual_usg_id
from jtf_qual_usgs_all jqua
where mod(cl_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;
SELECT qual_type_usg_id
INTO l_qual_type_usg_id
FROM jtf_qual_type_usgs_all
WHERE source_id = p_source_id
AND qual_type_id = p_trans_id;
SELECT max(to_number(substr(index_name, instr(index_name, '_N') +2)))
INTO l_counter
FROM jtf_tae_qual_products
WHERE source_id = p_source_id
AND trans_object_type_id = p_trans_id;
SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
INTO l_terr_analyze_id
FROM dual;
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,
p_qual_prd_tbl(i), --RELATION_PRODUCT,
p_source_id, --SOURCE_ID,
p_trans_id, --TRANS_OBJECT_TYPE_ID,
'JTF_TAE_TN' || TO_CHAR(ABS(l_qual_type_usg_id)) || '_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_no_of_records
FROM jty_terr_values_idx_header
WHERE source_id = p_source_id
AND relation_product = p_qual_prd_tbl(i);
SELECT jty_terr_values_idx_header_s.nextval
INTO l_header_seq
FROM dual;
SELECT 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN' ||
(nvl(max(to_number(substr(index_name, instr(index_name, '_BN')+3))), 0) + 1)
INTO l_index_name
FROM jty_terr_values_idx_header
WHERE index_name like 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN%';
INSERT INTO jty_terr_values_idx_header (
terr_values_idx_header_id
,source_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,relation_product
,index_name
,build_index_flag
,delete_flag )
VALUES (
l_header_seq
,p_source_id
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,p_qual_prd_tbl(i)
,l_index_name
,'Y'
,'N');
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
);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.comparison_operator
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_char_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_char
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.high_value_char
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_number
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.high_value_number
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.interest_type_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.primary_interest_code_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.secondary_interest_code_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.currency_code
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value1_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value2_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value3_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value4_id
,null
,null);
INSERT INTO jty_terr_values_idx_details (
terr_values_idx_details_id
,terr_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_terr_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.first_char
,null
,null);
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
);
update JTF_TAE_QUAL_factors
set UPDATE_SELECTIVITY_FLAG = 'N',
USE_TAE_COL_IN_INDEX_FLAG = 'N'
where TAE_COL_MAP is null;
select qual_usg_id
from jtf_qual_usgs_all jqua
where mod(cl_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;
SELECT qual_type_usg_id
INTO l_qual_type_usg_id
FROM jtf_qual_type_usgs_all
WHERE source_id = p_source_id
AND qual_type_id = p_trans_id;
SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
INTO l_terr_analyze_id
FROM dual;
SELECT JTY_DEA_ATTR_PRODUCTS_S.NEXTVAL
INTO l_qual_product_id
FROM dual;
INSERT INTO JTY_DEA_ATTR_PRODUCTS
( DEA_ATTR_PRODUCTS_ID,
ATTR_RELATION_PRODUCT,
SOURCE_ID,
TRANS_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,
p_qual_prd_tbl(i), --RELATION_PRODUCT,
p_source_id, --SOURCE_ID,
p_trans_id, --TRANS_OBJECT_TYPE_ID,
'JTF_TAE_DE' || TO_CHAR(ABS(l_qual_type_usg_id)) || '_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_no_of_records
FROM jty_dea_values_idx_header
WHERE source_id = p_source_id
AND relation_product = p_qual_prd_tbl(i);
SELECT jty_dea_values_idx_header_s.nextval
INTO l_header_seq
FROM dual;
INSERT INTO jty_dea_values_idx_header (
dea_values_idx_header_id
,source_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,relation_product
,index_name
,build_index_flag
,delete_flag)
VALUES (
l_header_seq
,p_source_id
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,p_qual_prd_tbl(i)
,'JTY_DEA_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN' || i
,'Y'
,'N');
SELECT count(*)
INTO l_exist_qual_detail_count
FROM jty_dea_attr_factors
WHERE qual_usg_id = q_detail.qual_usg_id;
SELECT JTY_DEA_ATTR_FACTORS_S.NEXTVAL
INTO l_qual_factor_id
FROM dual;
INSERT INTO JTY_DEA_ATTR_FACTORS
( DEA_ATTR_FACTORS_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,
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 -- OBJECT_VERSION_NUMBER
);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.comparison_operator
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_char_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_char
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.high_value_char
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.low_value_number
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.high_value_number
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.interest_type_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.primary_interest_code_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.secondary_interest_code_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.currency_code
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value1_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value2_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value3_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.value4_id
,null
,null);
INSERT INTO jty_dea_values_idx_details (
dea_values_idx_details_id
,dea_values_idx_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,values_col_map
,input_selectivity
,input_ordinal_selectivity )
VALUES (
jty_dea_values_idx_details_s.nextval
,l_header_seq
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_USER_ID
,q_detail.first_char
,null
,null);
SELECT dea_attr_factors_id
INTO l_qual_factor_id
FROM jty_dea_attr_factors
WHERE qual_usg_id = qual_name.qual_usg_id
AND rownum < 2;
SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
INTO l_qual_prod_factor_id
FROM dual;
INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
( DEA_ATTR_PROD_FACTORS_ID,
DEA_ATTR_PRODUCTS_ID,
DEA_ATTR_FACTORS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ANALYZE_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 --OBJECT_VERSION_NUMBER
);
update JTY_DEA_ATTR_FACTORS
set UPDATE_SELECTIVITY_FLAG = 'N',
USE_TAE_COL_IN_INDEX_FLAG = 'N'
where TAE_COL_MAP is null;
SELECT A.dea_values_idx_header_id, count(*) num_components
FROM jty_dea_values_idx_header A,
jty_dea_values_idx_details B
WHERE A.source_id = cl_source_id
AND A.dea_values_idx_header_id = B.dea_values_idx_header_id
AND B.values_col_map is not null
GROUP BY A.dea_values_idx_header_id
ORDER BY 2;
SELECT * FROM (
SELECT A.dea_values_idx_header_id, count(*) num_components
FROM jty_dea_values_idx_header A,
jty_dea_values_idx_details B
WHERE A.source_id = cl_source_id
AND A.dea_values_idx_header_id <> cl_dea_values_idx_header_id
AND A.dea_values_idx_header_id = B.dea_values_idx_header_id
AND B.values_col_map is not null
AND A.build_index_flag = 'Y'
GROUP BY A.dea_values_idx_header_id )
WHERE num_components >= cl_size
ORDER BY 2 DESC;
/* that have beeen marked deleted in incremental mode */
IF (p_mode = 'DEA INCREMENTAL') THEN
UPDATE jty_dea_values_idx_header
SET delete_flag = 'N'
WHERE source_id = p_source_id
AND delete_flag = 'Y';
SELECT COUNT(*)
INTO S_element_ord_subset_L_count
FROM (
SELECT rownum row_count, values_col_map, input_selectivity
FROM (
SELECT B.values_col_map, B.input_selectivity
FROM jty_dea_values_idx_details B
WHERE B.dea_values_idx_header_id = cl_set_S.dea_values_idx_header_id
AND B.values_col_map IS NOT NULL
ORDER BY B.input_selectivity )) S,
(
SELECT rownum row_count, values_col_map, input_selectivity
FROM (
SELECT B.values_col_map, B.input_selectivity
FROM jty_dea_values_idx_details B
WHERE B.dea_values_idx_header_id = cl_set_L.dea_values_idx_header_id
AND B.values_col_map IS NOT NULL
ORDER BY B.input_selectivity )) L
WHERE S.values_col_map = L.values_col_map
AND S.row_count = L.row_count;
UPDATE jty_dea_values_idx_header
SET BUILD_INDEX_FLAG = 'N'
WHERE dea_values_idx_header_id = cl_set_S.dea_values_idx_header_id;
SELECT A.terr_values_idx_header_id, count(*) num_components
FROM jty_terr_values_idx_header A,
jty_terr_values_idx_details B
WHERE A.source_id = cl_source_id
AND A.build_index_flag = 'Y'
AND A.terr_values_idx_header_id = B.terr_values_idx_header_id
AND B.values_col_map is not null
GROUP BY A.terr_values_idx_header_id
ORDER BY 2;
SELECT * FROM (
SELECT A.terr_values_idx_header_id, count(*) num_components
FROM jty_terr_values_idx_header A,
jty_terr_values_idx_details B
WHERE A.source_id = cl_source_id
AND A.terr_values_idx_header_id <> cl_terr_values_idx_header_id
AND A.terr_values_idx_header_id = B.terr_values_idx_header_id
AND B.values_col_map is not null
AND A.build_index_flag = 'Y'
GROUP BY A.terr_values_idx_header_id )
WHERE num_components >= cl_size
ORDER BY 2 DESC;
/* that have beeen marked deleted in incremental mode */
IF (p_mode = 'INCREMENTAL') THEN
UPDATE jty_terr_values_idx_header
SET build_index_flag = 'N'
WHERE source_id = p_source_id
AND delete_flag = 'Y';
SELECT COUNT(*)
INTO S_element_ord_subset_L_count
FROM (
SELECT rownum row_count, values_col_map, input_selectivity
FROM (
SELECT B.values_col_map, B.input_selectivity
FROM jty_terr_values_idx_details B
WHERE B.terr_values_idx_header_id = cl_set_S.terr_values_idx_header_id
AND B.values_col_map IS NOT NULL
ORDER BY B.input_selectivity )) S,
(
SELECT rownum row_count, values_col_map, input_selectivity
FROM (
SELECT B.values_col_map, B.input_selectivity
FROM jty_terr_values_idx_details B
WHERE B.terr_values_idx_header_id = cl_set_L.terr_values_idx_header_id
AND B.values_col_map IS NOT NULL
ORDER BY B.input_selectivity )) L
WHERE S.values_col_map = L.values_col_map
AND S.row_count = L.row_count;
UPDATE jty_terr_values_idx_header
SET BUILD_INDEX_FLAG = 'N'
WHERE terr_values_idx_header_id = cl_set_S.terr_values_idx_header_id;
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 = cl_source_id
AND p.trans_object_type_id = cl_trans_id
GROUP BY p.trans_object_type_id, p.qual_product_id, p.relation_product
ORDER BY p.relation_product;
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 = cl_source_id
AND p.trans_object_type_id = cl_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 = cl_source_id
AND p.trans_object_type_id = cl_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
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;
SELECT p.trans_type_id, count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
FROM jty_dea_attr_products p,
jty_dea_attr_prod_factors pf,
jty_dea_attr_factors f
WHERE p.dea_attr_products_id = pf.dea_attr_products_id
AND pf.dea_attr_factors_id = f.dea_attr_factors_id
AND f.tae_col_map is not null
AND p.source_id = cl_source_id
AND p.trans_type_id = cl_trans_id
GROUP BY p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
ORDER BY p.attr_relation_product;
SELECT * FROM (
SELECT count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
FROM jty_dea_attr_products p,
jty_dea_attr_prod_factors pf
WHERE p.dea_attr_products_id = pf.dea_attr_products_id
AND p.source_id = cl_source_id
AND p.trans_type_id = cl_trans_id
GROUP BY p.dea_attr_products_id, p.attr_relation_product )
WHERE num_components >= cl_size
AND attr_relation_product > cl_relation_product
ORDER BY 1 DESC, dea_attr_products_id ASC;
SELECT p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
FROM jty_dea_attr_products p
WHERE NOT EXISTS (SELECT *
FROM jty_dea_attr_products ip,
jty_dea_attr_prod_factors ipf,
jty_dea_attr_factors ifc
WHERE use_tae_col_in_index_flag = 'Y'
AND ip.dea_attr_products_id = ipf.dea_attr_products_id
AND ipf.dea_attr_factors_id = ifc.dea_attr_factors_id
AND ip.dea_attr_products_id = p.dea_attr_products_id)
AND p.source_id = cl_source_id
AND p.trans_type_id = cl_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.attr_relation_product, f.tae_col_map, f.input_selectivity
FROM jty_dea_attr_products p,
jty_dea_attr_prod_factors pf,
jty_dea_attr_factors f
WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
AND pf.dea_attr_products_id = p.dea_attr_products_id
AND p.attr_relation_product = cl_set_S.attr_relation_product
AND f.tae_col_map is not null
AND p.source_id = p_source_id
AND p.trans_type_id = p_trans_id
ORDER BY input_selectivity )) S,
(
SELECT rownum row_count, tae_col_map, input_selectivity
FROM (
SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
FROM jty_dea_attr_products p,
jty_dea_attr_prod_factors pf,
jty_dea_attr_factors f
WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
AND pf.dea_attr_products_id = p.dea_attr_products_id
AND p.attr_relation_product = cl_set_L.attr_relation_product
AND f.tae_col_map is not null
AND p.source_id = p_source_id
AND p.trans_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 jty_dea_attr_prod_factors pf,
jty_dea_attr_factors f
WHERE pf.dea_attr_factors_id = f.dea_attr_factors_id
AND pf.dea_attr_products_id = cl_set_S.dea_attr_products_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 JTY_DEA_ATTR_PRODUCTS
SET BUILD_INDEX_FLAG = 'N', FIRST_CHAR_FLAG = l_first_char_flag
WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
AND attr_relation_product NOT IN (4841, 324347);
UPDATE JTY_DEA_ATTR_PRODUCTS
SET FIRST_CHAR_FLAG = l_first_char_flag
WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
AND attr_relation_product IN (4841, 324347);
UPDATE JTY_DEA_ATTR_PRODUCTS
SET BUILD_INDEX_FLAG = 'Y', FIRST_CHAR_FLAG = l_first_char_flag
WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id;
UPDATE JTY_DEA_ATTR_PRODUCTS
SET FIRST_CHAR_FLAG = 'Y'
WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
AND attr_relation_product = 353393;
UPDATE JTY_DEA_ATTR_PRODUCTS p
SET BUILD_INDEX_FLAG = 'N'
WHERE p.dea_attr_products_id = empty_column_index.dea_attr_products_id;
l_selectivity_return_val NUMBER;
jty_tae_index_creation_pvt.dea_selectivity(p_trans_target, x_return_status);
x_msg_data := 'API jty_tae_index_creation_pvt.dea_selectivity has failed';
jty_tae_index_creation_pvt.selectivity(p_trans_target, p_mode, null, x_return_status);
x_msg_data := 'API jty_tae_index_creation_pvt.selectivity has failed';