82 lines
1.9 KiB
SQL
82 lines
1.9 KiB
SQL
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;
|