eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_GNR_PVT dependencies on HZ_GEOGRAPHY_IDENTIFIERS

Line 136: FROM hz_geography_identifiers

132: l_temp_usage VARCHAR2(30);
133:
134: CURSOR c_geo_id (l_country_code IN VARCHAR2) IS
135: SELECT geography_id
136: FROM hz_geography_identifiers
137: WHERE UPPER(identifier_value) = l_country_code
138: AND identifier_type = 'CODE'
139: AND identifier_subtype = 'ISO_COUNTRY_CODE'
140: AND geography_use = 'MASTER_REF'

Line 753: FROM hz_geography_identifiers hi

749: lv_geo_id NUMBER;
750:
751: CURSOR c_geo_name (v_geo_name VARCHAR2, v_geo_type VARCHAR2, v_country_code VARCHAR2) IS
752: SELECT hg.geography_name, hg.geography_id, hg.geography_code
753: FROM hz_geography_identifiers hi
754: ,hz_geographies hg
755: WHERE hi.identifier_type = 'NAME'
756: AND UPPER(hi.identifier_value) = v_geo_name
757: AND hi.geography_type = v_geo_type

Line 767: FROM hz_geography_identifiers hi

763: ;
764:
765: CURSOR c_geo_code (v_geo_code VARCHAR2, v_geo_type VARCHAR2, v_country_code VARCHAR2) IS
766: SELECT hg.geography_name, hg.geography_id, hg.geography_code
767: FROM hz_geography_identifiers hi
768: ,hz_geographies hg
769: WHERE hi.identifier_type = 'CODE'
770: AND UPPER(hi.identifier_value) = v_geo_code
771: AND hi.geography_type = v_geo_type

Line 1197: l_from_5 := l_from_5 ||', hz_geography_identifiers hg'||ll_index;

1193: */
1194:
1195: -- Commented by Nishant Singhai for Bug 10007587 (modified SQL query below)
1196: /*
1197: l_from_5 := l_from_5 ||', hz_geography_identifiers hg'||ll_index;
1198:
1199: l_where_5 := l_where_5 ||' AND hg0.'||geo_struct_tbl(ll_index).v_element_col||'_id = hg'||ll_index||'.geography_id'
1200: ||' AND hg'||ll_index||'.geography_use = hg'||to_char(l_priv_valid_index)||'.geography_use'
1201: ||' AND hg'||ll_index||'.geography_type = :x_'||ll_index||'_1 '

Line 1208: FROM HZ_GEOGRAPHY_IDENTIFIERS HG1

1204: ---------------------------------
1205: /* -- New Sample query
1206: AND EXISTS
1207: (SELECT *+ no_unnest * NULL
1208: FROM HZ_GEOGRAPHY_IDENTIFIERS HG1
1209: WHERE hg0.GEOGRAPHY_ELEMENT1_id = hg1.geography_id
1210: AND HG1.GEOGRAPHY_USE = HG0.GEOGRAPHY_USE
1211: AND HG1.GEOGRAPHY_TYPE = :x_1_1
1212: AND UPPER(HG1.IDENTIFIER_VALUE) LIKE :x_1_2

Line 1216: L_WHERE_5 := L_WHERE_5 ||' AND EXISTS (SELECT /+ no_unnest / NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX

1212: AND UPPER(HG1.IDENTIFIER_VALUE) LIKE :x_1_2
1213: AND HG1.IDENTIFIER_TYPE = :x_1_3
1214: )
1215: */
1216: L_WHERE_5 := L_WHERE_5 ||' AND EXISTS (SELECT /*+ no_unnest */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX
1217: ||' WHERE hg0.'||GEO_STRUCT_TBL(LL_INDEX).V_ELEMENT_COL||'_id = hg'||LL_INDEX||'.geography_id '
1218: ||' AND hg'||LL_INDEX||'.geography_use = hg0.geography_use'
1219: ||' AND hg'||LL_INDEX||'.geography_type = :x_'||LL_INDEX||'_1 '
1220: ||' AND UPPER(hg'||LL_INDEX||'.identifier_value) LIKE :x_'||LL_INDEX||'_2 '

Line 1253: -- l_from_6 := l_from_6 ||', hz_geography_identifiers hg'||ll_index;

1249: BEGIN
1250: -- 1st select stmt
1251: IF (LL_SQL_ID = 1) THEN
1252: -- Commented by Nishant Singhai for Bug 10007587 (modified SQL query below)
1253: -- l_from_6 := l_from_6 ||', hz_geography_identifiers hg'||ll_index;
1254:
1255: -- we are adding country_code in where clause. This has to be done only once
1256: -- so putting the check of index = 1
1257: IF (ll_index = 1) THEN

Line 1275: L_WHERE_6 := L_WHERE_6 ||' AND EXISTS (SELECT /+ no_unnest / NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX

1271: ||' AND UPPER(hg'||ll_index||'.identifier_value) LIKE :x_'||ll_index||'_2 ';
1272: */
1273:
1274: ------------
1275: L_WHERE_6 := L_WHERE_6 ||' AND EXISTS (SELECT /*+ no_unnest */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX
1276: ||' WHERE hg0.'||GEO_STRUCT_TBL(LL_INDEX).V_ELEMENT_COL||'_id = hg'||LL_INDEX||'.geography_id'
1277: ||' AND hg'||LL_INDEX||'.geography_use = hg0.geography_use'
1278: ||' AND hg'||LL_INDEX||'.geography_type = :x_'||LL_INDEX||'_1 '
1279: ||' AND UPPER(hg'||LL_INDEX||'.identifier_value) LIKE :x_'||LL_INDEX||'_2 ) ';

