CREATE OR REPLACE FUNCTION PERCENTUAL_ALT_CARD(
   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
   P_CDSERVFILI   IN SERVICO.CDSERVFILI%TYPE,
   P_CDPRATO      IN PRATO.CDPRATO%TYPE,
   P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
   P_DTMESCARD    IN DATE) RETURN NUMBER IS

   V_NRPRATOMAT NUMBER;
   V_NRPRATO    NUMBER;
   V_QTDEPRATO  NUMBER;
   V_RESULT     NUMBER;
BEGIN

   V_RESULT := 0;

   SELECT NVL((SELECT COUNT(1)
               FROM COMPCARD CP, CARDADIA CA
               WHERE CA.CDFILIAL = P_CDFILIAL
                 AND CA.CDSERVFILI = P_CDSERVFILI
                 AND CA.DTCARDAPIO BETWEEN P_DTMESCARD AND LAST_DAY(P_DTMESCARD)
                 AND CA.CDFILIAL = CP.CDFILIAL
                 AND CA.NRCARDAPIO = CP.NRCARDAPIO
                 AND CP.CDPRATOMAT = P_CDPRATO
                 AND CP.CDFILAUXPRATMAT = P_CDFILAUXPRAT
                 AND NOT EXISTS(SELECT X.CDPRATO
                               FROM COMPCARD X, CARDADIA Y
                               WHERE X.CDFILIAL = Y.CDFILIAL
                               AND X.NRCARDAPIO = Y.NRCARDAPIO
                               AND Y.CDFILIAL = CA.CDFILIAL
                               AND Y.CDSERVFILI = CA.CDSERVFILI
                               AND Y.DTCARDAPIO BETWEEN CA.DTMESCARD AND LAST_DAY(CA.DTMESCARD)
                               AND X.CDPRATOMAT = CP.CDPRATO
                               AND X.CDFILAUXPRAT = CP.CDFILAUXPRAT
                               AND X.CDPRATO = CP.CDPRATOMAT
                               )

               GROUP BY CP.CDPRATOMAT), 0)
            INTO V_NRPRATOMAT
   FROM DUAL;

   SELECT NVL((SELECT NVL(COUNT(1), 0) AS NRPRATO
               FROM COMPCARD CP, CARDADIA CA
               WHERE CA.CDFILIAL = P_CDFILIAL
                 AND CA.CDSERVFILI = P_CDSERVFILI
                 AND CA.DTCARDAPIO BETWEEN P_DTMESCARD AND LAST_DAY(P_DTMESCARD)
                 AND CA.CDFILIAL = CP.CDFILIAL
                 AND CA.NRCARDAPIO = CP.NRCARDAPIO
                 AND CP.CDPRATO = P_CDPRATO
                 AND CP.CDFILAUXPRAT = P_CDFILAUXPRAT
               GROUP BY CP.CDPRATO), 0)
            INTO V_NRPRATO
   FROM DUAL;

      IF (V_NRPRATOMAT > 0) AND (V_NRPRATO = 0) THEN
         V_RESULT := 100;
      ELSE
         V_QTDEPRATO := V_NRPRATOMAT + V_NRPRATO;
         IF (V_QTDEPRATO <> 0) THEN
            V_RESULT := (V_NRPRATOMAT * 100) / V_QTDEPRATO;
         ELSE
            V_RESULT := 0;
        END IF;
      END IF;
   RETURN(V_RESULT);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('PERCENTUAL_ALT_CARD','5.20.000'); END;
/