create or replace package body apps.pvertex_duplicate_address is



  PROCEDURE io_print(msg IN VARCHAR2, flag IN VARCHAR2) IS
  BEGIN
    IF flag = 'D' THEN
      dbms_output.put_line(msg);
    ELSIF flag = 'L' THEN
      fnd_file.put_line(fnd_file.log, msg);
    ELSIF flag = 'O' THEN
      fnd_file.put_line(fnd_file.output, msg);
    ELSE
      dbms_output.put_line('FLAG IS NULL');
    END IF;
  END;

  PROCEDURE p_duplicate(errbuf OUT VARCHAR2, retcode OUT NUMBER) is
  
    cursor invoices is
      select b.creation_date,
             b.batch_source_name,
             interface_line_attribute1,
             interface_line_attribute2,
             hl.address1,
             hl.address2,
             hl.address3,
             hl.address4,
             upper(hl.city) city,
             hl.state,
             hl.province,
             upper(hl.county) county,
             hl.country,
             hl.postal_code
        from ra_interface_lines_all b,
             hz_cust_acct_sites_all hcas,
             hz_party_sites         hps,
             hz_locations           hl
       where b.creation_date >= sysdate - 30
         and interface_line_attribute1 in ('US031079601') --, '100362183')
         and b.orig_system_ship_address_id = hcas.cust_acct_site_id
         and hcas.party_site_id = hps.party_site_id
         and hps.location_id = hl.location_id;
  
    cursor c_vertex(city   IN VARCHAR2,
                    county IN VARCHAR2 ) is
    
      select lc.LOCNAMECITY
        from VERTEX.LOCCITY lc, vertex.loccounty lco --, vertex.locstate ls
       where lc.LOCNAMECITY = city
         and lco.LOCNAMECOUNTY = county
                   and lc.LOCGEOCOUNTY = lco.LOCGEOCOUNTY;
    
  
    p_vertex c_vertex%ROWTYPE;
    p_city   varchar2(35);
    p_county varchar2(35);
  
    cit varchar2(35);
  
  begin
    for i in invoices loop
    
      p_city   := i.city;
      p_county := i.county;
    
      dbms_output.put_line((p_city));
      dbms_output.put_line(length(i.city));
      dbms_output.put_line((p_county));
      dbms_output.put_line(length(i.county));
      open c_vertex(p_city, p_county /*, i.postal_code*/);
      FETCH c_vertex
        INTO cit;
      IF c_vertex%NOTFOUND THEN
        dbms_output.put_line(cit);
        io_print('Not Validated', 'D');
      else
        io_print('Validated', 'D');
        dbms_output.put_line(cit);
      END IF;
      CLOSE c_vertex;
    
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      errbuf  := SQLERRM;
      retcode := 2;
  END;
END;