[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT geography_id
FROM hz_geography_identifiers
WHERE UPPER(identifier_value) = l_country_code
AND identifier_type = 'CODE'
AND identifier_subtype = 'ISO_COUNTRY_CODE'
AND geography_use = 'MASTER_REF'
AND geography_type = 'COUNTRY'
;
l_query := 'SELECT COUNT(*) FROM ('||l_query_temp||')';
SELECT hg.geography_name, hg.geography_id, hg.geography_code
FROM hz_geography_identifiers hi
,hz_geographies hg
WHERE hi.identifier_type = 'NAME'
AND UPPER(hi.identifier_value) = v_geo_name
AND hi.geography_type = v_geo_type
AND hi.geography_use = 'MASTER_REF'
AND hi.geography_id = hg.geography_id
AND hg.geography_use = 'MASTER_REF'
AND UPPER(hg.country_code) = v_country_code
AND SYSDATE BETWEEN hg.START_DATE AND hg.END_DATE
;
SELECT hg.geography_name, hg.geography_id, hg.geography_code
FROM hz_geography_identifiers hi
,hz_geographies hg
WHERE hi.identifier_type = 'CODE'
AND UPPER(hi.identifier_value) = v_geo_code
AND hi.geography_type = v_geo_type
AND hi.geography_use = 'MASTER_REF'
AND hi.geography_id = hg.geography_id
AND hg.geography_use = 'MASTER_REF'
AND UPPER(hg.country_code) = v_country_code
AND SYSDATE BETWEEN hg.START_DATE AND hg.END_DATE
;
l_select VARCHAR2(2000);
l_select_6 VARCHAR2(1000);
l_select_mp VARCHAR2(1000);
PROCEDURE insert_in_geo_suggest_tbl (pv_geo_data_count NUMBER,
pv_geo_rec_tab_col VARCHAR2,
pv_geo_rec_geo_name VARCHAR2,
pv_geo_rec_geo_id NUMBER,
pv_geo_rec_geo_code VARCHAR2)
IS
pv_geo_rec_geo_type VARCHAR2(100);
END insert_in_geo_suggest_tbl;
PROCEDURE insert_in_geo_rec_tbl (pv_geo_data_count NUMBER,
pv_geo_rec_element_col VARCHAR2,
pv_geo_rec_geo_name VARCHAR2,
pv_geo_rec_geo_id NUMBER,
pv_geo_rec_geo_code VARCHAR2)
IS
pv_geo_rec_geo_type VARCHAR2(100);
END insert_in_geo_rec_tbl;
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(ll_geo_suggest_index,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
PROCEDURE insert_mp_in_geo_rec_proc (ll_geo_data_count IN NUMBER,
ll_geo_type IN VARCHAR2,
ll_geo_name IN VARCHAR2,
ll_geo_code IN VARCHAR2,
ll_geo_id IN NUMBER,
x_insert_geo_suggest_rec OUT nocopy VARCHAR2) IS
ll_mapped_param_value VARCHAR2(100);
insert_in_geo_rec_tbl(ll_geo_data_count,
ll_mapped_element_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
x_insert_geo_suggest_rec := 'N';
ELSE -- if mapped parameter not passed, insert it
insert_in_geo_rec_tbl(ll_geo_data_count,
ll_mapped_element_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
END insert_mp_in_geo_rec_proc;
l_insert_geo_valid_rec_final VARCHAR2(1);
l_insert_geo_suggest_rec VARCHAR2(1);
SELECT COUNT(*)
INTO l_structure_level_count
FROM hz_geo_structure_levels
WHERE country_code = upper(p_country_code)
CONNECT BY geography_type = PRIOR parent_geography_type
AND country_code = upper(p_country_code)
START WITH geography_type = ll_geo_type
AND country_code = upper(p_country_code);
-- build the select statement
l_select_mp := 'SELECT distinct p0.geography_id , p0.geography_name ,'
||'p0.geography_code , p0.geography_type '
||',p1.geography_id , p1.geography_name ,'
||'p1.geography_code , p1.geography_type ';
l_select_mp := l_select_mp ||',p'||i||'.geography_id , p'||i||'.geography_name '
||',p'||i||'.geography_code, p'||i||'.geography_type ';
l_select_mp := l_select_mp ||', NULL ';
l_sql_stmt_mp := l_select_mp||l_from_mp||l_where_mp;
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_1,
ll_geo_name => rec_mp.geo_name_1,
ll_geo_code => rec_mp.geo_code_1,
ll_geo_id => rec_mp.geo_id_1,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_2 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_2,
ll_geo_name => rec_mp.geo_name_2,
ll_geo_code => rec_mp.geo_code_2,
ll_geo_id => rec_mp.geo_id_2,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_3 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_3,
ll_geo_name => rec_mp.geo_name_3,
ll_geo_code => rec_mp.geo_code_3,
ll_geo_id => rec_mp.geo_id_3,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_4 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_4,
ll_geo_name => rec_mp.geo_name_4,
ll_geo_code => rec_mp.geo_code_4,
ll_geo_id => rec_mp.geo_id_4,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_5 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_5,
ll_geo_name => rec_mp.geo_name_5,
ll_geo_code => rec_mp.geo_code_5,
ll_geo_id => rec_mp.geo_id_5,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_6 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_6,
ll_geo_name => rec_mp.geo_name_6,
ll_geo_code => rec_mp.geo_code_6,
ll_geo_id => rec_mp.geo_id_6,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_7 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_7,
ll_geo_name => rec_mp.geo_name_7,
ll_geo_code => rec_mp.geo_code_7,
ll_geo_id => rec_mp.geo_id_7,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_8 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_8,
ll_geo_name => rec_mp.geo_name_8,
ll_geo_code => rec_mp.geo_code_8,
ll_geo_id => rec_mp.geo_id_8,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_9 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_9,
ll_geo_name => rec_mp.geo_name_9,
ll_geo_code => rec_mp.geo_code_9,
ll_geo_id => rec_mp.geo_id_9,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF ((rec_mp.geo_id_10 IS NOT NULL) AND (NVL(l_insert_geo_valid_rec_final,'Y') <> 'N'))
THEN
insert_mp_in_geo_rec_proc (ll_geo_data_count => l_geo_data_count,
ll_geo_type => rec_mp.geo_type_10,
ll_geo_name => rec_mp.geo_name_10,
ll_geo_code => rec_mp.geo_code_10,
ll_geo_id => rec_mp.geo_id_10,
x_insert_geo_suggest_rec => l_insert_geo_suggest_rec);
IF (l_insert_geo_suggest_rec = 'N') THEN
l_insert_geo_valid_rec_final := 'N';
IF (l_insert_geo_valid_rec_final = 'N') THEN
geo_rec_tbl.DELETE(l_geo_data_count);
l_insert_geo_valid_rec_final := 'Y';
l_insert_geo_suggest_rec := 'Y';
-- Select clause
-- NOTE : IF ANY element is added or deleted in select clause, modify geo_rec_type
-- record type accordingly.
l_select := 'SELECT DISTINCT hg0.GEOGRAPHY_ID, hg0.GEOGRAPHY_TYPE, hg0.GEOGRAPHY_NAME,'||
'hg0.GEOGRAPHY_CODE, hg0.MULTIPLE_PARENT_FLAG, hg0.COUNTRY_CODE,'||
'hg0.GEOGRAPHY_ELEMENT1, hg0.GEOGRAPHY_ELEMENT1_ID, hg0.GEOGRAPHY_ELEMENT1_CODE,'||
'hg0.GEOGRAPHY_ELEMENT2, hg0.GEOGRAPHY_ELEMENT2_ID, hg0.GEOGRAPHY_ELEMENT2_CODE,'||
'hg0.GEOGRAPHY_ELEMENT3, hg0.GEOGRAPHY_ELEMENT3_ID, hg0.GEOGRAPHY_ELEMENT3_CODE,'||
'hg0.GEOGRAPHY_ELEMENT4, hg0.GEOGRAPHY_ELEMENT4_ID, hg0.GEOGRAPHY_ELEMENT4_CODE,'||
'hg0.GEOGRAPHY_ELEMENT5, hg0.GEOGRAPHY_ELEMENT5_ID, hg0.GEOGRAPHY_ELEMENT5_CODE,'||
'hg0.GEOGRAPHY_ELEMENT6, hg0.GEOGRAPHY_ELEMENT6_ID,'||
'hg0.GEOGRAPHY_ELEMENT7, hg0.GEOGRAPHY_ELEMENT7_ID,'||
'hg0.GEOGRAPHY_ELEMENT8, hg0.GEOGRAPHY_ELEMENT8_ID,'||
'hg0.GEOGRAPHY_ELEMENT9, hg0.GEOGRAPHY_ELEMENT9_ID,'||
'hg0.GEOGRAPHY_ELEMENT10, hg0.GEOGRAPHY_ELEMENT10_ID ';
l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
||' WHERE hg0.geography_use = ''MASTER_REF'' '
||' AND SYSDATE between hg0.start_date and hg0.end_date '
||' AND upper(hg0.country_code) = :x_country_code_101 '
||' AND hg0.multiple_parent_flag = ''Y'' '
||' AND hg0.geography_id = hg1.geography_id '
||' AND hg1.geography_use = ''MASTER_REF'' '
||' AND hg1.geography_type = :x_geo_type_101 '
||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_101 ' ;
l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
||' WHERE hg0.geography_use = ''MASTER_REF'' '
||' AND SYSDATE between hg0.start_date and hg0.end_date '
||' AND upper(hg0.country_code) = :x_country_code_102 '
||' AND hg0.multiple_parent_flag = ''Y'' '
||' AND hg0.geography_id = hg1.geography_id '
||' AND hg1.geography_use = ''MASTER_REF'' '
||' AND hg1.geography_type = :x_geo_type_102 '
||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_102 ' ;
l_sql_stmt := l_select||l_from||l_where||l_sql_stmt_2||l_order_by;
l_sql_stmt := l_select||l_from||l_where||l_order_by;
l_sql_stmt := l_select||l_from||l_where||l_sql_stmt_2||l_order_by;
l_sql_stmt := l_select||l_from||l_from_6||l_where_6||l_sql_stmt_2||l_order_by;
l_sql_stmt := l_select||l_from||l_from_7||l_where_7||l_sql_stmt_2||l_sql_stmt_3||l_order_by;
l_sql_stmt := l_select||l_from||l_from_6||l_where_6
||' UNION '||
l_select||l_from||l_from_7||l_where_7||l_sql_stmt_2||l_sql_stmt_3||l_order_by;
ns_debug.put_line('Length of SELECT Clause : '||LENGTH(l_select));
-- DELETE all other rows and keep only this row in address suggestion
l_geo_suggest_tbl_temp(1) := geo_suggest_tbl(i);
geo_suggest_tbl.DELETE;
SELECT dmap.loc_component, dmap.geography_type, dmap.geo_element_col, dmap.loc_seq_num
FROM hz_geo_struct_map smap
,hz_geo_struct_map_dtl dmap
WHERE smap.address_style = ll_address_style
AND SMAP.country_code = ll_country_code
AND SMAP.loc_tbl_name = ll_table_name
AND smap.map_id = dmap.map_id
ORDER BY dmap.loc_seq_num;
SELECT dmap.loc_component, dmap.geography_type, dmap.geo_element_col, dmap.loc_seq_num
FROM hz_geo_struct_map smap
,hz_geo_struct_map_dtl dmap
WHERE smap.address_style IS NULL
AND SMAP.country_code = ll_country_code
AND SMAP.loc_tbl_name = ll_table_name
AND smap.map_id = dmap.map_id
ORDER BY dmap.loc_seq_num;
SELECT haud.geography_type
FROM hz_geo_struct_map smap
,hz_geo_struct_map_dtl dmap
,hz_address_usages hau
,hz_address_usage_dtls haud
WHERE smap.address_style = ll_address_style
AND SMAP.country_code = ll_country_code
AND SMAP.loc_tbl_name = ll_table_name
AND smap.map_id = dmap.map_id
AND smap.map_id = hau.map_id
AND hau.usage_code = ll_usage
AND hau.status_flag = 'A'
AND hau.usage_id = haud.usage_id
AND haud.geography_type = dmap.geography_type
ORDER BY dmap.loc_seq_num;
SELECT haud.geography_type
FROM hz_geo_struct_map smap
,hz_geo_struct_map_dtl dmap
,hz_address_usages hau
,hz_address_usage_dtls haud
WHERE smap.address_style IS NULL
AND SMAP.country_code = ll_country_code
AND SMAP.loc_tbl_name = ll_table_name
AND smap.map_id = dmap.map_id
AND smap.map_id = hau.map_id
AND hau.usage_code = ll_usage
AND hau.status_flag = 'A'
AND hau.usage_id = haud.usage_id
AND haud.geography_type = dmap.geography_type
ORDER BY dmap.loc_seq_num;
geo_struct_tbl.DELETE;
geo_suggest_tbl.DELETE(1);
l_insert_geo_valid_rec_final VARCHAR2(1);
l_insert_geo_suggest_rec VARCHAR2(1);
l_select_child VARCHAR2(2000);
l_select_child := 'SELECT distinct p1.geography_id , p1.geography_name ,'
||'p1.geography_code , p1.geography_type ';
l_select_child := l_select_child ||',p'||l_child_map_count||'.geography_id , p'||l_child_map_count||'.geography_name '
||',p'||l_child_map_count||'.geography_code, p'||l_child_map_count||'.geography_type ';
-- pad rest of the columns in select stmt (to be fetched in child_rec record) to null
-- for example, if geo_struct_tbl contains 3 mapping rows, then other 7 elements (since total is 10),
-- have to be made NULL in select statement
l_total_null_cols_child := (10-(l_child_map_count))*4;
l_select_child := l_select_child ||', NULL ';
l_sql_stmt_child := l_select_child||l_from_child||l_where_child;
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
insert_in_geo_suggest_tbl(l_geo_data_count,
l_geo_rec_tab_col,
l_geo_rec_geo_name,
l_geo_rec_geo_id,
l_geo_rec_geo_code
);
-- Delete bind tables that are used for search1,2,and3
bind_table_1.DELETE;
bind_table_2.DELETE;
-- we delete the results table before starting any new search routine
geo_suggest_tbl.DELETE;
geo_rec_tbl.DELETE;
bind_table_1.DELETE;
-- return suggestion. Delete other suggested values and just return the
-- fetched values. Bug 4633962 (Fixed by Nishant Singhai on 29-Sep-2005)
-------------------------------------------------------------------------+
check_exact_match_del_rest;