CREATE OR REPLACE PROCEDURE VERIFICA_CARD_PADRAO(
   P_CDFILIAL   IN CARDADIA.CDFILIAL%TYPE,
   P_DTMESCARD  IN CARDADIA.DTMESCARD%TYPE,
   P_NRLISTA    IN LISTA.NRLISTA%TYPE)

IS
   V_IDUTCARDPADR VARCHAR2(1);
   V_IDCENTRAL VARCHAR2(1);
BEGIN
   SELECT MAX(IDUTCARDPADR)
      INTO V_IDUTCARDPADR
   FROM PARAMGERAL;
   IF (V_IDUTCARDPADR = 'S') THEN

      SELECT MAX(IDFILCENTPME)
         INTO V_IDCENTRAL
      FROM FILIAL
      WHERE CDFILIAL = P_CDFILIAL;

      IF (V_IDCENTRAL <> 'S') THEN
         RETURN;
      END IF;

      DELETE LOGCARDPADRAO
      WHERE CDFILIAL = P_CDFILIAL
        AND DTMESCARD = P_DTMESCARD;

      FOR C_SERVICO IN (
         SELECT CDLISTA AS CDSERVFILI
         FROM LISTA
         WHERE (NRLISTA = P_NRLISTA)
         ) LOOP

         /* GRAVAR A CONFIGURACAO DO SERVICO DAS FILIAIS AGREGADAS */
         FOR C_FILIAL_AGREG IN (
            SELECT DISTINCT F.CDFILIAL
            FROM FILIAL F, SERVICO S, CONFCARD C
            WHERE (F.CDFILCENTPME = P_CDFILIAL)           AND
                  (S.CDFILIAL     = F.CDFILIAL)           AND
                  (S.CDSERVFILI   = C_SERVICO.CDSERVFILI) AND
                  (C.CDFILIAL     = S.CDFILIAL)           AND
                  (C.CDSERVFILI   = S.CDSERVFILI)
         )LOOP


            INSERT INTO LOGCARDPADRAO(IDLOGCARDPAD, NRSEQLOGCARD, CDFILIAL,
                                      CDSERVFILI, DTMESCARD, CDFILIAGRE,
                                      DTCARDAPIO, CDPRATO, CDFILAUXPRAT,
                                      CDTIPOPRAT)
            (SELECT '1', ROWNUM, CA.CDFILIAL,
                    CA.CDSERVFILI, CA.DTMESCARD, C_FILIAL_AGREG.CDFILIAL,
                    CA.DTCARDAPIO, CP.CDPRATO, CP.CDFILAUXPRAT,
                    CP.CDTIPOPRAT
             FROM CARDADIA CA, COMPCARD CP, PRATO PR
             WHERE CA.CDFILIAL = P_CDFILIAL
               AND CA.CDSERVFILI = C_SERVICO.CDSERVFILI
               AND CA.DTMESCARD = P_DTMESCARD
               AND CP.CDFILIAL = CA.CDFILIAL
               AND CP.NRCARDAPIO = CA.NRCARDAPIO
               AND CP.CDFILAUXPRAT = P_CDFILIAL
               AND PR.CDPRATO = CP.CDPRATO
               AND PR.CDFILAUXPRAT = CP.CDFILAUXPRAT
               AND NOT EXISTS(SELECT PRATO.CDPRATO
                              FROM PRATO
                              WHERE PRATO.CDFILAUXPRAT = C_FILIAL_AGREG.CDFILIAL
                                AND PRATO.CDPRATO = CP.CDPRATO
                                AND UPPER(PRATO.NMPRATO) = UPPER(PR.NMPRATO)
                             )
            );

            FOR C_PRATO_NEXIST IN (
               SELECT DISTINCT CP.CDFILAUXPRAT, CP.CDPRATO
               FROM CARDADIA CA, COMPCARD CP, PRATO PR
               WHERE CA.CDFILIAL = P_CDFILIAL
                AND CA.CDSERVFILI = C_SERVICO.CDSERVFILI
                AND CA.DTMESCARD = P_DTMESCARD
                AND CP.CDFILIAL = CA.CDFILIAL
                AND CP.NRCARDAPIO = CA.NRCARDAPIO
                AND CP.CDFILAUXPRAT = P_CDFILIAL
                AND NOT EXISTS(SELECT PRATO.CDPRATO
                               FROM PRATO
                               WHERE PRATO.CDFILAUXPRAT = C_FILIAL_AGREG.CDFILIAL
                                 AND PRATO.CDPRATO = CP.CDPRATO)
                AND PR.CDPRATO = CP.CDPRATO
                AND PR.CDFILAUXPRAT = CP.CDFILAUXPRAT
            ) LOOP

               UPDATE LOGCARDPADRAO
               SET IDLOGCARDPAD = '0'
               WHERE CDFILIAL = P_CDFILIAL
                 AND CDSERVFILI = C_SERVICO.CDSERVFILI
                 AND DTMESCARD = P_DTMESCARD
                 AND CDFILIAGRE = C_FILIAL_AGREG.CDFILIAL
                 AND CDPRATO = C_PRATO_NEXIST.CDPRATO
                 AND CDFILAUXPRAT = C_PRATO_NEXIST.CDFILAUXPRAT;

            END LOOP;
         END LOOP;
      END LOOP;
   END IF;
   DELETE FROM LISTA
   WHERE NRLISTA = P_NRLISTA;
   COMMIT;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('VERIFICA_CARD_PADRAO','5.30.000'); END;
/

