[Home] [Help]
128: l_temp_usage VARCHAR2(30);
129:
130: CURSOR c_geo_id (l_country_code IN VARCHAR2) IS
131: SELECT geography_id
132: FROM hz_geography_identifiers
133: WHERE UPPER(identifier_value) = l_country_code
134: AND identifier_type = 'CODE'
135: AND identifier_subtype = 'ISO_COUNTRY_CODE'
136: AND geography_use = 'MASTER_REF'
745: lv_geo_id NUMBER;
746:
747: CURSOR c_geo_name (v_geo_name VARCHAR2, v_geo_type VARCHAR2, v_country_code VARCHAR2) IS
748: SELECT hg.geography_name, hg.geography_id, hg.geography_code
749: FROM hz_geography_identifiers hi
750: ,hz_geographies hg
751: WHERE hi.identifier_type = 'NAME'
752: AND UPPER(hi.identifier_value) = v_geo_name
753: AND hi.geography_type = v_geo_type
759: ;
760:
761: CURSOR c_geo_code (v_geo_code VARCHAR2, v_geo_type VARCHAR2, v_country_code VARCHAR2) IS
762: SELECT hg.geography_name, hg.geography_id, hg.geography_code
763: FROM hz_geography_identifiers hi
764: ,hz_geographies hg
765: WHERE hi.identifier_type = 'CODE'
766: AND UPPER(hi.identifier_value) = v_geo_code
767: AND hi.geography_type = v_geo_type
1185: AND UPPER(hgi1.identifier_value) LIKE UPPER('us%')
1186: --AND hgi1.identifier_type = 'CODE' -- 'NAME'
1187: */
1188:
1189: l_from_5 := l_from_5 ||', hz_geography_identifiers hg'||ll_index;
1190:
1191: l_where_5 := l_where_5 ||' AND hg0.'||geo_struct_tbl(ll_index).v_element_col||'_id = hg'||ll_index||'.geography_id'
1192: ||' AND hg'||ll_index||'.geography_use = hg'||to_char(l_priv_valid_index)||'.geography_use'
1193: ||' AND hg'||ll_index||'.geography_type = :x_'||ll_index||'_1 '
1217: PROCEDURE create_where_2_clause_pvt (ll_index NUMBER, ll_sql_id NUMBER) IS
1218: BEGIN
1219: -- 1st select stmt
1220: IF (ll_sql_id = 1) THEN
1221: l_from_6 := l_from_6 ||', hz_geography_identifiers hg'||ll_index;
1222:
1223: -- we are adding country_code in where clause. This has to be done only once
1224: -- so putting the check of index = 1
1225: IF (ll_index = 1) THEN
1244:
1245: -- 2nd select stmt
1246: IF (ll_sql_id = 2) THEN
1247:
1248: l_from_7 := l_from_7 ||', hz_geography_identifiers hg'||ll_index;
1249:
1250: -- we are adding country_code in where clause. This has to be done only once
1251: -- so putting the check of index = 1
1252: IF (ll_index = 1) THEN
2149: ------------------
2150: -- select stmt to fetch the multiple parent records for passed value
2151: -- once record is fetched, seperate multiple parent procedure will get all
2152: -- corresponding data for that
2153: l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
2154: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2155: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2156: ||' AND upper(hg0.country_code) = :x_country_code_101 '
2157: ||' AND hg0.multiple_parent_flag = ''Y'' '
2165: -- once record is fetched, seperate multiple parent procedure will get all
2166: -- corresponding data for that union case where we are doing max mapped element -1
2167: -- i.e. if Foster City + 94065 is passed then, check for Foster City for Multiple
2168: -- Parent case.
2169: l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
2170: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2171: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2172: ||' AND upper(hg0.country_code) = :x_country_code_102 '
2173: ||' AND hg0.multiple_parent_flag = ''Y'' '