CREATE OR REPLACE PROCEDURE VALIDA_CARDAPIO_FECDIA(
   P_CDFILIAL       IN FILIAL.CDFILIAL%TYPE,
   P_DATAPROC       IN DATE,
   P_CDSERVDIFEFET OUT VARCHAR2,
   P_SEMEFETIVOS   OUT VARCHAR2,
   P_SEMRETIRADA   OUT VARCHAR2,
   P_NAOEVOLESTOQ  OUT VARCHAR2,
   P_SERVSEMRETI   OUT VARCHAR2)
AS

V_CDSERVDIFEFET  VARCHAR2(2000);

V_IDEFETCARDIA      VARCHAR2(1);
V_IDBLOFECHMES      VARCHAR2(1);
V_IDBLOFECHRETIEFET VARCHAR2(1);
V_IDBLOFERTNEV      VARCHAR2(1);
V_FATUSERV          VARCHAR2(1);
V_TEMRETIRADA       VARCHAR2(1);
V_SEMEFETIVOS       VARCHAR2(4);
V_IDBLOQEFEPDIFEFEF VARCHAR2(1);
V_IDCONSCOMFAT      VARCHAR2(1);
V_QTCOMEFETCLI      DOUBLE PRECISION;
V_IDEVOLESTOQ       VARCHAR2(2);
V_TOTALEFET         DOUBLE PRECISION;

