DROP PACKAGE DWH.PKG_CALCOLO_SLA_FRANCHIGIA;

--
-- PKG_CALCOLO_SLA_FRANCHIGIA  (Package) 
--
CREATE OR REPLACE PACKAGE DWH.PKG_CALCOLO_SLA_FRANCHIGIA AS
/******************************************************************************
   NAME:       PKG_CALCOLO_SLA_FRANCHIGIA
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        11/02/2011             1. Created this package.
******************************************************************************/


  PROCEDURE CALCOLO_SLA0_RECIPIENT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA1_RECIPIENT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA2_RECIPIENT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA3_RECIPIENT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA0_DONOR (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA1_DONOR (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA2_DONOR (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA3_DONOR (dataRif IN DATE,cod_errore OUT NUMBER);

  PROCEDURE CALCOLO_SLA1_DONOR_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA2_DONOR_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA3_DONOR_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA1_REC_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA2_REC_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);
  PROCEDURE CALCOLO_SLA3_REC_VIRT (dataRif IN DATE,cod_errore OUT NUMBER);


  PROCEDURE CALCOLO_SLA_FRANCHIGIA(dataRif IN DATE);


END PKG_CALCOLO_SLA_FRANCHIGIA;
/



DROP PACKAGE BODY DWH.PKG_CALCOLO_SLA_FRANCHIGIA;

--
-- PKG_CALCOLO_SLA_FRANCHIGIA  (Package Body) 
--
CREATE OR REPLACE PACKAGE BODY DWH.PKG_CALCOLO_SLA_FRANCHIGIA as


procedure CALCOLO_SLA0_RECIPIENT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS

temp_data_in_att     DATE;
temp_data_fin_att    DATE;
temp_data_in_pic     DATE;
temp_data_fin_pic    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftempH CHAR(2);
min_fin_ftempH CHAR(2);
sec_fin_ftempH CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
ora_fin_file1 CHAR(2);
min_fin_file1 CHAR(2);
sec_fin_file1 CHAR(2);
ora_fin_fileH CHAR(2);
min_fin_fileH CHAR(2);
sec_fin_fileH CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA0_RECIPIENT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;
temp_data_in_fileRDA DATE;
temp_data_fin_fileRDA DATE;


CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi,
     q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
FROM(
  SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
  FROM
      mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_out.tipo_file = 1 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
    (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) 
  ) q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
  OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_attesi_hoc is
SELECT COUNT(*) AS eventiAttesi,
     q.recipientRete
    ,q.donatingRete
    ,q.donorEffettivo
    ,q.recipientEffettivo
FROM(
SELECT
     a.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,a.CODICE_OPERATORE_DONATING as donatingRete
    ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,a.codice_operatore_rec_eff as recipientEffettivo
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
FROM
    mnp.mnp_gestione_richiesta_rec a
  ,mnp.mnp_xml_richiesta_out rich_out
  ,mnp.mnp_xml_out xml_out
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_out.tipo_file = 9 AND
  rich_out.id_richiesta = a.id_richiesta AND
  xml_out.nome_file = rich_out.nome_file AND
  xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) 
  ) q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
  OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
    ,q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
    ,ack_out_pic.nome_file nomeFilePIC
  FROM
     mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
    ,mnp.mnp_xml_in xml_in
    ,mnp.mnp_xml_richiesta_in rich_in
    ,mnp.mnp_xml_ack_out ack_out_pic
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_out.tipo_file = 1 AND
    xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_in.tipo_file = 5 AND
    rich_in.id_richiesta = rich_out.id_richiesta AND
    xml_in.nome_file = rich_in.nome_file AND
    ack_out_pic.nome_file_riferito = rich_in.nome_file AND
    (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
    ack_out_pic.risultato = 'OK'
    ) q
WHERE
    ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
      OR
      to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA
    )
  AND to_date( SUBSTR( q.nomeFilePIC, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_pic AND temp_data_fin_pic
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
    ,q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
    ,ack_out_pic.nome_file nomeFilePIC
  FROM
     mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
    ,mnp.mnp_xml_in xml_in
    ,mnp.mnp_xml_richiesta_in rich_in
    ,mnp.mnp_xml_ack_out ack_out_pic
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_in.tipo_file = 9 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND 
    xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
    rich_in.id_richiesta = rich_out.id_richiesta AND
    xml_in.nome_file = rich_in.nome_file AND
    ack_out_pic.nome_file_riferito = rich_in.nome_file AND
    (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
    ack_out_pic.risultato = 'OK'
  ) q
WHERE
    ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
      OR
      to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA
    )
  AND to_date( SUBSTR( q.nomeFilePIC, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_pic AND temp_data_fin_pic
GROUP BY
  q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


BEGIN
  cod_errore := 0;

  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');
  -- Finestre temporale TINIZIALE
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;
  -- RICAVA FINESTRE TEMPORALI PER TIPO FILE 9 ATTIVAZIONE P.HOC
  SELECT  SUBSTR(T_FINALE_ACK, 1,2),  SUBSTR(T_FINALE_ACK, 4,2),  SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO    ora_fin_ftempH,min_fin_ftempH,sec_fin_ftempH,ora_fin_fileH,min_fin_fileH,sec_fin_fileH
  FROM    mnp.mnp_anag_finestre_temp_d147
  WHERE   TIPO_FILE = 9;

  -- Finestra temporale della presa in carico negativa
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 5;

  -- carico gli eventi attesi per STD/MVNO

  -- Setta l'istante iniziale e finale a ieriLAV, poichè il Tmax completamento per lo SLA0 è zero
  -- Tiniziale = giorno prima dell'osservazione (dataRif-1)a fine finestra invio Ack RDA
  -- Tfinale = Ack Recipient in risposta alla PIC (in uscita)
  -- In caso di mancanza di ACK in ingresso considero le finestre temporali dei file
  temp_data_in_att      := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
  temp_data_fin_att     := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),1);
  temp_data_in_fileRDA  := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),2);
  temp_data_fin_fileRDA := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),1);

  FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
    INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
      TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA0'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'R'
         ,mese_comp
          );
  END LOOP;

  -- carico gli eventi attesi per pHOC

  -- Setta l'istante iniziale e finale a ieriLAV, poichè il Tmax completamento per lo SLA0 è zero
  -- Tiniziale = Ack Donating a RDA Recipient (in ingresso)
  -- Tfinale = Ack Recipient i risposta alla PIC (in uscita)

  temp_data_in_att      := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),2);
  temp_data_fin_att     := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),1);
  temp_data_in_fileRDA  := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_fileH ||min_fin_fileH||sec_fin_fileH),'YYYYMMDDHH24MISS'),2);
  temp_data_in_fileRDA  := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_fileH ||min_fin_fileH||sec_fin_fileH),'YYYYMMDDHH24MISS'),1);


  FOR rec IN eventi_attesi_hoc LOOP
    BEGIN
      n_rec := n_rec +1;
      INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
        TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
        DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
      VALUES (
        'SLA0'
        ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
        ,rec.donorEffettivo
        ,rec.recipientEffettivo
        ,rec.donatingRete
        ,rec.recipientRete
        ,rec.eventiAttesi
        ,'R'
        ,mese_comp
      );

    EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
    WHEN dup_val_on_index THEN
      BEGIN
        -- lettura dati preesistenti
        SELECT EVENTI_ATTESI
        INTO   eventi
        FROM   MNP_DW_SLA_FRANCHIGIA_OUT
        WHERE  TIPO_EVENTO   = 'SLA0'
        AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
        AND    DONOR_EFFETTIVO = rec.donorEffettivo
        AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
        AND     DONOR_RETE = rec.donatingRete;

        eventi:= eventi + rec.eventiAttesi;

        UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
        SET EVENTI_ATTESI = eventi
        WHERE  TIPO_EVENTO   = 'SLA0'
        AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
        AND    DONOR_EFFETTIVO = rec.donorEffettivo
        AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
        AND     DONOR_RETE = rec.donatingRete;
      END;
    END;
  END  LOOP;

  -- aggiorno gli eventi in SLA per STD/MVNO

  -- calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
  temp_data_in_pic:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
  temp_data_fin_pic:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

  FOR rec IN eventi_in_SLA LOOP

    UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
    SET EVENTI_IN_SLA = rec.eventiSLA
    WHERE  TIPO_EVENTO   = 'SLA0'
      AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
      AND    DONOR_EFFETTIVO = rec.donorEffettivo
      AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
      AND     DONOR_RETE = rec.donatingRete;
  END LOOP;

  FOR rec IN eventi_in_SLA_hoc LOOP

    UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
    SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
    WHERE  TIPO_EVENTO   = 'SLA0'
      AND  DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
      AND  DONOR_EFFETTIVO = rec.donorEffettivo
      AND  RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
      AND  DONOR_RETE = rec.donatingRete;

  END LOOP;

  COMMIT;

  D_fine:=sysdate;
  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION WHEN OTHERS THEN
  if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
  end if;
  if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
  end if;
  if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
  end if;
  if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
  end if;
  cod_errore := 1;
  DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA0_RECIPIENT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
  RETURN;

