SET serveroutput on

DECLARE

   	

	operatore		varchar2(10);

	errore			exception;

	rich_mese_pre_prp	number(6);
	dir_out         varchar2(255);
	
 CURSOR cur_operatori is
  SELECT desc_olo, aom_out 
  FROM mnp_olo_report 
  where flag_attivo = 1
  and desc_olo not in ('TIMT', 'TIMG','BLUI')
  order by desc_olo;




BEGIN


	OPEN cur_operatori;
	
   LOOP
		FETCH cur_operatori INTO operatore, dir_out;
		EXIT WHEN cur_operatori%NOTFOUND;
		
		declare
		
		cod  		varchar2(23);
		msisdn 	varchar2(15);
		op_don 	varchar2(4);
		data		varchar2(15);
		file1    UTL_FILE.file_type;
		esito		varchar2(15);
  
		
		CURSOR cur_res is
  		select c.id_richiesta, c.msisdn, o.desc_olo, to_char(decode(c.data_cut_over_aom, null,c.data_cut_over_calc, c.data_cut_over_aom), 'DD/MM/YYYY'), 'Mancante'
  		from mnp_gestione_richiesta_rec c, mnp_olo o
  		where c.stato in(8,10,11)
  		and o.desc_olo = operatore
  		and c.espletamentoterzeparti like '%'|| o.cod_olo||'%'
  		and decode(c.data_cut_over_aom, null, c.data_cut_over_calc,c.data_cut_over_aom)< trunc(sysdate);
  		
		
		begin
		
		file1 := UTL_FILE.fopen (dir_out, 'TIMG_to_'|| upper(operatore)||'_MANCATENOTIFICHETERZEPARTI_'||to_char(sysdate,'yyyyMMdd')||'.csv', 'w');
		
      UTL_FILE.put_line (file1,'Codice richiesta; Msisdn; Operatore Donating; Data invio;Esito(mancante/KO);');
      UTL_FILE.put_line (file1,' ');
            
		OPEN cur_res;
   	LOOP
			FETCH cur_res INTO cod,msisdn,op_don,data,esito;
			EXIT WHEN cur_res%NOTFOUND;

			UTL_FILE.put_line (file1,cod ||';'|| msisdn ||';'|| op_don ||';'|| data ||';'|| esito ||';');
      END LOOP;
      CLOSE cur_res;
      UTL_FILE.fflush   (file1);
    	UTL_FILE.fclose   (file1);
      end; 	
   END LOOP;
   CLOSE cur_operatori;

	exception
   	--when errore then
    	--	DBMS_OUTPUT.PUT_LINE('La data inserita è null');
   	when others then
         DBMS_OUTPUT.PUT_LINE(sqlerrm);

END;
/
exit;