BEGIN

   P_CDSERVDIFEFET := '';
   P_SEMEFETIVOS   := 'N';
   P_SEMRETIRADA   := '';
   P_NAOEVOLESTOQ  := '';
   P_SERVSEMRETI   := '';

   V_CDSERVDIFEFET  := '';

   V_IDCONSCOMFAT   := 'N';
   V_QTCOMEFETCLI   := 0;
   V_IDEVOLESTOQ    := 'S';
   V_TOTALEFET      := 0;
   V_SEMEFETIVOS    := 'N';
   V_IDEFETCARDIA   := 'N';
   V_IDBLOFECHMES   := 'N';
   V_IDBLOFERTNEV   := 'N';
   V_IDBLOQEFEPDIFEFEF := 'N';

   BEGIN
      SELECT NVL(IDEFETCARDIA,'N')
        INTO V_IDEFETCARDIA
        FROM PARAMPLANEJA
      WHERE (CDFILIAL = P_CDFILIAL);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDEFETCARDIA := 'N';
   END;

   BEGIN
      SELECT NVL(IDBLOFECHMES,'N'), NVL(IDBLOFERTNEV,'N'), NVL(IDBLOQEFEPDIFEFEF,'N'), NVL(IDBLOFECHRETIEFET, 'N')
        INTO V_IDBLOFECHMES, V_IDBLOFERTNEV, V_IDBLOQEFEPDIFEFEF, V_IDBLOFECHRETIEFET
        FROM PARAMFILIAL P, FILIAL F
       WHERE (P.CDFILIAL = P_CDFILIAL)  AND
             (P.CDFILIAL = F.CDFILIAL);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDBLOFECHMES := 'N';
         V_IDBLOFECHRETIEFET := 'N';
   END;

   IF NVL(V_IDEFETCARDIA,'N') = 'S' THEN
      BEGIN
         SELECT MAX(CA.CDFILIAL) AS SEMEFETIVOS
           INTO V_SEMEFETIVOS
         FROM
            SERVICO SE,
            CARDADIA CA
         WHERE
            (CA.CDFILIAL   = P_CDFILIAL)   AND
            (CA.DTCARDAPIO = P_DATAPROC) AND
            (SE.CDFILIAL   = CA.CDFILIAL)   AND
            (SE.CDSERVFILI = CA.CDSERVFILI) AND
            (SE.IDSERVPADR = 'N')           AND
            (QTCOMEFETCAR IS NULL)          AND
             EXISTS (SELECT NRCARDAPIO
                     FROM RETIRADA RE
                      WHERE (RE.CDFILIAL   = CA.CDFILIAL) AND
                            (RE.NRCARDAPIO = CA.NRCARDAPIO));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_SEMEFETIVOS := 'N';
         END;
   END IF;

   IF NVL(V_SEMEFETIVOS,'N') <> 'N' THEN
      P_SEMEFETIVOS := 'S';
      RETURN;
   ELSE
      
      IF(NVL(V_IDBLOFECHMES,'N') = 'S') THEN

         V_TEMRETIRADA := 'N';

         BEGIN
         SELECT MAX(CA.CDFILIAL) AS SEMEFETIVOS
           INTO V_SEMEFETIVOS
           FROM SERVICO SE, CARDADIA CA
          WHERE (CA.CDFILIAL   = P_CDFILIAL)   AND
                (CA.DTCARDAPIO = P_DATAPROC) AND
                (SE.CDFILIAL   = CA.CDFILIAL)   AND
                (SE.CDSERVFILI = CA.CDSERVFILI) AND
                (SE.IDSERVPADR = 'N')           AND
                (NVL(QTCOMEFETCAR,0) > 0);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_SEMEFETIVOS := 'N';
         END;
     

         BEGIN
            SELECT MAX('S')
              INTO V_TEMRETIRADA
              FROM RETIRADA
             WHERE (DTRETIRADA = P_DATAPROC)  AND
                  (CDFILIAL = P_CDFILIAL);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_TEMRETIRADA := 'N';
         END;
         IF NVL(V_TEMRETIRADA,'N') = 'N' AND ((V_IDBLOFECHRETIEFET = 'N') OR NVL(V_SEMEFETIVOS, 'N') <> 'N') THEN
            P_SEMRETIRADA := 'S';
            RETURN;
         END IF;
      END IF;
      
      FOR C_PLANEJADO IN (SELECT C.NRCARDAPIO, C.CDSERVFILI, NVL(C.QTCLIEFETCAR,0) AS QTCLIEFETCAR, NVL(IDSERVATIV,'N') AS IDSERVATIV
                            FROM CARDADIA C, SERVICO S
                           WHERE C.CDFILIAL   = P_CDFILIAL
                             AND C.DTCARDAPIO = P_DATAPROC
                             AND C.CDFILIAL   = S.CDFILIAL
                             AND C.CDSERVFILI = S.CDSERVFILI
                             AND S.IDTIPOSERV = 'N'
                             AND EXISTS (SELECT CDFILIAL, NRCARDAPIO
                                               FROM COMPCARD
                                               WHERE COMPCARD.CDFILIAL = C.CDFILIAL
                                                 AND COMPCARD.NRCARDAPIO = C.NRCARDAPIO)
                             AND ((NVL(C.QTCLIEFETCAR,0) > 0) OR (EXISTS(SELECT CDFILIAL FROM RETIRADA WHERE RETIRADA.CDFILIAL = C.CDFILIAL
                                                                                                         AND RETIRADA.NRCARDAPIO = C.NRCARDAPIO)))
                           ORDER BY C.CDSERVFILI) LOOP


         BEGIN
            SELECT MIN(NVL(C.IDEVOLESTOQ,'N')) 
              INTO V_IDEVOLESTOQ
              FROM RETIRADA R, ITEMRETI A, ITLANCTOEST C
             WHERE (R.CDFILIAL     = P_CDFILIAL)
               AND (C.CDFILIMOVI   = P_CDFILIAL)
               AND (R.NRCARDAPIO   = C_PLANEJADO.NRCARDAPIO)
               AND (R.CDFILIAL     = A.CDFILIAL)
               AND (R.NRRETIRADA   = A.NRRETIRADA)
               AND (R.NRCARDAPIO   = A.NRCARDAPIO)
               AND (A.CDFIITLANEST = C.CDFILIAL  )
               AND (A.NRLANCESTQ   = C.NRLANCESTQ)
               AND (A.NRSEQUITEM   = C.NRSEQUITEM);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDEVOLESTOQ := 'NR';  /*NAO LOCALIZOU A RETIRADA*/
         END;

         IF (V_IDEVOLESTOQ = 'N') AND (NVL(V_IDBLOFERTNEV,'N') = 'S') THEN
              P_NAOEVOLESTOQ := 'S';
              RETURN;
         ELSE

            IF (NVL(V_IDEVOLESTOQ,'NR') = 'NR') AND (C_PLANEJADO.IDSERVATIV = 'S') AND ((NVL(V_IDBLOFECHMES,'N') = 'S') AND (C_PLANEJADO.QTCLIEFETCAR > 0)) THEN
               P_SERVSEMRETI := 'S';
               RETURN;
            END IF;
         END IF;

         IF NVL(V_IDBLOQEFEPDIFEFEF,'N') = 'S' THEN


             SELECT NVL(IDCONSCOMFAT,'N')
               INTO V_IDCONSCOMFAT
               FROM SERVICO
              WHERE CDFILIAL = P_CDFILIAL
                AND CDSERVFILI = C_PLANEJADO.CDSERVFILI;

            SELECT NVL(IDFATUSERV,'S')
              INTO V_FATUSERV
              FROM SERVICO
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVFILI = C_PLANEJADO.CDSERVFILI;

            FOR C_EFETIVOS IN (SELECT CARDADIA.CDSERVFILI, NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI) AS CDSERVICFATURAM
                                 FROM CARDADIA, SERVIFAT, SERVICO
                                WHERE CARDADIA.CDFILIAL   = P_CDFILIAL
                                  AND CARDADIA.NRCARDAPIO = C_PLANEJADO.NRCARDAPIO
                                  AND CARDADIA.CDFILIAL   = SERVIFAT.CDFILIAL(+)
                                  AND CARDADIA.CDSERVFILI = SERVIFAT.CDSERVFILI(+)
                                  AND CARDADIA.CDFILIAL   = SERVICO.CDFILIAL
                                  AND SERVICO.CDSERVFILI  = NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI)
                                  ORDER BY CARDADIA.CDSERVFILI, NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI)) LOOP

                  SELECT NVL(SUM(NVL(E.QTCOMEFETCLI, 0) + NVL(E.QTVENDAEFETCLI, 0)),0)
                    INTO V_QTCOMEFETCLI
                    FROM SERVCLIE S, EFETCLIE E
                   WHERE S.CDFILIAL      = P_CDFILIAL
                     AND S.CDSERVFILI    = C_EFETIVOS.CDSERVICFATURAM
                     AND S.CDFILIAL      = E.CDFILIAL
                     AND S.CDSERVFILI    = E.CDSERVFILI
                     AND S.CDCLIENTE     = E.CDCLIENTE
                     AND E.DTEFETCLIE    = P_DATAPROC
                     AND E.CDSERVPLAN    = C_EFETIVOS.CDSERVFILI;

                  V_TOTALEFET := V_TOTALEFET + V_QTCOMEFETCLI;

            END LOOP;

            IF (V_TOTALEFET <> C_PLANEJADO.QTCLIEFETCAR) AND (V_FATUSERV = 'S') THEN
               V_CDSERVDIFEFET := V_CDSERVDIFEFET || '_' || C_PLANEJADO.CDSERVFILI;
            END IF;
         END IF;

         V_TOTALEFET    := 0;
         V_QTCOMEFETCLI := 0;
         V_IDEVOLESTOQ  := 'S';
      END LOOP;

      V_QTCOMEFETCLI   := 0;
      V_IDEVOLESTOQ    := 'S';
      V_TOTALEFET      := 0;

      FOR C_NAOPLANEJADO IN (SELECT C.NRCARDAPIO, C.CDSERVFILI, NVL(C.QTCLIEFETCAR,0) AS QTCLIEFETCAR
                               FROM CARDADIA C, SERVICO S, PARAMPLANEJA P
                              WHERE C.CDFILIAL   = P_CDFILIAL
                                AND C.DTCARDAPIO = P_DATAPROC
                                AND C.CDFILIAL   = S.CDFILIAL
                                AND C.CDSERVFILI = S.CDSERVFILI
                                AND S.IDTIPOSERV = 'N'
                                AND P.CDFILIAL = C.CDFILIAL
                                AND C.CDSERVFILI <> P.CDSERVRETPME
                                AND NOT EXISTS (SELECT CDFILIAL, NRCARDAPIO
                                                  FROM COMPCARD
                                                  WHERE COMPCARD.CDFILIAL = C.CDFILIAL
                                                    AND COMPCARD.NRCARDAPIO = C.NRCARDAPIO)
                                AND ((NVL(C.QTCLIEFETCAR,0) > 0) OR (EXISTS(SELECT CDFILIAL FROM RETIRADA WHERE RETIRADA.CDFILIAL = C.CDFILIAL
                                                                                                            AND RETIRADA.NRCARDAPIO = C.NRCARDAPIO)))

                           ORDER BY C.CDSERVFILI) LOOP

         BEGIN
               SELECT MIN(NVL(C.IDEVOLESTOQ,'N')) /*MIN PQ SE TIVER ALGUM REGISTRO COM N, RETORNA 'N', SE FOSSE MAX RETORNARIA 'S'*/
              INTO V_IDEVOLESTOQ
              FROM RETIRADA R, ITEMRETI A, ITLANCTOEST C
             WHERE (R.CDFILIAL     = P_CDFILIAL)
               AND (C.CDFILIMOVI   = P_CDFILIAL)
               AND (R.NRCARDAPIO   = C_NAOPLANEJADO.NRCARDAPIO)
               AND (R.CDFILIAL     = A.CDFILIAL)
               AND (R.NRRETIRADA   = A.NRRETIRADA)
               AND (R.NRCARDAPIO   = A.NRCARDAPIO)
               AND (A.CDFIITLANEST = C.CDFILIAL  )
               AND (A.NRLANCESTQ   = C.NRLANCESTQ)
               AND (A.NRSEQUITEM   = C.NRSEQUITEM);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDEVOLESTOQ := 'NR';  /*NAO LOCALIZOU A RETIRADA*/
         END;

         IF (V_IDEVOLESTOQ = 'N') AND (NVL(V_IDBLOFERTNEV,'N') = 'S')THEN /*O ESTOQUE NAO FOI EVOLUIDO*/
             P_NAOEVOLESTOQ := 'S';
             RETURN;
         END IF;

         IF (NVL(V_IDEVOLESTOQ,'NR') <> 'NR') AND (V_IDBLOQEFEPDIFEFEF = 'S') THEN

            SELECT NVL(IDCONSCOMFAT,'N')
              INTO V_IDCONSCOMFAT
              FROM SERVICO
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVFILI = C_NAOPLANEJADO.CDSERVFILI;

            SELECT NVL(IDFATUSERV,'S')
              INTO V_FATUSERV
              FROM SERVICO
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVFILI = C_NAOPLANEJADO.CDSERVFILI;

            FOR C_EFETIVOSNPLAN IN (SELECT CARDADIA.CDSERVFILI, NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI) AS CDSERVICFATURAM
                                 FROM CARDADIA, SERVIFAT, SERVICO
                                WHERE CARDADIA.CDFILIAL   = P_CDFILIAL
                                  AND CARDADIA.NRCARDAPIO = C_NAOPLANEJADO.NRCARDAPIO
                                  AND CARDADIA.CDFILIAL   = SERVIFAT.CDFILIAL(+)
                                  AND CARDADIA.CDSERVFILI = SERVIFAT.CDSERVFILI(+)
                                  AND CARDADIA.CDFILIAL   = SERVICO.CDFILIAL
                                  AND SERVICO.CDSERVFILI  = NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI)
                                  ORDER BY CARDADIA.CDSERVFILI, NVL(SERVIFAT.CDSERVICFATURAM, CARDADIA.CDSERVFILI)) LOOP

                  SELECT NVL(SUM(NVL(E.QTCOMEFETCLI, 0) + NVL(E.QTVENDAEFETCLI, 0)),0)
                    INTO V_QTCOMEFETCLI
                    FROM SERVCLIE S, EFETCLIE E
                   WHERE S.CDFILIAL      = P_CDFILIAL
                     AND S.CDSERVFILI    = C_EFETIVOSNPLAN.CDSERVICFATURAM
                     AND S.CDFILIAL      = E.CDFILIAL
                     AND S.CDSERVFILI    = E.CDSERVFILI
                     AND S.CDCLIENTE     = E.CDCLIENTE
                     AND E.DTEFETCLIE    = P_DATAPROC
                     AND E.CDSERVPLAN    = C_EFETIVOSNPLAN.CDSERVFILI;

                  V_TOTALEFET := V_TOTALEFET + V_QTCOMEFETCLI;

            END LOOP;

            IF (V_TOTALEFET <> C_NAOPLANEJADO.QTCLIEFETCAR) AND (V_FATUSERV = 'S') THEN
               V_CDSERVDIFEFET := V_CDSERVDIFEFET || '_' || C_NAOPLANEJADO.CDSERVFILI;
            END IF;

         END IF;

         V_TOTALEFET    := 0;
         V_QTCOMEFETCLI := 0;
         V_IDEVOLESTOQ  := 'S';
      END LOOP;      
   END IF;

   P_CDSERVDIFEFET  := V_CDSERVDIFEFET;
   RETURN;
END;
/