END CALCOLO_SLA0_RECIPIENT;


procedure CALCOLO_SLA1_RECIPIENT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS

  temp_data_in_att     DATE;
  temp_data_fin_att    DATE;
  temp_data_in_val     DATE;
  temp_data_fin_val    DATE;
  ora_fin_ftemp1 CHAR(2);
  min_fin_ftemp1 CHAR(2);
  sec_fin_ftemp1 CHAR(2);
  ora_fin_ftempH CHAR(2);
  min_fin_ftempH CHAR(2);
  sec_fin_ftempH CHAR(2);
  ora_fin_ftemp2 CHAR(2);
  min_fin_ftemp2 CHAR(2);
  sec_fin_ftemp2 CHAR(2);
  ora_fin_file1 CHAR(2);
  min_fin_file1 CHAR(2);
  sec_fin_file1 CHAR(2);
  ora_fin_fileH CHAR(2);
  min_fin_fileH CHAR(2);
  sec_fin_fileH CHAR(2);
  mese_comp CHAR(7);
  n_rec integer := 0;
  proc_name constant varchar2(30) := 'CALCOLO_SLA1_RECIPIENT';
  D_ini DATE;
  D_fine DATE;
  eventi NUMBER;
  temp_data_in_fileRDA DATE;
  temp_data_fin_fileRDA DATE;


CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi,
     q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
FROM(
  SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
  FROM
     mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_out.tipo_file = 1 AND
    xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
    a.stato NOT IN (5,9) ) q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
  OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_attesi_hoc is
SELECT COUNT(*) AS eventiAttesi,
     q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
FROM(
  SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
  FROM
      mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_out.tipo_file = 9 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
    (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
    a.stato NOT IN (5,9) ) q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
  OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
    ,q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
    ,ack_out_val.nome_file nomeAckVal
FROM
    mnp.mnp_gestione_richiesta_rec a
   ,mnp.mnp_xml_richiesta_out rich_out
  ,mnp.mnp_xml_out xml_out
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_ack_out ack_out_val
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_out.tipo_file = 1 AND
  rich_out.id_richiesta = a.id_richiesta AND
  xml_out.nome_file = rich_out.nome_file AND
  xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
  xml_in.tipo_file = 2 AND
  rich_in.id_richiesta = rich_out.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out_val.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  ack_out_val.risultato = 'OK') q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
    OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
    AND to_date( SUBSTR( q.nomeAckVal, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
    ,q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFileRDA
    ,ack_out_val.nome_file nomeFileValAck
FROM
    mnp.mnp_gestione_richiesta_rec a
   ,mnp.mnp_xml_richiesta_out rich_out
  ,mnp.mnp_xml_out xml_out
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_ack_out ack_out_val
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_out.tipo_file = 9 AND
  rich_out.id_richiesta = a.id_richiesta AND
  xml_out.nome_file = rich_out.nome_file AND
  xml_in.tipo_file = 2 AND
  rich_in.id_richiesta = rich_out.id_richiesta AND
  xml_out.data_eff > trunc(temp_data_in_fileRDA) AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out_val.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  ack_out_val.risultato = 'OK' and
  ack_out_val.nome_file_riferito = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=2 --validazione
                           AND   y.NOME_FILE = x.NOME_FILE)
  ) q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
    OR to_date( SUBSTR( q.nomeFileRDA, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileRDA AND temp_data_fin_fileRDA)
  AND to_date( SUBSTR( q.nomeFileValAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


BEGIN
  cod_errore := 0;

  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');

  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;
  -- RICAVA FINESTRE TEMPORALI PER TIPO FILE 9 ATTIVAZIONE P.HOC
  SELECT  SUBSTR(T_FINALE_ACK, 1,2),  SUBSTR(T_FINALE_ACK, 4,2),  SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2),  SUBSTR(T_FINALE, 4,2),  SUBSTR(T_FINALE, 7,2)
  INTO    ora_fin_ftempH,min_fin_ftempH,sec_fin_ftempH,ora_fin_fileH,min_fin_fileH,sec_fin_fileH
  FROM    mnp.mnp_anag_finestre_temp_d147
  WHERE   TIPO_FILE = 9;

  -- per pic presa dal nome file out non sono previsti i 15 minuti
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE = 'AOM';



  -- carico gli eventi attesi per std/MVNO

  -- Setta l'istante iniziale e finale, considerando Tmax completamento per lo SLA1 = 1 ggLav
  -- Tiniziale = Ack Donating a RDA di due giorni prima (datarif-3) a fine finestra temporale
  -- Tfinale = Ack Donating a RDA di un giornio prima (datarif-2) a fine finestra temporale
  -- in assenza di Ack in ingresso considero l'invio del file RDA
  temp_data_in_att      := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
  temp_data_fin_att     := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
  temp_data_in_fileRDA  := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),3);
  temp_data_fin_fileRDA := dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),2);

  FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
    INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
    TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
    DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA1'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'R'
         ,mese_comp
          );
  END LOOP;

  -- carico gli eventi attesi per pHOC

  -- calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
  temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),3);
  temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),2);
  temp_data_in_fileRDA:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_fileH ||min_fin_fileH||sec_fin_fileH),'YYYYMMDDHH24MISS'),3);
  temp_data_fin_fileRDA:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_fileH ||min_fin_fileH||sec_fin_fileH),'YYYYMMDDHH24MISS'),2);


  FOR rec IN eventi_attesi_hoc LOOP
    BEGIN
      n_rec := n_rec +1;
      INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
        TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
        DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
      VALUES (
        'SLA1'
        ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
        ,rec.donorEffettivo
        ,rec.recipientEffettivo
        ,rec.donatingRete
        ,rec.recipientRete
        ,rec.eventiAttesi
        ,'R'
        ,mese_comp
      );

    EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
    WHEN dup_val_on_index
    THEN
      BEGIN
        -- lettura dati preesistenti
       SELECT EVENTI_ATTESI
        INTO   eventi
       FROM   MNP_DW_SLA_FRANCHIGIA_OUT
        WHERE  TIPO_EVENTO   = 'SLA1'
        AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
        AND    DONOR_EFFETTIVO = rec.donorEffettivo
        AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
        AND    DONOR_RETE = rec.donatingRete;

       eventi:= eventi + rec.eventiAttesi;


         UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
         SET EVENTI_ATTESI = eventi
          WHERE  TIPO_EVENTO   = 'SLA1'
          AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
          AND    DONOR_EFFETTIVO = rec.donorEffettivo
          AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
          AND     DONOR_RETE = rec.donatingRete;
      END;
    END;
  END  LOOP;

  --aggiorno gli eventi in SLA per std/MVNO

  --calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
  temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
  temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

  FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


 FOR rec IN eventi_in_SLA_hoc LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

  D_fine:=sysdate;
  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
    if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA1_RECIPIENT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA1_RECIPIENT;



