SET serveroutput on DECLARE rs1 varchar2(255); rs2 varchar2(255); operatore varchar2(15); ope_don varchar2(15); dir_out varchar2(255); num NUMBER(15,0):=0; totale NUMBER(15,0):=0; file1 UTL_FILE.file_type; data_fine date; data date; p varchar2(15):='&1'; cod varchar2(15); cod1 varchar2(15); desc_olo varchar2(15); tot NUMBER(15,0):=0; count_tot NUMBER(15,0):=0; num1 number(15,0):=0; num2 number(15,0):=0; num3 number(15,0):=0; num4 number(15,0):=0; num5 number(15,0):=0; num6 number(15,0):=0; num7 number(15,0):=0; num8 number(15,0):=0; num9 number(15,0):=0; num10 number(15,0):=0; num11 number(15,0):=0; num12 number(15,0):=0; num13 number(15,0):=0; num14 number(15,0):=0; num15 number(15,0):=0; num16 number(15,0):=0; num17 number(15,0):=0; num18 number(15,0):=0; num19 number(15,0):=0; num20 number(15,0):=0; num21 number(15,0):=0; num22 number(15,0):=0; num23 number(15,0):=0; num24 number(15,0):=0; num25 number(15,0):=0; num26 number(15,0):=0; num27 number(15,0):=0; num28 number(15,0):=0; num29 number(15,0):=0; num30 number(15,0):=0; totale1 NUMBER(15,0):=0; totale2 NUMBER(15,0):=0; totale3 NUMBER(15,0):=0; totale4 NUMBER(15,0):=0; totale5 NUMBER(15,0):=0; totale6 NUMBER(15,0):=0; totale7 NUMBER(15,0):=0; totale8 NUMBER(15,0):=0; totale9 NUMBER(15,0):=0; totale10 NUMBER(15,0):=0; totale11 NUMBER(15,0):=0; totale12 NUMBER(15,0):=0; totale13 NUMBER(15,0):=0; totale14 NUMBER(15,0):=0; totale15 NUMBER(15,0):=0; totale16 NUMBER(15,0):=0; totale17 NUMBER(15,0):=0; totale18 NUMBER(15,0):=0; totale19 NUMBER(15,0):=0; totale20 NUMBER(15,0):=0; totale21 NUMBER(15,0):=0; totale22 NUMBER(15,0):=0; totale23 NUMBER(15,0):=0; totale24 NUMBER(15,0):=0; totale25 NUMBER(15,0):=0; totale26 NUMBER(15,0):=0; totale27 NUMBER(15,0):=0; totale28 NUMBER(15,0):=0; totale29 NUMBER(15,0):=0; totale30 NUMBER(15,0):=0; -- Cursore degli operatori per la riga dei donating CURSOR cur_operatori is SELECT desc_olo FROM mnp_olo_report where flag_attivo = 1 --and desc_olo <> 'TIMG' order by desc_olo; --Totale cumulativo numero di linee portate tra operatori non TIM CURSOR cur_risul(data_fine date,operatore_ varchar2) is select nvl(sum(a.num),0),a.cod,a.cod1,b.desc_olo from mnp_olo_report b, ( select count(*)num,codice_operatore_recipient cod,codice_operatore_donating cod1 from mnp_gestione_richiesta_porting grp, (SELECT desc_olo FROM mnp_olo_report where flag_attivo = 1) a where grp.stato in (4,6) and data_cut_over <= trunc(data_fine) and grp.codice_operatore_donating = a.desc_olo and grp.codice_operatore_recipient = operatore_ group by codice_operatore_recipient , codice_operatore_donating)a where a.cod1(+) = b.desc_olo and b.flag_attivo = 1 group by a.cod,a.cod1,b.desc_olo order by b.desc_olo; ------------------------------------------------------------------ -- fine dichiarazione dei cursori -- inizio procedura BEGIN if (p <> 'nullo')then data_fine := to_date('&1','dd/mm/yyyy'); dir_out:='&2'; else select donor_out into dir_out from mnp_olo_report where desc_olo = 'MONIT'; if (to_char(sysdate, 'DD')<= '15') then data := add_months((trunc(sysdate)), -1); data_fine := last_day(trunc(data)); else data_fine := to_date('15/'|| to_char(trunc(sysdate), 'mm/yyyy'), 'dd/mm/yyyy'); end if; end if; file1 := UTL_FILE.fopen (dir_out, 'TIM_to_AOM_TotaleNumeroLineePortate_'||to_char(sysdate,'yyyyMMdd')||'.csv', 'w'); BEGIN rs1 := ''; rs2 := ''; --ope_don := ''; tot := 0; num1 := 0; num2 := 0; num3 := 0; num4 := 0; num5 := 0; num6 := 0; num7 := 0; num8 := 0; num9 := 0; num10 := 0; num11 := 0; num12 := 0; num13 := 0; num14 := 0; num15 := 0; num16 := 0; num17 := 0; num18 := 0; num19 := 0; num20 := 0; num21 := 0; num22 := 0; num23 := 0; num24 := 0; num25 := 0; num26 := 0; num27 := 0; num28 := 0; num29 := 0; num30 := 0; totale := 0; totale1 := 0; totale2 := 0; totale3 := 0; totale4 := 0; totale5 := 0; totale6 := 0; totale7 := 0; totale8 := 0; totale9 := 0; totale10 := 0; totale11 := 0; totale12 := 0; totale13 := 0; totale14 := 0; totale15 := 0; totale16 := 0; totale17 := 0; totale18 := 0; totale19 := 0; totale20 := 0; totale21 := 0; totale22 := 0; totale23 := 0; totale24 := 0; totale25 := 0; totale26 := 0; totale27 := 0; totale28 := 0; totale29 := 0; totale30 := 0; count_tot := 0; UTL_FILE.put_line (file1,' '); UTL_FILE.put_line (file1,' '); UTL_FILE.put_line (file1,'Totale cumulativo numero di linee portate tra operatori non TIM'); UTL_FILE.put_line (file1,' '); UTL_FILE.put_line (file1,'Periodo fino al '|| trunc(data_fine)); UTL_FILE.put_line (file1,' '); UTL_FILE.put_line (file1,'Operatore Terza Parte TIM'); UTL_FILE.put_line (file1,' '); OPEN cur_operatori; LOOP FETCH cur_operatori INTO ope_don; EXIT WHEN cur_operatori%NOTFOUND; rs1:= rs1 ||';'|| ope_don ; END LOOP; CLOSE cur_operatori; rs1:=rs1 ||';'||'TOTALE'; UTL_FILE.put_line (file1,rs1); UTL_FILE.fflush (file1); exception when others then DBMS_OUTPUT.PUT_LINE(sqlerrm); END; begin rs1 := ''; rs2 := ''; OPEN cur_operatori; LOOP FETCH cur_operatori INTO ope_don; EXIT WHEN cur_operatori%NOTFOUND; OPEN cur_risul(data_fine,ope_don); LOOP FETCH cur_risul INTO num,cod,cod1,desc_olo; EXIT WHEN cur_risul%NOTFOUND; count_tot := count_tot + 1; rs2 := '' || rs2 || num ||';'; if (count_tot = 1) then num1:= num + num1; tot := num1 + tot; totale1:= totale1 + num1; end if; if (count_tot = 2) then num2:= num + num2; tot := num2 + tot; totale2:= totale2 + num2; end if; if (count_tot = 3) then num3:= num + num3; tot := num3 + tot; totale3:= totale3 + num3; end if; if (count_tot = 4) then num4:= num + num4; tot := num4 + tot; totale4:= totale4 + num4; end if; if (count_tot = 5) then num5:= num + num5; tot := num5 + tot; totale5:= totale5 + num5; end if; if (count_tot = 6) then num6:= num + num6; tot := num6 + tot; totale6:= totale6 + num6; end if; if (count_tot = 7) then num7:= num + num7; tot := num7 + tot; totale7:= totale7 + num7; end if; if (count_tot = 8) then num8:= num + num8; tot := num8 + tot; totale8:= totale8 + num8; end if; if (count_tot = 9) then num9:= num + num9; tot := num9 + tot; totale9:= totale9 + num9; end if; if (count_tot = 10) then num10:= num + num10; tot := num10 + tot; totale10:= totale10 + num10; end if; if (count_tot = 11) then num11:= num + num11; tot := num11 + tot; totale11:= totale11 + num11; end if; if (count_tot = 12) then num12:= num + num12; tot := num12 + tot; totale12:= totale12 + num12; end if; if (count_tot = 13) then num13:= num + num13; tot := num13 + tot; totale13:= totale13 + num13; end if; if (count_tot = 14) then num14:= num + num14; tot := num14 + tot; totale14:= totale14 + num14; end if; if (count_tot = 15) then num15:= num + num15; tot := num15 + tot; totale15:= totale15 + num15; end if; if (count_tot = 16) then num16:= num + num16; tot := num16 + tot; totale16:= totale16 + num16; end if; if (count_tot = 17) then num17:= num + num17; tot := num17 + tot; totale17:= totale17 + num17; end if; if (count_tot = 18) then num18:= num + num18; tot := num18 + tot; totale18:= totale18 + num18; end if; if (count_tot = 19) then num19:= num + num19; tot := num19 + tot; totale19:= totale19 + num19; end if; if (count_tot = 20) then num20:= num + num20; tot := num20 + tot; totale20:= totale20 + num20; end if; if (count_tot = 21) then num21:= num + num21; tot := num21 + tot; totale21:= totale21 + num21; end if; if (count_tot = 22) then num22:= num + num22; tot := num22 + tot; totale22:= totale22 + num22; end if; if (count_tot = 23) then num23:= num + num23; tot := num23 + tot; totale23:= totale23 + num23; end if; if (count_tot = 24) then num24:= num + num24; tot := num24 + tot; totale24:= totale24 + num24; end if; if (count_tot = 25) then num25:= num + num25; tot := num25 + tot; totale25:= totale25 + num25; end if; if (count_tot = 26) then num26:= num + num26; tot := num26 + tot; totale26:= totale26 + num26; end if; if (count_tot = 27) then num27:= num + num10; tot := num27 + tot; totale27:= totale27 + num27; end if; if (count_tot = 28) then num28:= num + num28; tot := num28 + tot; totale28:= totale28 + num28; end if; if (count_tot = 29) then num29:= num + num29; tot := num29 + tot; totale29:= totale29 + num29; end if; if (count_tot = 30) then num30:= num + num30; tot := num30 + tot; totale30:= totale30 + num30; end if; END LOOP; CLOSE cur_risul; -- DBMS_OUTPUT.PUT_LINE('count_tot è....' || count_tot ||'.... tot e....'|| tot ||'...desc_olo....'|| ope_don); UTL_FILE.put_line (file1,ope_don||';'|| rs2 || tot ||';'); UTL_FILE.fflush (file1); rs2 := ''; tot := 0; num1 := 0; num2 := 0; num3 := 0; num4 := 0; num5 := 0; num6 := 0; num7 := 0; num8 := 0; num9 := 0; num10 := 0; end loop; totale:= totale1+totale2+totale3+totale4+totale5+totale6+totale7+totale8+totale9+totale10+totale11+totale12+totale13+totale14+totale15+totale16+totale17+totale18+totale19+totale20+totale21+totale22+totale23+totale24+totale25+totale26+totale27+totale28+totale29+totale30; totale1 := totale1+totale6+totale11+totale16+totale21; totale2 := totale2+totale7+totale12+totale17+totale22; totale3 := totale3+totale8+totale13+totale18+totale23; totale4 := totale4+totale9+totale14+totale19+totale24; totale5 := totale5+totale10+totale15+totale20+totale25; UTL_FILE.put_line (file1,'TOTALE;'|| totale1 || ';'|| totale2 || ';'|| totale3 || ';'|| totale4 || ';'|| totale5 || ';'|| totale ||';'); CLOSE cur_operatori; UTL_FILE.fflush (file1); UTL_FILE.fclose (file1); exception when others then DBMS_OUTPUT.PUT_LINE(sqlerrm); END; end; / exit;