Line 1292: -- l_from_7 := l_from_7 ||', hz_geography_identifiers hg'||ll_index;

1288:
1289: -- 2nd select stmt
1290: IF (LL_SQL_ID = 2) THEN
1291: -- Commented by Nishant Singhai for Bug 10007587 (modified SQL query below)
1292: -- l_from_7 := l_from_7 ||', hz_geography_identifiers hg'||ll_index;
1293:
1294: -- we are adding country_code in where clause. This has to be done only once
1295: -- so putting the check of index = 1
1296: IF (ll_index = 1) THEN

Line 1313: L_WHERE_7 := L_WHERE_7 || ' AND EXISTS (SELECT /+ no_unnest / NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX

1309: ||' AND hg'||ll_index||'.geography_type = :x_'||ll_index||'_11 '
1310: ||' AND UPPER(hg'||ll_index||'.identifier_value) LIKE :x_'||ll_index||'_12 ';
1311: */
1312:
1313: L_WHERE_7 := L_WHERE_7 || ' AND EXISTS (SELECT /*+ no_unnest */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS hg'||LL_INDEX
1314: ||' WHERE hg0.'||GEO_STRUCT_TBL(LL_INDEX).V_ELEMENT_COL||'_id = hg'||LL_INDEX||'.geography_id'
1315: ||' AND hg'||LL_INDEX||'.geography_use = hg0.geography_use'
1316: ||' AND hg'||LL_INDEX||'.geography_type = :x_'||LL_INDEX||'_11 '
1317: ||' AND UPPER(hg'||LL_INDEX||'.identifier_value) LIKE :x_'||LL_INDEX||'_12 ) ';

Line 2221: l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '

2217:
2218: -- Commented by Nishant Singhai for Bug 10007587 (modified SQL query below)
2219: -- Additional fix from bug 12335409 for performance in new query
2220: /*
2221: l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
2222: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2223: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2224: ||' AND upper(hg0.country_code) = :x_country_code_101 '
2225: ||' AND hg0.multiple_parent_flag = ''Y'' '

Line 2231: l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from --||' , hz_geography_identifiers hg1 '

2227: ||' AND hg1.geography_use = ''MASTER_REF'' '
2228: ||' AND hg1.geography_type = :x_geo_type_101 '
2229: ||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_101 ' ;
2230: */
2231: l_sql_stmt_2 := ' UNION ALL '||l_select ||l_from --||' , hz_geography_identifiers hg1 '
2232: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2233: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2234: ||' AND hg0.country_code = :x_country_code_101 '
2235: ||' AND hg0.multiple_parent_flag = ''Y'' '

Line 2236: ||' AND EXISTS (SELECT /+ no_unnest / NULL FROM HZ_GEOGRAPHY_IDENTIFIERS HG1 '

2232: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2233: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2234: ||' AND hg0.country_code = :x_country_code_101 '
2235: ||' AND hg0.multiple_parent_flag = ''Y'' '
2236: ||' AND EXISTS (SELECT /*+ no_unnest */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS HG1 '
2237: ||' WHERE hg0.geography_id = hg1.geography_id '
2238: ||' AND hg1.geography_use = ''MASTER_REF'' '
2239: ||' AND hg1.geography_type = :x_geo_type_101 '
2240: ||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_101 ) ' ;

Line 2252: l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '

2248:
2249: -- Commented by Nishant Singhai for Bug 10007587 (modified SQL query below)
2250: -- Additional fix from bug 12335409 for performance in new query
2251: /*
2252: l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from||' , hz_geography_identifiers hg1 '
2253: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2254: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2255: ||' AND upper(hg0.country_code) = :x_country_code_102 '
2256: ||' AND hg0.multiple_parent_flag = ''Y'' '

Line 2262: l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from --||' , hz_geography_identifiers hg1 '

2258: ||' AND hg1.geography_use = ''MASTER_REF'' '
2259: ||' AND hg1.geography_type = :x_geo_type_102 '
2260: ||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_102 ' ;
2261: */
2262: l_sql_stmt_3 := ' UNION ALL '||l_select ||l_from --||' , hz_geography_identifiers hg1 '
2263: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2264: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2265: ||' AND hg0.country_code = :x_country_code_102 '
2266: ||' AND hg0.multiple_parent_flag = ''Y'' '

Line 2267: ||' AND EXISTS (SELECT /+ no_unnest / NULL FROM HZ_GEOGRAPHY_IDENTIFIERS HG1 '

2263: ||' WHERE hg0.geography_use = ''MASTER_REF'' '
2264: ||' AND SYSDATE between hg0.start_date and hg0.end_date '
2265: ||' AND hg0.country_code = :x_country_code_102 '
2266: ||' AND hg0.multiple_parent_flag = ''Y'' '
2267: ||' AND EXISTS (SELECT /*+ no_unnest */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS HG1 '
2268: ||' WHERE hg0.geography_id = hg1.geography_id '
2269: ||' AND hg1.geography_use = ''MASTER_REF'' '
2270: ||' AND hg1.geography_type = :x_geo_type_102 '
2271: ||' AND UPPER(hg1.identifier_value) LIKE :x_geo_name_102 ) ' ;