DECLARE

  l_query VARCHAR2(4000);
  l_lat double precision;
  l_lng double precision;
  l_orientation CHAR(1) := v('P5_ORIENTATION');
  l_distance_sort CHAR(3) := v('P5_DISTANCE_SORT');


BEGIN

  --Sync index before retrieving rows to ensure up-to-date records every time
  ctx_ddl.sync_index('properties_addr1_ctx_idx', '2M');

  --Used subquery here to get my distance sorting feature working correctly
  l_query:=
   'select *
   from (
   select
        rowid,
        p_id,
        p_i_id,
        a_id,
        address_line_1,
        address_line_2,
        city,
        postcode,
        geom_location,
        case when v(''P5_POSTCODE_SEARCH'') is null then ''N/A''
        else ''value''
        end as distance,
        notes,
        image_filename,
        property_image,
        dbms_lob.getlength("PROPERTY_IMAGE_THUMBNAIL") "PROPERTY_IMAGE_THUMBNAIL",
        agency_name,
        contact_email,
        contact_phone
    from   vw_listings_overview ) l';

  IF v('P5_ADDRESS_SEARCH') IS NOT NULL THEN
      l_query := l_query||' '||'
      where (CONTAINS(address_line_1, ''$'|| v('P5_ADDRESS_SEARCH') ||''') > 0)';
  ELSE
      l_query := l_query||' '||'
      where
        (instr(upper(''address_line_1''),upper(nvl(:P5_ADDRESS_SEARCH, ''address_line_1''))) > 0)';
  END IF;

  IF v('P5_POSTCODE_SEARCH') IS NOT NULL THEN

      brian.GEOCODE_GM_XML (:P5_POSTCODE_SEARCH, l_lat, l_lng);
      DBMS_OUTPUT.PUT_LINE('Latitude ='||l_lat);
      DBMS_OUTPUT.PUT_LINE('Longitude ='||l_lng);

      l_query := REPLACE(l_query, '''value''', 'cast(round(sdo_geom.sdo_distance(
                         sdo_geometry(2001, 8307,
                         sdo_point_type('||l_lng||','||l_lat||', null), null, null),
                         geom_location, 0.005, ''unit=mile''), ''2'') as varchar2(10))');

  END IF;

  --Radio group item logic
  IF l_orientation <> 'A' THEN
      IF l_orientation = 'L' THEN
          l_query :=
              l_query ||' and l.property_image.getWidth() > l.property_image.getHeight() ';

      ELSIF l_orientation = 'P' THEN
          l_query :=
              l_query ||' and l.property_image.getWidth() < l.property_image.getHeight() ';
      END IF;
  END IF;

  IF l_distance_sort <> 'A' THEN
      IF v('P5_POSTCODE_SEARCH') IS NOT NULL THEN
          l_query :=
              l_query ||' and distance < ' || l_distance_sort || ' ';
      END IF;
  END IF;

  --Append 'order by' clause onto query if user has searched via a postcode ->
  IF v('P5_POSTCODE_SEARCH') IS NOT NULL THEN
       l_query := l_query||' '||'order by distance asc nulls last;';
  END IF;

  -- test and debug only
    htp.p( l_query );
    htp.p( '<br>' );
    htp.p( v('P5_ADDRESS_SEARCH') );
    htp.p( '<br>' );
    htp.p('Orientation: '||v('P5_ORIENTATION') );
    htp.p( '<br>' );
    htp.p('Distance Sort: '||v('P5_DISTANCE_SORT') );
    htp.p( '<br><br>' );

  RETURN l_query;

END;