APPS.CSF_LF_GEOPVT SQL Statements

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

Line: 8

 PROCEDURE insert_geo_batch_address(
             p_task_rec    csf_task_address_pvt.task_rec_type
    )
    IS
    BEGIN
     INSERT INTO csf_geo_batch_address(
       ADDRESS1,
       ADDRESS2,
       ADDRESS3,
       ADDRESS4,
       POSTAL_CODE,
       CITY,
       COUNTY,
       STATE,
       COUNTRY,
       ADDRESS_ID)
       VALUES
       (initcap(p_task_rec.address1),
       initcap(p_task_rec.address2),
       initcap(p_task_rec.address3),
       initcap(p_task_rec.address4),
       p_task_rec.postal_code,
       initcap(p_task_rec.city),
       initcap(p_task_rec.county),
       initcap(p_task_rec.state),
       initcap(p_task_rec.country),
       p_task_rec.location_id
       );

Line: 38

    PROCEDURE update_geo_batch_address(
              p_task_rec    csf_task_address_pvt.task_rec_type
    )
    IS
    BEGIN
     UPDATE csf_geo_batch_address SET
       ADDRESS1 = initcap(p_task_rec.address1),
       ADDRESS2 = initcap(p_task_rec.address2),
       ADDRESS3 = initcap(p_task_rec.address3),
       ADDRESS4 = initcap(p_task_rec.address4),
       POSTAL_CODE = p_task_rec.postal_code,
       CITY = initcap(p_task_rec.city),
       COUNTY = initcap(p_task_rec.county),
       STATE = initcap(p_task_rec.state),
       COUNTRY = initcap(p_task_rec.country),
       ACCURACY = NULL,
       LATITUDE = NULL,
       LONGITUDE = NULL,
       SEGMENT_ID = NULL
     WHERE ADDRESS_ID = p_task_rec.location_id;

Line: 60

    PROCEDURE delete_valid_geo_batch_address
    IS
    BEGIN
     DELETE FROM csf_geo_batch_address
     WHERE ACCURACY >= fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');

Line: 75

      SELECT count(*) into l_location_id FROM csf_geo_batch_address WHERE address_id = p_task_rec.location_id;

Line: 78

       insert_geo_batch_address(p_task_rec);

Line: 81

       update_geo_batch_address(p_task_rec);

Line: 113

         SELECT ftt.territory_code country_code
           FROM fnd_territories_tl ftt
          WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
            AND ftt.language = 'US';

Line: 143

  , p_update_address    =>  'T'
  );

Line: 249

   l_sql_stmt := 'SELECT 1  FROM csf_lf_pois' || l_dyn_tbl_name || ' r
    WHERE SDO_WITHIN_DISTANCE(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
    SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE''  and rownum = 1';

Line: 262

  l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_POIS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
                  FROM csf_lf_pois' || l_dyn_tbl_name || '  r
                  WHERE SDO_NN(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
                  SDO_ORDINATE_ARRAY(:1, :2)), ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';

Line: 281

    l_sql_stmt := 'SELECT 1  FROM csf_lf_roadsegments' || l_dyn_tbl_name || '  r
    WHERE SDO_WITHIN_DISTANCE(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE''  and rownum = 1';

Line: 295

    l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_RDSEGS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
  FROM csf_lf_roadsegments' || l_dyn_tbl_name || '  r
  WHERE SDO_NN(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
  SDO_ORDINATE_ARRAY(:1, :2, :3, :4)),
        ''sdo_num_res=1'', 1) = ''TRUE''  ORDER BY dist';

Line: 317

    l_sql_stmt := 'select postal_code from csf_lf_roadsegm_posts' || l_dyn_tbl_name || '  rp, csf_lf_postcodes' || l_dyn_tbl_name || '  p where rp.postal_code_id =
  p.postal_code_id and rp.roadsegment_id = :1 and rownum = 1';

Line: 325

   l_sql_stmt := 'select n.name , p.place_id from csf_lf_names' || l_dyn_tbl_name || '   n,csf_lf_roadsegm_places' || l_dyn_tbl_name ||
  '   pn, csf_lf_places' || l_dyn_tbl_name || '   p, csf_lf_roadsegm_names' || l_dyn_tbl_name ||
  '  rn where rn.roadsegment_id = :1 and rn.name_id = n.name_id and rn.roadsegment_id =
  pn.roadsegment_id and pn.place_id = p.place_id and rownum = 1';

Line: 335

    l_sql_stmt := 'Select round((min(address_start) + max(address_end))/2) from (select   block_id, 1
  address_side,left_address_format address_format, left_address_scheme address_scheme, start_left_address
  address_start, end_left_address  address_end, address_type from  csf_lf_blocks' || l_dyn_tbl_name || '   where roadsegment_id = :1 union all
  select   block_id,  2,  right_address_format, right_address_scheme,start_right_address, end_right_address, address_type
  from csf_lf_blocks where roadsegment_id = :2 and rownum = 1)';

Line: 350

    l_sql_stmt := 'select  n.name, p.parent_place_id, p.place_parent_level from csf_lf_names' || l_dyn_tbl_name || '   n,
   csf_lf_place_names' || l_dyn_tbl_name || '   pn, csf_lf_places' || l_dyn_tbl_name || '   p where n.name_id = pn.name_id and
   pn.place_id = p.place_id and p.place_id = :1 and pn.name_type = ''O'' and rownum = 1';

Line: 378

  l_sql_stmt := 'select country_name from csf_sdm_ctry_profiles' || l_dyn_tbl_name || '   where place_id = :1 and
  NATIONAL_LANG_CODE = ''ENG''';