APPS.PV_PARTNER_GEO_MATCH_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 82

      select LH.location_hierarchy_id,
      LH.LOCATION_TYPE_CODE TYPE_CODE,
      DECODE(LH.LOCATION_TYPE_CODE,
      'AREA1', LH.AREA1_CODE,
      'AREA2',LH.AREA2_CODE,
      'COUNTRY', LH.COUNTRY_CODE,
      'CREGION', LH.COUNTRY_REGION_CODE,
      'STATE', LH.STATE_CODE,
      'SREGION', LH.STATE_REGION_CODE,
      'CITY', LH.CITY_CODE,
      'POSTAL_CODE', LH.POSTAL_CODE_START||'-'||LH.POSTAL_CODE_END) LOWEST_CODE,
      DECODE(LH.LOCATION_TYPE_CODE, 'POSTAL_CODE',1,
      'CITY', 2,
      'SREGION', 3,
      'STATE', 4,
      'CREGION', 5,
      'COUNTRY', 6,
      'AREA2', 7,
      'AREA1', 8) LOC_TYPE
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.location_hierarchy_id in (
      	SELECT * FROM TABLE (CAST(p_geo_hierarchy_id AS JTF_NUMBER_TABLE))
      )
      and lh.location_type_code in ('AREA1', 'AREA2', 'COUNTRY', 'CREGION', 'STATE', 'SREGION', 'CITY', 'POSTAL_CODE')
      order by loc_type;

Line: 109

      select L.postal_code, L.city, L.state, L.country
      from hz_party_sites PS, hz_locations L
      where PS.location_id = L.location_id
            and PS.party_id = cv_partner_party_id
            and PS.identifying_address_flag = 'Y';

Line: 117

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.postal_code_start <= cv_postal_code
            and LH.postal_code_end >= cv_postal_code
            and LH.city_code = cv_city_code
            and LH.state_code = cv_state_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'POSTAL_CODE';

Line: 129

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.postal_code_start <= cv_postal_code
            and LH.postal_code_end >= cv_postal_code
            and LH.city_code = cv_city_code
            and LH.state_region_code = cv_state_region_code
            and LH.state_code = cv_state_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'POSTAL_CODE';

Line: 142

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.postal_code_start <= cv_postal_code
            and LH.postal_code_end >= cv_postal_code
            and LH.city_code = cv_city_code
            and LH.state_code = cv_state_code
            and LH.country_region_code = cv_country_region_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'POSTAL_CODE';

Line: 154

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.city_code = cv_city_code
            and LH.state_code = cv_state_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'CITY';

Line: 163

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.city_code = cv_city_code
            and LH.state_region_code = cv_state_region_code
            and LH.state_code = cv_state_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'CITY';

Line: 173

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.city_code = cv_city_code
            and LH.state_code = cv_state_code
            and LH.country_region_code = cv_country_region_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'CITY';

Line: 182

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.state_code = cv_state_code
            and LH.country_code = cv_country_code
            and lh.location_type_code = 'STATE';

Line: 190

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.state_code = cv_state_code
            and LH.country_region_code = cv_country_region_code
            and LH.country_code = cv_country_code
            and LH.location_type_code = 'STATE';

Line: 198

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
            and LH.location_type_code = 'COUNTRY';

Line: 204

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
            and LH.area2_code = cv_area2_code
            and LH.location_type_code = 'COUNTRY';

Line: 211

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
            and LH.area1_code = cv_area1_code
            and LH.location_type_code = 'COUNTRY';

Line: 273

            SELECT substr(l_ordered_loc_rec.lowest_code, instr(l_ordered_loc_rec.lowest_code, '-')+1)
               INTO l_postal_code_start
               FROM dual;

Line: 277

            SELECT substr(l_ordered_loc_rec.lowest_code, 1, instr(l_ordered_loc_rec.lowest_code, '-')-1)
               INTO l_postal_code_end
               FROM dual;

Line: 500

      select LH.location_hierarchy_id,
      LH.LOCATION_TYPE_CODE TYPE_CODE,
      DECODE(LH.LOCATION_TYPE_CODE,
      'AREA1', LH.AREA1_CODE,
      'AREA2',LH.AREA2_CODE,
      'COUNTRY', LH.COUNTRY_CODE) LOWEST_CODE,
      DECODE(LH.LOCATION_TYPE_CODE,
      'COUNTRY', 6,
      'AREA2', 7,
      'AREA1', 8) LOC_TYPE
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.location_hierarchy_id in (
      	SELECT * FROM TABLE (CAST(p_geo_hierarchy_id AS JTF_NUMBER_TABLE))
      )
      and LH.LOCATION_TYPE_CODE in ('AREA1', 'AREA2', 'COUNTRY')
      order by loc_type;

Line: 518

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
      and LH.location_type_code = 'COUNTRY';

Line: 524

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
      and LH.area2_code = cv_area2_code
      and LH.location_type_code = 'COUNTRY';

Line: 531

      select 'x'
      from JTF_LOC_HIERARCHIES_VL LH
      where LH.country_code = cv_country_code
      and LH.area1_code = cv_area1_code
      and LH.location_type_code = 'COUNTRY';

Line: 610

      select L.country
      from hz_party_sites PS, hz_locations L, pv_partner_profiles PP
      where PS.location_id = L.location_id
            and PP.partner_party_id = PS.party_id
            and PP.partner_id = cv_partner_id
            and PS.identifying_address_flag = 'Y';

Line: 704

      select L.country
      from hz_party_sites PS, hz_locations L
      where PS.location_id = L.location_id
            and PS.party_id = cv_party_id
            and PS.identifying_address_flag = 'Y';