/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     This small package was used to export names of people that were to be placed on a 'Do Not Call' List.      
   When a person requests that they be taken from a marketing list, their name is flagged in the                
   telemarketing(campaign)table. The resulting names are then exported by this Package to a comma delimited 
   file where the names can be inputted into another database holding the Do Not Call List. This
   is a common situation because many of the automatic dialing software use a common database such as SQL Server.
   However access to the database is restricted, therefore most of the records are held in a different
   database where they can be easily updated and queried without affecting the dialer. The main tables are fl.lead 
   which holds all the records and fl.remove which keeps a record of all records placed on the 'Do Not Call' List.     
     The CREATE_LIST procedure gets the name of all the campaigns (telemarketing lists) in the main
   table. The names are then passed one at a time to the PRINT_TO_FILE procedure which creates a file for
   each campaign and fills the file with the required records. The init.ora parameter _UTL_FILE
   must be set to the folder where the procedure will write or the procedure will fail. 
  Created by Chris Kirlew - 02/2004
 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++   
*/  

CREATE OR REPLACE PACKAGE      DNC_EXPORT
AS
    Procedure CREATE_LIST(table_name in varchar2 := NULL);
    Procedure PRINT_TO_FILE(campaign in varchar2,new_table in varchar2);
    

END DNC_EXPORT;
/

CREATE OR REPLACE PACKAGE BODY DNC_EXPORT
AS

-- Create a new table to record records removed, insert records from external table, query table
-- for campaign name. Pass campaign name to the PRINT_TO_FILE procedure
Procedure CREATE_LIST(table_name in varchar2 := NULL) 
AS									
        campaignName		varchar2(32);  				  
  	  new_table		      varchar2(32);
	  default_table		varchar2(32); 
	  sqlstr                varchar2(1000);
	  p_query			varchar2(1000);
	  l_status              integer;
	  l_cnt			integer default 0;
	  columnValue		varchar2(32);
	  l_theCursor           integer default dbms_sql.open_cursor;


BEGIN
-- Create table to hold records to be removed
	select to_char(sysdate,'"ckirlew.remove_"YYMMDD') into default_table from dual;
	   if table_name is NULL then new_table := default_table;
	   else new_table := table_name;
	   end if;

      execute immediate 'create table '|| new_table ||' tablespace users as select * from fl.removes_ext';



-- Get the Campaign names and pass each name to the PRINT_TO_FILE procedure
       p_query := 'select distinct( case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
                  else campaign end) as campaign from fl.lead
				  where lead_id in ( select lead_id from ' ||new_table||' ) and
				  lead_id not in ( select lead_id from fl.remove )
				  order by 1';

	   dbms_sql.parse( l_theCursor,  p_query, dbms_sql.native );

	   dbms_sql.define_column( l_theCursor, 1, columnValue, 4000 );

	   l_status := dbms_sql.execute(l_theCursor);

	   loop
	        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
			dbms_sql.column_value( l_theCursor, 1, columnValue );
			l_cnt := l_cnt + 1;
       	    PRINT_TO_FILE(columnValue, new_table);
	   end loop;
	   dbms_sql.close_cursor(l_theCursor);

END CREATE_LIST;

-- Export the records to a comma delimited file.
Procedure PRINT_TO_FILE(campaign in varchar2,new_table in varchar2) 
AS
      l_campaign		    varchar2(30);
	p_dir  		    varchar2(30) := 'CELLIT';
	p_filename 	          varchar2(30) default null;
	p_separator     	    varchar2(1) default ',';
      l_separator           varchar2(10) default '';
	l_colCnt              number default 0;
      l_output              utl_file.file_type;
      l_theCursor           integer default dbms_sql.open_cursor;
      l_status              integer;
	l_columnValue         varchar2(4000);
      l_cnt                 number default 0;
	l_date		    varchar2(4) default null;
      p_query  	   	    varchar2(4000);

BEGIN
   l_campaign := campaign;

        p_filename := ''||campaign||'_'||to_char(sysdate,'mmddyy')||'.csv';

   		l_output := utl_file.fopen( p_dir, p_filename, 'w',32000 );

		IF campaign = 'WELLSCARGO'
		THEN
			p_query :='select case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
		    else campaign end as campaign,last_name as lname,first_name as fname,
		    substr( home_phone, 1, 3 ) as areac,
		    substr( home_phone, 4, 3 ) as prefix,
		    substr( home_phone, 7, 4 ) as telroot,
		    address, city, state, zip
			from fl.lead
			where substr( campaign, 1, 1 ) = ''W''
			and lead_id in ( select lead_id from ' ||new_table||' ) and
			lead_id not in ( select lead_id from fl.remove )
			order by 1';
		ELSE
			p_query :='select case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
		    else campaign end as campaign,last_name as lname,first_name as fname,
		    substr( home_phone, 1, 3 ) as areac,
		    substr( home_phone, 4, 3 ) as prefix,
		    substr( home_phone, 7, 4 ) as telroot,
		    address, city, state, zip
			from fl.lead
			where campaign ='''||campaign||'''
			and lead_id in ( select lead_id from ' ||new_table||' ) and
			lead_id not in ( select lead_id from fl.remove )
			order by 1';
		END IF;

	    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
		for i in 1 .. 255 loop
	        begin
	            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
	            l_colCnt := i;

	        exception
	            when others then
	                if ( sqlcode = -1007 ) then exit;
	                else
	                    raise;
	                end if;
	        end;
	    end loop;

	    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 4000 );

	    l_status := dbms_sql.execute(l_theCursor);

	    loop
	        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
			l_separator := '';
	        for i in 1 .. l_colCnt loop
	            dbms_sql.column_value( l_theCursor, i, l_columnValue );
	            utl_file.put(l_output, l_separator || l_columnValue);
			l_separator := p_separator;
	        end loop;
	        utl_file.new_line( l_output );
	        l_cnt := l_cnt+1;
	    end loop;

	    dbms_sql.close_cursor(l_theCursor);

	    utl_file.fclose( l_output);


END PRINT_TO_FILE;

END DNC_EXPORT;
/