CREATE OR REPLACE PROCEDURE DELETA_PRATOS_CARDBASE
(
P_NRCARDAPIOBASE IN NUMBER,
P_DIAS IN VARCHAR2,
P_CONFIGURACAO IN VARCHAR2
)
AS
BEGIN   
   FOR C_DIA IN
   (
      SELECT NRCARDAPIOBASE, NRDIACARDBASE
        FROM DIACARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASE
         AND VALOR_NA_LISTA(TO_CHAR(NRDIACARDBASE),P_DIAS) > 0
   ) LOOP       
      --Inativa regras de adesao especificas do dia
      UPDATE ADESAOCARDBASE
         SET IDATIVO = 'N'
       WHERE NRCARDAPIOBASE = C_DIA.NRCARDAPIOBASE
         AND NRDIACARDBASE = C_DIA.NRDIACARDBASE;

      FOR C_CONFIG IN
      (
         SELECT NRCONFIGCARDBASE
           FROM CONFIGCARDBASE
          WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASE
            AND VALOR_NA_LISTA(TO_CHAR(NRCONFIGCARDBASE), P_CONFIGURACAO) > 0
      ) LOOP
         FOR C_COMP IN
         (
            SELECT NRCOMPCARDBASE
              FROM COMPCARDBASE
             WHERE NRCARDAPIOBASE = C_DIA.NRCARDAPIOBASE
               AND NRDIACARDBASE = C_DIA.NRDIACARDBASE
               AND NRCONFIGCARDBASE = C_CONFIG.NRCONFIGCARDBASE
         ) LOOP
            DELETE FROM REGIAOPRATCARDBASE
             WHERE NRPRATCOMPCARDBASE IN (SELECT NRPRATCOMPCARDBASE FROM PRATCOMPCARDBASE 
                                           WHERE NRCOMPCARDBASE = C_COMP.NRCOMPCARDBASE);

            DELETE FROM PRATCOMPCARDBASE
             WHERE NRCOMPCARDBASE = C_COMP.NRCOMPCARDBASE;


            DELETE FROM COMPCARDBASE
             WHERE NRCOMPCARDBASE = C_COMP.NRCOMPCARDBASE;
         END LOOP;
      END LOOP;
   END LOOP;
END;
/