procedure CALCOLO_SLA2_RECIPIENT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA2_RECIPIENT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  ,a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta_rec a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_in.tipo_file = 2 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  a.stato <> 7 AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_attesi_hoc is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
   mnp.mnp_gestione_richiesta_rec a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_in.tipo_file = 2 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  a.stato <> 7 AND
  substr(rich_in.richiestaxml,instr(rich_in.richiestaxml,'<STATO_RICHIESTA_NOTIFICA>')+ 26,1)='0' AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta_rec a
   ,mnp.mnp_xml_in xml_in_esp
  ,mnp.mnp_xml_richiesta_in rich_in_esp
  ,mnp.mnp_xml_ack_out ack_out_esp
  ,mnp.mnp_xml_in xml_in_val
  ,mnp.mnp_xml_richiesta_in rich_in_val
  ,mnp.mnp_xml_ack_out ack_out_val
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_in_esp.tipo_file = 6 AND
  rich_in_esp.id_richiesta = a.id_richiesta AND
  ack_out_esp.nome_file_riferito = rich_in_esp.nome_file AND
  xml_in_esp.nome_file = rich_in_esp.nome_file AND
  xml_in_val.tipo_file = 2 AND
  rich_in_val.id_richiesta = rich_in_esp.id_richiesta AND
  xml_in_val.nome_file = rich_in_val.nome_file AND
  ack_out_val.nome_file_riferito = rich_in_val.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  a.CODICE_OPERATORE_DONATING= xml_in_esp.MITTENTE AND
  ack_out_val.risultato = 'OK' and
  ack_out_esp.risultato = 'OK' and
  to_date( SUBSTR( ack_out_val.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val AND
  to_date( SUBSTR( ack_out_esp.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM mnp.mnp_gestione_richiesta_rec a
   ,mnp.mnp_xml_in xml_in_esp
  ,mnp.mnp_xml_richiesta_in rich_in_esp
  ,mnp.mnp_xml_ack_out ack_out_esp
  ,mnp.mnp_xml_in xml_in_val
  ,mnp.mnp_xml_richiesta_in rich_in_val
  ,mnp.mnp_xml_ack_out ack_out_val
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_in_esp.tipo_file = 6 AND
  rich_in_esp.id_richiesta = a.id_richiesta AND
  ack_out_esp.nome_file_riferito = rich_in_esp.nome_file AND
  xml_in_esp.nome_file = rich_in_esp.nome_file AND
  xml_in_val.tipo_file = 2 AND
  rich_in_val.id_richiesta = rich_in_esp.id_richiesta AND
  xml_in_val.nome_file = rich_in_val.nome_file AND
  ack_out_val.nome_file_riferito = rich_in_val.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  a.CODICE_OPERATORE_DONATING= xml_in_esp.MITTENTE AND
  ack_out_val.risultato = 'OK' and
  ack_out_esp.risultato = 'OK' and
  to_date( SUBSTR( ack_out_val.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val AND
  to_date( SUBSTR( ack_out_esp.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp AND
  ack_out_val.NOME_FILE_riferito = (select max(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in_val.ID_RICHIESTA
                           AND   x.TIPO_FILE=2 --validazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;


    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE= 'AOM';


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



  --carico gli eventi attesi per std/MVNO

  --calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
  temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
  temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

  FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
    INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
      TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'R'
         ,mese_comp
          );
  END LOOP;

--carico gli eventi attesi per pHOC


   FOR rec IN eventi_attesi_hoc LOOP
   BEGIN
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'R'
         ,mese_comp
          );

EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
            WHEN dup_val_on_index
             THEN BEGIN
                  -- lettura dati preesistenti
                 SELECT EVENTI_ATTESI
                  INTO   eventi
                 FROM   MNP_DW_SLA_FRANCHIGIA_OUT
                  WHERE  TIPO_EVENTO   = 'SLA2'
                  AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                  AND    DONOR_EFFETTIVO = rec.donorEffettivo
                  AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                  AND     DONOR_RETE = rec.donatingRete;

                 eventi:= eventi + rec.eventiAttesi;


                   UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_ATTESI = eventi
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
              END;
END;
END  LOOP;

  --aggiorno gli eventi in SLA per std/MVNO

  --calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
  temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
  temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

  FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
  END LOOP;


  FOR rec IN eventi_in_SLA_hoc LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
  END LOOP;

  COMMIT;

  D_fine:=sysdate;
  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
    if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA2_RECIPIENT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;
END CALCOLO_SLA2_RECIPIENT;



procedure CALCOLO_SLA3_RECIPIENT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_por     DATE;
temp_data_fin_por    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
ora_fin_file1  CHAR(2);
min_fin_file1  CHAR(2);
sec_fin_file1  CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA3_RECIPIENT';
D_ini DATE;
D_fine DATE;
temp_data_in_file DATE;
temp_data_fin_file DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
    ,q.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,q.CODICE_OPERATORE_DONATING as donatingRete
    ,q.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,q.codice_operatore_rec_eff as recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT
    ,a.CODICE_OPERATORE_DONATING
    ,a.CODICE_OPERATORE_DON_EFF
    ,a.codice_operatore_rec_eff
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAck
    ,xml_out.nome_file nomeFilePorting
  FROM
      mnp.mnp_gestione_richiesta_rec a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    a.codice_operatore_rec_eff = 'TIMG' AND
    xml_out.tipo_file = 3 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_out.data_eff > trunc(temp_data_in_file) AND
    xml_out.destinatario <> a.codice_operatore_donating AND
    xml_out.destinatario <> 'NPTS') q
WHERE (to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR to_date( SUBSTR( q.nomeFilePorting, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_file AND temp_data_fin_file)
GROUP BY
   q.CODICE_OPERATORE_RECIPIENT, q.CODICE_OPERATORE_DONATING,q.CODICE_OPERATORE_DON_EFF, q.codice_operatore_rec_eff;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
    ,q.recipientRete
    ,q.donatingRete
    ,q.donorEffettivo
    ,q.recipientEffettivo
from
( SELECT
     a.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,xml_out_por.destinatario as donatingRete
    ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,a.codice_operatore_rec_eff as recipientEffettivo
    ,(
      Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out_por.nome_file
      and ack_in.risultato = 'OK'
     ) nomeFileAckPorting
    ,xml_out_por.nome_file nomeFilePorting
    ,ack_out_esp.NOME_FILE nomeFileAckEspl
FROM
    mnp.mnp_gestione_richiesta_rec a
   ,mnp.mnp_xml_in xml_in_esp
  ,mnp.mnp_xml_richiesta_in rich_in_esp
  ,mnp.mnp_xml_ack_out ack_out_esp
  ,mnp.mnp_xml_out xml_out_por
  ,mnp.mnp_xml_richiesta_out rich_out_por
WHERE
  a.codice_operatore_rec_eff = 'TIMG' AND
  xml_in_esp.tipo_file = 6 AND
  rich_in_esp.id_richiesta = a.id_richiesta AND
  ack_out_esp.nome_file_riferito = rich_in_esp.nome_file AND
  xml_in_esp.nome_file = rich_in_esp.nome_file AND
  xml_out_por.tipo_file = 3 AND
  xml_out_por.data_eff > trunc(temp_data_in_file) AND
  rich_out_por.id_richiesta = rich_in_esp.id_richiesta AND
  xml_out_por.nome_file = rich_out_por.nome_file AND
  xml_out_por.destinatario= xml_in_esp.MITTENTE AND
  xml_in_esp.mittente <> a.codice_operatore_donating AND
  xml_in_esp.mittente <> 'NPTS' AND
  ack_out_esp.risultato = 'OK' ) q
WHERE
  (to_date( SUBSTR( q.nomeFileAckPorting, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR
   to_date( SUBSTR( q.nomeFilePorting, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_file AND temp_data_fin_file )
    AND
  to_date( SUBSTR( q.nomeFileAckEspl, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;


BEGIN
  cod_errore := 0;

  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 3;


-- per pic presa dal nome file out non sono previsti i 15 minuti
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
temp_data_in_file:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_file:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),2);


   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA3'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'R'
         ,mese_comp
          );
  END LOOP;



--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA3'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA3_RECIPIENT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA3_RECIPIENT;



procedure CALCOLO_SLA0_DONOR(dataRif IN DATE,cod_errore   OUT      NUMBER) IS

temp_data_in_att     DATE;
temp_data_fin_att    DATE;
temp_data_in_pic     DATE;
temp_data_fin_pic    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftempH CHAR(2);
min_fin_ftempH CHAR(2);
sec_fin_ftempH CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA0_DONOR';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  xml_in.tipo_file = 1 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  xml_in.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=1 --attvazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_attesi_hoc is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  xml_in.tipo_file = 9 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  xml_in.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=9 --attvazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;

CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out_att
  ,mnp.mnp_xml_out xml_out_pic
  ,mnp.mnp_xml_richiesta_out rich_out
WHERE
  xml_in.tipo_file = 1 AND
  rich_in.id_richiesta = a.id_richiesta AND
  ack_out_att.nome_file_riferito = rich_in.nome_file AND
  xml_in.nome_file = rich_in.nome_file AND
  xml_out_pic.tipo_file = 5 AND
  rich_out.id_richiesta = rich_in.id_richiesta AND
  xml_out_pic.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  ack_out_att.risultato = 'OK' and
  to_date( SUBSTR( ack_out_att.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  to_date( SUBSTR( xml_out_pic.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_pic AND temp_data_fin_pic AND
  xml_in.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=1 --attvazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          ) AND
  xml_out_pic.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_OUT x,  mnp.MNP_XML_RICHIESTA_OUT y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=5 --pic
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out_att
  ,mnp.mnp_xml_out xml_out_pic
  ,mnp.mnp_xml_richiesta_out rich_out
WHERE
  xml_in.tipo_file = 9 AND
  rich_in.id_richiesta = a.id_richiesta AND
  ack_out_att.nome_file_riferito = rich_in.nome_file AND
  xml_in.nome_file = rich_in.nome_file AND
  xml_out_pic.tipo_file = 5 AND
  rich_out.id_richiesta = rich_in.id_richiesta AND
  xml_out_pic.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  ack_out_att.risultato = 'OK' and
  to_date( SUBSTR( ack_out_att.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  to_date( SUBSTR( xml_out_pic.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_pic AND temp_data_fin_pic  AND
  xml_in.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_IN x,  mnp.MNP_XML_RICHIESTA_IN y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=9 --attvazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )  AND
  xml_out_pic.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_OUT x,  mnp.MNP_XML_RICHIESTA_OUT y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=5 --pic
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;
   ora_fin_ftempH := null;
   min_fin_ftempH := null;
   sec_fin_ftempH := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;
  -- RICAVA FINESTRE TEMPORALI PER TIPO FILE 9 ATTIVAZIONE P.HOC
  SELECT  SUBSTR(T_FINALE_ACK, 1,2),  SUBSTR(T_FINALE_ACK, 4,2),  SUBSTR(T_FINALE_ACK, 7,2)
  INTO    ora_fin_ftempH,min_fin_ftempH,sec_fin_ftempH
  FROM    mnp.mnp_anag_finestre_temp_d147
  WHERE   TIPO_FILE = 9;

-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 5;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),1);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA0'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );
  END LOOP;

--carico gli eventi attesi per pHOC

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),1);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),1);


   FOR rec IN eventi_attesi_hoc LOOP
   BEGIN
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
    VALUES (
          'SLA0'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );

EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
            WHEN dup_val_on_index
             THEN BEGIN
                  -- lettura dati preesistenti
                 SELECT EVENTI_ATTESI
                  INTO   eventi
                 FROM   MNP_DW_SLA_FRANCHIGIA_OUT
                  WHERE  TIPO_EVENTO   = 'SLA0'
                  AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                  AND    DONOR_EFFETTIVO = rec.donorEffettivo
                  AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                  AND     DONOR_RETE = rec.donatingRete;

                 eventi:= eventi + rec.eventiAttesi;


                   UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_ATTESI = eventi
                    WHERE  TIPO_EVENTO   = 'SLA0'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
              END;
END;
END  LOOP;

--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_pic:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_pic:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA0'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


 FOR rec IN eventi_in_SLA_hoc LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA0'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
    if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA0_DONOR ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA0_DONOR;



procedure CALCOLO_SLA1_DONOR(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_att     DATE;
temp_data_fin_att    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftempH CHAR(2);
min_fin_ftempH CHAR(2);
sec_fin_ftempH CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA1_DONOR';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  xml_in.tipo_file = 1 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  a.stato <> 4 AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_attesi_hoc is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  xml_in.tipo_file = 9 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  a.stato <> 4 AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;

CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out_att
  ,mnp.mnp_xml_out xml_out_val
  ,mnp.mnp_xml_richiesta_out rich_out
WHERE
  xml_in.tipo_file = 1 AND
  rich_in.id_richiesta = a.id_richiesta AND
  ack_out_att.nome_file_riferito = rich_in.nome_file AND
  xml_in.nome_file = rich_in.nome_file AND
  xml_out_val.tipo_file = 2 AND
  rich_out.id_richiesta = rich_in.id_richiesta AND
  xml_out_val.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  ack_out_att.risultato = 'OK' and
  to_date( SUBSTR( ack_out_att.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  to_date( SUBSTR( xml_out_val.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out_att
  ,mnp.mnp_xml_out xml_out_val
  ,mnp.mnp_xml_richiesta_out rich_out
WHERE
  xml_in.tipo_file = 9 AND
  rich_in.id_richiesta = a.id_richiesta AND
  ack_out_att.nome_file_riferito = rich_in.nome_file AND
  xml_in.nome_file = rich_in.nome_file AND
  xml_out_val.tipo_file = 2 AND
  rich_out.id_richiesta = rich_in.id_richiesta AND
  xml_out_val.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  ack_out_att.risultato = 'OK' and
  to_date( SUBSTR( ack_out_att.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_att AND temp_data_fin_att AND
  to_date( SUBSTR( xml_out_val.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val AND
  xml_out_val.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_OUT x,  mnp.MNP_XML_RICHIESTA_OUT y
                           WHERE y.ID_RICHIESTA= rich_in.ID_RICHIESTA
                           AND   x.TIPO_FILE=2 --validazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING,a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;


BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;
   ora_fin_ftempH := null;
   min_fin_ftempH := null;
   sec_fin_ftempH := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;
  -- RICAVA FINESTRE TEMPORALI PER TIPO FILE 9 ATTIVAZIONE P.HOC
  SELECT  SUBSTR(T_FINALE_ACK, 1,2),  SUBSTR(T_FINALE_ACK, 4,2),  SUBSTR(T_FINALE_ACK, 7,2)
  INTO    ora_fin_ftempH,min_fin_ftempH,sec_fin_ftempH
  FROM    mnp.mnp_anag_finestre_temp_d147
  WHERE   TIPO_FILE = 9;

-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE = 'AOM';



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA1'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );
  END LOOP;

--carico gli eventi attesi per pHOC

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),3);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftempH ||min_fin_ftempH||sec_fin_ftempH),'YYYYMMDDHH24MISS'),2);


   FOR rec IN eventi_attesi_hoc LOOP
   BEGIN
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
    VALUES (
          'SLA1'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );

EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
            WHEN dup_val_on_index
             THEN BEGIN
                  -- lettura dati preesistenti
                 SELECT EVENTI_ATTESI
                  INTO   eventi
                 FROM   MNP_DW_SLA_FRANCHIGIA_OUT
                  WHERE  TIPO_EVENTO   = 'SLA1'
                  AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                  AND    DONOR_EFFETTIVO = rec.donorEffettivo
                  AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                  AND     DONOR_RETE = rec.donatingRete;

                 eventi:= eventi + rec.eventiAttesi;


                   UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_ATTESI = eventi
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
              END;
END;
END  LOOP;

--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


 FOR rec IN eventi_in_SLA_hoc LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
 END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
    if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA1_DONOR ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA1_DONOR;


procedure CALCOLO_SLA2_DONOR(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
ora_fin_file1  CHAR(2);
min_fin_file1 CHAR(2);
sec_fin_file1 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA2_DONOR';
D_ini DATE;
D_fine DATE;
temp_data_in_fileval DATE;
temp_data_fin_fileval DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT 
COUNT(*) AS eventiAttesi
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
SELECT
  a.CODICE_OPERATORE_RECIPIENT as recipientRete
  ,a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
  ,xml_out.NOME_FILE NomeFileVal
  ,(
    Select min(ack_in.nome_file)
    from mnp_xml_ack_in ack_in
    where 
    ack_in.nome_file_riferito=xml_out.nome_file
    and ack_in.risultato = 'OK'
    ) nomefileack
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_out rich_out
  ,mnp.mnp_xml_out xml_out
WHERE
  xml_out.tipo_file = 2 AND
  xml_out.data_eff > trunc(temp_data_in_fileval) AND
  rich_out.id_richiesta = a.id_richiesta AND
  xml_out.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 ) AND
  a.stato <> 10 
) q
WHERE
  ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
    OR
    to_date( SUBSTR( q.NomeFileVal, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileval AND temp_data_fin_fileval
  )
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;


CURSOR eventi_attesi_hoc is
SELECT 
COUNT(*) AS eventiAttesi
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
SELECT
  a.CODICE_OPERATORE_RECIPIENT as recipientRete
  ,a.CODICE_OPERATORE_DONATING as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
  ,xml_out.NOME_FILE NomeFileVal
  ,(
    Select min(ack_in.nome_file)
    from mnp_xml_ack_in ack_in
    where 
    ack_in.nome_file_riferito=xml_out.nome_file
    and ack_in.risultato = 'OK'
    ) nomefileack
FROM
    mnp.mnp_gestione_richiesta a
  ,mnp.mnp_xml_richiesta_out rich_out
  ,mnp.mnp_xml_out xml_out
WHERE
  xml_out.tipo_file = 2 AND
  xml_out.data_eff > trunc(temp_data_in_fileval) AND
  rich_out.id_richiesta = a.id_richiesta AND
  xml_out.nome_file = rich_out.nome_file AND
  (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
  a.stato <> 10 
) q
WHERE
  ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
    OR
    to_date( SUBSTR( q.NomeFileVal, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileval AND temp_data_fin_fileval
  )
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;

CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT
    a.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,a.CODICE_OPERATORE_DONATING as donatingRete
    ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,a.codice_operatore_rec_eff as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out_val.nome_file
      and ack_in.risultato = 'OK') nomeFileAck
    ,xml_out_val.NOME_FILE NomeFileVal
    ,xml_out_esp.NOME_FILE NomeFileEspl
  FROM
      mnp.mnp_gestione_richiesta a
    ,mnp.mnp_xml_richiesta_out rich_out_esp
    ,mnp.mnp_xml_richiesta_out rich_out_val
    ,mnp.mnp_xml_out xml_out_esp
    ,mnp.mnp_xml_out xml_out_val
  WHERE
    xml_out_esp.tipo_file = 6 AND
    xml_out_esp.data_eff > trunc(temp_data_in_esp) AND
    rich_out_esp.id_richiesta = a.id_richiesta AND
    rich_out_esp.nome_file=xml_out_esp.nome_file AND
    xml_out_val.nome_file = rich_out_val.nome_file AND
    xml_out_val.data_eff > trunc(temp_data_in_fileval) AND
    xml_out_val.tipo_file = 2 AND
    rich_out_val.id_richiesta = rich_out_esp.id_richiesta AND
    xml_out_esp.DESTINATARIO <> 'NPTS' AND
    (a.richiestaAdHocAom = 0 and a.richiestaAdHoc = 0 )
    ) q
WHERE
  ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
    OR
    to_date( SUBSTR( q.NomeFileVal, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileval AND temp_data_fin_fileval
  )AND
  to_date( SUBSTR( q.NomeFileEspl, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;


CURSOR eventi_in_sla_hoc is
SELECT COUNT(*) AS eventiSLA
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT
    a.CODICE_OPERATORE_RECIPIENT as recipientRete
    ,a.CODICE_OPERATORE_DONATING as donatingRete
    ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
    ,a.codice_operatore_rec_eff as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out_val.nome_file
      and ack_in.risultato = 'OK') nomeFileAck
    ,xml_out_val.NOME_FILE NomeFileVal
    ,xml_out_esp.NOME_FILE NomeFileEspl
  FROM
       mnp.mnp_gestione_richiesta a
    ,mnp.mnp_xml_richiesta_out rich_out_esp
    ,mnp.mnp_xml_richiesta_out rich_out_val
    ,mnp.mnp_xml_out xml_out_esp
    ,mnp.mnp_xml_out xml_out_val
  WHERE
    xml_out_esp.tipo_file = 6 AND
    xml_out_esp.data_eff > trunc(temp_data_in_esp) AND
    rich_out_esp.id_richiesta = a.id_richiesta AND
    rich_out_esp.nome_file=xml_out_esp.nome_file AND
    xml_out_val.nome_file = rich_out_val.nome_file AND
    xml_out_val.tipo_file = 2 AND
    xml_out_val.data_eff > trunc(temp_data_in_fileval) AND
    rich_out_val.id_richiesta = rich_out_esp.id_richiesta AND
    xml_out_esp.DESTINATARIO <> 'NPTS' AND
    (a.richiestaAdHocAom > 0 or a.richiestaAdHoc > 0 ) AND
    xml_out_val.Nome_File = (select max(X.NOME_FILE)
                           FROM mnp.MNP_XML_OUT x,  mnp.MNP_XML_RICHIESTA_OUT y
                           WHERE y.ID_RICHIESTA= rich_out_val.ID_RICHIESTA
                           AND   x.TIPO_FILE=2 --validazione
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
    ) q
WHERE
  ( to_date( SUBSTR( q.nomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_val AND temp_data_fin_val
    OR
    to_date( SUBSTR( q.NomeFileVal, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileval AND temp_data_fin_fileval
  )
  AND
  to_date( SUBSTR( q.NomeFileEspl, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp  
GROUP BY
   q.recipientRete, q.donatingRete,q.donorEffettivo, q.recipientEffettivo;


BEGIN
  cod_errore := 0;

  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE = 'AOM';


-- per pic presa dal nome file out non sono previsti i 15 minuti
  SELECT  SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
temp_data_in_fileval:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_fileval:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );
  END LOOP;

--carico gli eventi attesi per pHOC



   FOR rec IN eventi_attesi_hoc LOOP
   BEGIN
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO, MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );

EXCEPTION -- potrebbe essere gi¿ presente un record per progetti std/mvno. In questo caso devo sommare
            WHEN dup_val_on_index
             THEN BEGIN
                  -- lettura dati preesistenti
                 SELECT EVENTI_ATTESI
                  INTO   eventi
                 FROM   MNP_DW_SLA_FRANCHIGIA_OUT
                  WHERE  TIPO_EVENTO   = 'SLA2'
                  AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                  AND    DONOR_EFFETTIVO = rec.donorEffettivo
                  AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                  AND     DONOR_RETE = rec.donatingRete;

                 eventi:= eventi + rec.eventiAttesi;


                   UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_ATTESI = eventi
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
              END;
END;
END  LOOP;

--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


 FOR rec IN eventi_in_SLA_hoc LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = EVENTI_IN_SLA + rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_attesi_hoc%ISOPEN then CLOSE eventi_attesi_hoc;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
    if eventi_in_sla_hoc%ISOPEN then CLOSE eventi_in_sla_hoc;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA2_DONOR ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;
END CALCOLO_SLA2_DONOR;


procedure CALCOLO_SLA3_DONOR(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_por     DATE;
temp_data_fin_por    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA3_DONOR';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
    mnp.mnp_gestione_richiesta_porting a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
WHERE
  xml_in.tipo_file = 3 AND
  rich_in.id_richiesta = a.id_richiesta AND
  xml_in.nome_file = rich_in.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;




CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,a.CODICE_OPERATORE_RECIPIENT as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DON_EFF as donorEffettivo
  ,a.codice_operatore_rec_eff as recipientEffettivo
FROM
   mnp.mnp_gestione_richiesta_porting a
  ,mnp.mnp_xml_richiesta_in rich_in
  ,mnp.mnp_xml_in xml_in
  ,mnp.mnp_xml_ack_out ack_out
  ,mnp.mnp_xml_richiesta_out rich_out_esp
  ,mnp.mnp_xml_out xml_out_esp
WHERE
  xml_out_esp.tipo_file = 6 AND
  rich_out_esp.id_richiesta = a.id_richiesta AND
  rich_out_esp.nome_file=xml_out_esp.nome_file AND
  ack_out.nome_file_riferito = rich_in.nome_file AND
  xml_in.nome_file = rich_in.nome_file AND
  xml_in.tipo_file = 3 AND
  rich_in.id_richiesta = rich_out_esp.id_richiesta AND
  xml_out_esp.DESTINATARIO <> 'NPTS' AND
  ack_out.risultato = 'OK' and
  to_date( SUBSTR( ack_out.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por AND
  to_date( SUBSTR( xml_out_esp.NOME_FILE, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp AND
  xml_out_esp.NOME_FILE = (select min(X.NOME_FILE)
                           FROM mnp.MNP_XML_OUT x,  mnp.MNP_XML_RICHIESTA_OUT y
                           WHERE y.ID_RICHIESTA= rich_out_esp.ID_RICHIESTA
                           AND   x.TIPO_FILE=6 --espletamento
                           AND   y.NOME_FILE = x.NOME_FILE
                          )
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DON_EFF, a.codice_operatore_rec_eff;




BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;


    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 3;


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA3'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'D'
         ,mese_comp
          );
  END LOOP;


--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di espl da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA3'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
   END LOOP;


   COMMIT;

   D_fine:=sysdate;
   DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;

      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA3_DONOR ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;
END CALCOLO_SLA3_DONOR;


procedure CALCOLO_SLA1_DONOR_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_att     DATE;
temp_data_fin_att    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA1_DONOR_VIRT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
   mnp.MNP_GEST_RICH_DONOR_VIRT a
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto
WHERE
  a.id_richiesta = sto.id_richiesta AND
  sto.stato_a = 2 AND
  sto.data_i_o BETWEEN temp_data_in_att AND temp_data_fin_att
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
   ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
 mnp.MNP_GEST_RICH_DONOR_VIRT a
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto_in
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto_fin
WHERE
  a.id_richiesta = sto_in.id_richiesta AND
  a.id_richiesta = sto_fin.id_richiesta AND
  sto_in.stato_a = 2 AND
  sto_fin.stato_a IN (6,7) AND
  sto_in.data_i_o BETWEEN temp_data_in_att AND temp_data_fin_att AND
  sto_fin.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;




BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE = 'MVNO';



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA1'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;


--aggiorno gli eventi in SLA

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA1_DON_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA1_DONOR_VIRT;

procedure CALCOLO_SLA2_DONOR_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA2_DONOR_VIRT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
   mnp.MNP_GEST_RICH_DONOR_VIRT a
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto
WHERE
  a.id_richiesta = sto.id_richiesta AND
  sto.stato_a = 6 AND
  sto.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
   ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
 mnp.MNP_GEST_RICH_DONOR_VIRT a
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto_in
  ,mnp.MNP_STORICO_RICH_DON_VIRT sto_fin
WHERE
  a.id_richiesta = sto_in.id_richiesta AND
  a.id_richiesta = sto_fin.id_richiesta AND
  sto_in.stato_a = 6 AND
  sto_fin.stato_a =10 AND
  sto_in.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val AND
  sto_fin.data_i_o BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;




BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2  AND TIPO_OPERATORE = 'MVNO';


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;

--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;


--aggiorno gli eventi in SLA

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA2_DON_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA2_DONOR_VIRT;


procedure CALCOLO_SLA3_DONOR_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_por     DATE;
temp_data_fin_por    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
ora_fin_file1 CHAR(2);
min_fin_file1 CHAR(2);
sec_fin_file1 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA3_DONOR_VIRT';
D_ini DATE;
D_fine DATE;
temp_data_in_fileport DATE;
temp_data_fin_fileport DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT 'TIMG' as recipientRete
    ,xml_out.destinatario as donatingRete
    ,a.CODICE_OPERATORE_DONATING as donorEffettivo
    ,a.codice_operatore_recipient as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK') NomeFileACK
    ,xml_out.NOME_FILE NomeFilePort
  FROM
      mnp.mnp_gest_rich_donor_virt a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    xml_out.tipo_file = 3 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_out.data_eff > trunc(temp_data_in_fileport) AND 
    xml_out.destinatario not in('TIMG','NPTS')
    ) q
WHERE
    to_date( SUBSTR( q.NomeFileACK, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR
    to_date( SUBSTR( q.NomeFilePort, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileport AND temp_data_fin_fileport
GROUP BY
   q.recipientEffettivo, q.recipientRete, q.donatingRete,q.donorEffettivo;



CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT 'TIMG' as recipientRete
    ,xml_out_por.destinatario as donatingRete
    ,a.CODICE_OPERATORE_DONATING as donorEffettivo
    ,a.CODICE_OPERATORE_RECIPIENT as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out_por.nome_file
      and ack_in.risultato = 'OK') NomeFileACK
    ,xml_out_por.NOME_FILE NomeFilePort
    ,ack_out_esp.NOME_FILE NomeFileAckEspl
  FROM
      mnp.mnp_gest_rich_donor_virt a
     ,mnp.mnp_xml_in xml_in_esp
    ,mnp.mnp_xml_richiesta_in rich_in_esp
    ,mnp.mnp_xml_ack_out ack_out_esp
    ,mnp.mnp_xml_out xml_out_por
    ,mnp.mnp_xml_richiesta_out rich_out_por
  WHERE
    xml_in_esp.tipo_file = 6 AND
    rich_in_esp.id_richiesta = a.id_richiesta AND
    ack_out_esp.nome_file_riferito = rich_in_esp.nome_file AND
    xml_in_esp.nome_file = rich_in_esp.nome_file AND
    xml_out_por.tipo_file = 3 AND
    xml_out_por.data_eff > trunc(temp_data_in_fileport) AND
    rich_out_por.id_richiesta = rich_in_esp.id_richiesta AND
    xml_out_por.nome_file = rich_out_por.nome_file AND
    xml_out_por.destinatario= xml_in_esp.MITTENTE AND
    xml_in_esp.mittente <> a.codice_operatore_donating AND
    xml_in_esp.mittente <> 'NPTS' AND
    ack_out_esp.risultato = 'OK') q
WHERE
  ( to_date( SUBSTR( q.NomeFileACK, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR
    to_date( SUBSTR( q.NomeFilePort, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_fileport AND temp_data_fin_fileport
  ) AND
  to_date( SUBSTR( q.NomeFileAckEspl, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   q.recipientEffettivo, q.recipientRete, q.donatingRete,q.donorEffettivo;




BEGIN
  cod_errore := 0;

  ora_fin_ftemp1 := null;
  min_fin_ftemp1 := null;
  sec_fin_ftemp1 := null;
  ora_fin_ftemp2 := null;
  min_fin_ftemp2 := null;
  sec_fin_ftemp2 := null;


  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 3;


-- per pic presa dal nome file out non sono previsti i 15 minuti
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
temp_data_in_fileport:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_fileport:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_file1 ||min_fin_file1||sec_fin_file1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA3'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;



--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA3'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA3_DON_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA3_DONOR_VIRT;




procedure CALCOLO_SLA1_REC_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_att     DATE;
temp_data_fin_att    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA1_REC_VIRT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
   mnp.MNP_GEST_RICH_REC_VIRT a
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto
WHERE
  a.id_richiesta = sto.id_richiesta AND
  sto.stato_a = 2 AND
  sto.data_i_o BETWEEN temp_data_in_att AND temp_data_fin_att
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
   ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
 mnp.MNP_GEST_RICH_REC_VIRT a
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto_in
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto_fin
WHERE
  a.id_richiesta = sto_in.id_richiesta AND
  a.id_richiesta = sto_fin.id_richiesta AND
  sto_in.stato_a = 2 AND
  sto_fin.stato_a IN (6,7) AND
  sto_in.data_i_o BETWEEN temp_data_in_att AND temp_data_fin_att AND
  sto_fin.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;




BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 1;


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2 AND TIPO_OPERATORE = 'MVNO';



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_att:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA1'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;


--aggiorno gli eventi in SLA

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA1'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA1_REC_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA1_REC_VIRT;


procedure CALCOLO_SLA2_REC_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_val     DATE;
temp_data_fin_val    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA2_REC_VIRT';
D_ini DATE;
D_fine DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
   mnp.MNP_GEST_RICH_REC_VIRT a
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto
WHERE
  a.id_richiesta = sto.id_richiesta AND
  sto.stato_a = 6 AND
  sto.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
   ,'TIMG' as recipientRete
  , 'TIMG' as donatingRete
  ,a.CODICE_OPERATORE_DONATING as donorEffettivo
  ,a.codice_operatore_recipient as recipientEffettivo
FROM
 mnp.MNP_GEST_RICH_REC_VIRT a
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto_in
  ,mnp.MNP_STORICO_RICH_REC_VIRT sto_fin
WHERE
  a.id_richiesta = sto_in.id_richiesta AND
  a.id_richiesta = sto_fin.id_richiesta AND
  sto_in.stato_a = 6 AND
  sto_fin.stato_a =10 AND
  sto_in.data_i_o BETWEEN temp_data_in_val AND temp_data_fin_val AND
  sto_fin.data_i_o BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   a.CODICE_OPERATORE_RECIPIENT, a.CODICE_OPERATORE_DONATING;




BEGIN
   cod_errore := 0;

   ora_fin_ftemp1 := null;
   min_fin_ftemp1 := null;
   sec_fin_ftemp1 := null;
   ora_fin_ftemp2 := null;
   min_fin_ftemp2 := null;
   sec_fin_ftemp2 := null;

    D_ini:=sysdate;

DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 2  AND TIPO_OPERATORE = 'MVNO';


-- per pic presa dal nome file out non sono previsti i 15 minuti
   SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_val:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA2'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;


--aggiorno gli eventi in SLA

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA2'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;

  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA2_REC_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA2_REC_VIRT;


procedure CALCOLO_SLA3_REC_VIRT(dataRif IN DATE,cod_errore   OUT      NUMBER) IS
temp_data_in_esp     DATE;
temp_data_fin_esp    DATE;
temp_data_in_por     DATE;
temp_data_fin_por    DATE;
ora_fin_ftemp1 CHAR(2);
min_fin_ftemp1 CHAR(2);
sec_fin_ftemp1 CHAR(2);
ora_fin_ftemp2 CHAR(2);
min_fin_ftemp2 CHAR(2);
sec_fin_ftemp2 CHAR(2);
ora_fin_file1  CHAR(2);
min_fin_file1  CHAR(2);
sec_fin_file1  CHAR(2);
mese_comp CHAR(7);
n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA3_REC_VIRT';
D_ini DATE;
D_fine DATE;
temp_data_in_filepor DATE;
temp_data_fin_filepor DATE;
eventi NUMBER;



CURSOR eventi_attesi is
SELECT COUNT(*) AS eventiAttesi
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT 'TIMG' as recipientRete
    ,xml_out.destinatario as donatingRete
    ,a.CODICE_OPERATORE_DONATING as donorEffettivo
    ,a.codice_operatore_recipient as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out.nome_file
      and ack_in.risultato = 'OK') NomeFileAck
    ,xml_out.NOME_FILE NomeFilePort
  FROM
      mnp.mnp_gest_rich_rec_virt a
    ,mnp.mnp_xml_richiesta_out rich_out
    ,mnp.mnp_xml_out xml_out
  WHERE
    xml_out.tipo_file = 3 AND
    rich_out.id_richiesta = a.id_richiesta AND
    xml_out.nome_file = rich_out.nome_file AND
    xml_out.data_eff > trunc(temp_data_in_filepor) AND
    xml_out.destinatario not in( 'TIMG', 'NPTS') ) q
WHERE
  ( to_date( SUBSTR( q.NomeFileAck, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR
    to_date( SUBSTR( q.NomeFilePort, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_filepor AND temp_data_fin_filepor
  )
GROUP BY
   q.recipientEffettivo, q.recipientRete, q.donatingRete,q.donorEffettivo;


CURSOR eventi_in_sla is
SELECT COUNT(*) AS eventiSLA
  ,q.recipientRete
  ,q.donatingRete
  ,q.donorEffettivo
  ,q.recipientEffettivo
from (
  SELECT 'TIMG' as recipientRete
    ,xml_out_por.destinatario as donatingRete
    ,a.CODICE_OPERATORE_DONATING as donorEffettivo
    ,a.codice_operatore_recipient as recipientEffettivo
    ,(Select min(ack_in.nome_file)
      from mnp_xml_ack_in ack_in
      where 
      ack_in.nome_file_riferito=xml_out_por.nome_file
      and ack_in.risultato = 'OK') NomeFileAckport
    ,xml_out_por.NOME_FILE NomeFilePort
    ,ack_out_esp.NOME_FILE NomeAckEspl
FROM
    mnp.mnp_gest_rich_rec_virt a
   ,mnp.mnp_xml_in xml_in_esp
  ,mnp.mnp_xml_richiesta_in rich_in_esp
  ,mnp.mnp_xml_ack_out ack_out_esp
  ,mnp.mnp_xml_out xml_out_por
  ,mnp.mnp_xml_richiesta_out rich_out_por
WHERE
  xml_in_esp.tipo_file = 6 AND
  rich_in_esp.id_richiesta = a.id_richiesta AND
  ack_out_esp.nome_file_riferito = rich_in_esp.nome_file AND
  xml_in_esp.nome_file = rich_in_esp.nome_file AND
  xml_out_por.tipo_file = 3 AND
  xml_out_por.data_eff > trunc(temp_data_in_filepor) AND
  rich_out_por.id_richiesta = rich_in_esp.id_richiesta AND
  xml_out_por.nome_file = rich_out_por.nome_file AND
  xml_out_por.destinatario= xml_in_esp.MITTENTE AND
  xml_in_esp.mittente <> a.codice_operatore_donating AND
  xml_in_esp.mittente <> 'NPTS' AND
  ack_out_esp.risultato = 'OK') q
WHERE
  ( to_date( SUBSTR( q.NomeFileAckport, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_por AND temp_data_fin_por
    OR
    to_date( SUBSTR( q.NomeFilePort, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_filepor AND temp_data_fin_filepor
  ) AND
  to_date( SUBSTR( q.NomeAckEspl, 5 , 14) ,'YYYYMMDDHH24MISS' ) BETWEEN temp_data_in_esp AND temp_data_fin_esp
GROUP BY
   q.recipientEffettivo, q.recipientRete, q.donatingRete,q.donorEffettivo;


BEGIN
  cod_errore := 0;

  D_ini:=sysdate;

  DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));

  /*il mese di competenza viene impostato sull'evento finale*/
  mese_comp:= TO_CHAR (dwh.FUN_GIORNI_MENO (dataRif,1),'mm/yyyy');


  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2),
    SUBSTR(T_FINALE, 1,2), SUBSTR(T_FINALE, 4,2), SUBSTR(T_FINALE, 7,2)
  INTO ora_fin_ftemp1,min_fin_ftemp1,sec_fin_ftemp1,ora_fin_file1,min_fin_file1,sec_fin_file1
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 3;


-- per pic presa dal nome file out non sono previsti i 15 minuti
  SELECT  SUBSTR(T_FINALE_ACK, 1,2), SUBSTR(T_FINALE_ACK, 4,2), SUBSTR(T_FINALE_ACK, 7,2)
  INTO ora_fin_ftemp2,min_fin_ftemp2,sec_fin_ftemp2
  FROM mnp.mnp_anag_finestre_temp_d147
  WHERE TIPO_FILE = 6;



--carico gli eventi attesi per std/MVNO

--calcolo l'intervallo di 24h dalla chiusura finestra del giorno precedente, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_por:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);
temp_data_in_filepor:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),3);
temp_data_fin_filepor:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp1 ||min_fin_ftemp1||sec_fin_ftemp1),'YYYYMMDDHH24MISS'),2);

   FOR rec IN eventi_attesi LOOP
    n_rec := n_rec +1;
     INSERT INTO MNP_DW_SLA_FRANCHIGIA_OUT (
     TIPO_EVENTO, DATA_EVENTO, DONOR_EFFETTIVO, RECIPIENT_EFFETTIVO,
      DONOR_RETE, RECIPIENT_RETE, EVENTI_ATTESI, PROCESSO,MESE_COMP_FINALE)
    VALUES (
          'SLA3'
         ,trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
         ,rec.donorEffettivo
         ,rec.recipientEffettivo
         ,rec.donatingRete
         ,rec.recipientRete
         ,rec.eventiAttesi
         ,'V'
         ,mese_comp
          );
  END LOOP;



--aggiorno gli eventi in SLA per std/MVNO

--calcolo l'intervallo di pic da inizio giornata, alla chiusura finestra del giorno corrente di determinazione eventi
temp_data_in_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||'00' ||'00'||'01'),'YYYYMMDDHH24MISS'),1);
temp_data_fin_esp:= dwh.FUN_GIORNI_MENO(TO_DATE((TO_CHAR (dataRif, 'YYYYMMDD')||ora_fin_ftemp2 ||min_fin_ftemp2||sec_fin_ftemp2),'YYYYMMDDHH24MISS'),1);

   FOR rec IN eventi_in_SLA LOOP

     UPDATE MNP_DW_SLA_FRANCHIGIA_OUT
                   SET EVENTI_IN_SLA = rec.eventiSLA
                    WHERE  TIPO_EVENTO   = 'SLA3'
                    AND    DATA_EVENTO = trunc(dwh.FUN_GIORNI_MENO (dataRif,1))
                    AND    DONOR_EFFETTIVO = rec.donorEffettivo
                    AND    RECIPIENT_EFFETTIVO   = rec.recipientEffettivo
                    AND     DONOR_RETE = rec.donatingRete;
    END LOOP;


  COMMIT;

   D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCEDURA '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440)||' - RECORDS TRATTATI: '||N_REC);

EXCEPTION
   WHEN OTHERS
   THEN
    if eventi_attesi%ISOPEN then CLOSE eventi_attesi;
    end if;
    if eventi_in_sla%ISOPEN then CLOSE eventi_in_sla;
    end if;
      cod_errore := 1;
      DBMS_OUTPUT.put_line ('Errore in DWH.CALCOLO_SLA3_REC_VIRT ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      RETURN;

END CALCOLO_SLA3_REC_VIRT;




procedure CALCOLO_SLA_FRANCHIGIA(dataRif IN Date) IS

  cod_errore  NUMBER (1);

  cod_errore_sla0_don  NUMBER (1):=0;
  cod_errore_sla1_don  NUMBER (1):=0;
  cod_errore_sla2_don  NUMBER (1):=0;
  cod_errore_sla3_don  NUMBER (1):=0;

  cod_errore_sla0_rec  NUMBER (1):=0;
  cod_errore_sla1_rec  NUMBER (1):=0;
  cod_errore_sla2_rec  NUMBER (1):=0;
  cod_errore_sla3_rec  NUMBER (1):=0;

  cod_errore_sla1_don_virt  NUMBER (1):=0;
  cod_errore_sla2_don_virt  NUMBER (1):=0;
  cod_errore_sla3_don_virt  NUMBER (1):=0;

  cod_errore_sla1_rec_virt  NUMBER (1):=0;
  cod_errore_sla2_rec_virt  NUMBER (1):=0;
  cod_errore_sla3_rec_virt  NUMBER (1):=0;



  n_rec integer := 0;
proc_name constant varchar2(30) := 'CALCOLO_SLA_FRANCHIGIA';
D_ini DATE;
D_fine DATE;
dataRif1 DATE;



BEGIN
--dbms_output.enable(9999999);
D_ini:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA: INIZIO PROCESSO '||PROC_NAME||' - '||TO_CHAR(D_INI,'YYYY-MM-DD HH24:MI:SS'));
-----------------------------------------------------
-- VERIFICO SE DATA_RIF E' FERIALE. SE E' FESTIVO NON FACCIO NULLA
-----------------------------------------------------
IF FUN_DATE_FESTIVE(dataRif) = dataRif
THEN BEGIN
   -----------------------------------------------------
   -- VOGLIO CALCOLARE TUTTI GLI EVENTI CHE FANNO RIFERIMENTO AL GIORNO LAVORATIVO ANTERIORE A DATA RIF
   -- EFFETTUO UNA DELETE PREVENTIVA DELLE TABELLA FRANCHIGIA PER LA GIORNATA DI INTERESSE
   -----------------------------------------------------
      dataRif1 := FUN_GIORNI_MENO (dataRif,1);
      DELETE MNP_DW_SLA_FRANCHIGIA_OUT WHERE TO_CHAR(DATA_EVENTO,'dd/mm/yyyy')=TO_CHAR(dataRif1,'dd/mm/yyyy');

  cod_errore := 0;


-- caricamento dati per il processo donor nella tabella MNP_DW_SLA_FRANCHIGIA_OUT
  calcolo_sla0_donor(dataRif,cod_errore_sla0_don);
  calcolo_sla1_donor(dataRif,cod_errore_sla1_don);
  calcolo_sla2_donor(dataRif,cod_errore_sla2_don);
  calcolo_sla3_donor(dataRif,cod_errore_sla3_don);

-- caricamento dati per il processo recipient nella tabella MNP_DW_SLA_FRANCHIGIA_OUT
  calcolo_sla0_recipient(dataRif,cod_errore_sla0_rec);
  calcolo_sla1_recipient(dataRif,cod_errore_sla1_rec);
  calcolo_sla2_recipient(dataRif,cod_errore_sla2_rec);
  calcolo_sla3_recipient(dataRif,cod_errore_sla3_rec);

-- caricamento dati per il processo donor virtuale nella tabella MNP_DW_SLA_FRANCHIGIA_OUT
  calcolo_sla1_donor_virt(dataRif,cod_errore_sla1_don_virt);
  calcolo_sla2_donor_virt(dataRif,cod_errore_sla2_don_virt);
  calcolo_sla3_donor_virt(dataRif,cod_errore_sla3_don_virt);

-- caricamento dati per il processo recipient virtuale nella tabella MNP_DW_SLA_FRANCHIGIA_OUT
  calcolo_sla1_rec_virt(dataRif,cod_errore_sla1_rec_virt);
  calcolo_sla2_rec_virt(dataRif,cod_errore_sla2_rec_virt);
  calcolo_sla3_rec_virt(dataRif,cod_errore_sla3_rec_virt);




 -----------------------------------------------------
   -- CALCOLO IL NUMERO 'EVENTI FUORI SLA' E A PERCENTUALE DI FRANCHIGIA PER
   -- TUTTI I RECORD INSERITI
   -----------------------------------------------------

  UPDATE dwh.mnp_dw_sla_franchigia_out
  SET
    eventi_fuori_sla = eventi_attesi - eventi_in_sla
    ,percentuale_fuori_sla = ceil(((eventi_attesi - eventi_in_sla)/eventi_attesi)*100)
    ,data_agg = sysdate
  WHERE
    data_agg is null;



  cod_errore := cod_errore_sla0_don + cod_errore_sla1_don+cod_errore_sla2_don +cod_errore_sla3_don    +
                cod_errore_sla0_rec + cod_errore_sla1_rec + cod_errore_sla2_rec + cod_errore_sla3_rec +
                cod_errore_sla1_rec_virt + cod_errore_sla2_rec_virt + cod_errore_sla3_rec_virt +
                cod_errore_sla1_don_virt + cod_errore_sla2_don_virt + cod_errore_sla3_don_virt;




    IF cod_errore = 0
   THEN
    -- tutto a posto
    DBMS_OUTPUT.put_line ('Caricamento dati e calcolo SLA avvenuto.');
    ELSE
    -- tutto na sola
     DBMS_OUTPUT.put_line ('CARICAMENTO DATI E CALCOLO SLA PARZIALE: PROCESSO TERMINATO CON ERRORI VERIFICARE LOG!');
   END IF;

  COMMIT;
  D_fine:=sysdate;
DBMS_OUTPUT.put_line ('PKG_CALCOLO_SLA_FRANCHIGIA:  FINE  PROCESSO '||PROC_NAME||' - '||TO_CHAR(D_FINE,'YYYY-MM-DD HH24:MI:SS')||' - DURATA IN MINUTI:'||TRUNC((D_fine-D_ini)*1440));


EXCEPTION
WHEN OTHERS THEN
  cod_errore := 1;
  DBMS_OUTPUT.put_line ('Errore nel caricamento dati e calcolo Sla ' || SQLERRM||' - '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
  RETURN;

  END;
  END IF;
END CALCOLO_SLA_FRANCHIGIA;



END PKG_CALCOLO_SLA_FRANCHIGIA;
/