create or replace FUNCTION RETORNA_DIAS_REALIZADOS(  
    P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,  
    P_DTINICIAL    IN DATE,  
    P_DTFINAL      IN DATE,
    P_DTPROCESSA   IN VARCHAR2 DEFAULT 'N')RETURN NUMBER  
AS  
   V_CONTDIA         NUMBER;  
   V_DTINICIAL       DATE;  
   V_IDFDIAENTREST   VARCHAR2(1);  
   V_IDFDIASAIDEST   VARCHAR2(1);  
   V_IDFDIARETIEST   VARCHAR2(1);  
   V_IDFDIACARDAP    VARCHAR2(1);  
   V_IDFDIAREQEST    VARCHAR2(1);  
   V_IDFDIAREQPLA    VARCHAR2(1);  
   V_IDFDIAEFEFAT    VARCHAR2(1);  
   V_IDFDIAEFEPLA    VARCHAR2(1);  
   V_BOFDIA          BOOLEAN;  
   V_DTFERIFILI      DATE;  
   V_NRCARDAPIO      VARCHAR2(10);  
   V_LANCEENTR       VARCHAR2(10);  
   V_LANCESAID       VARCHAR2(10);  
   V_RETIRADA        VARCHAR2(10);  
   V_REQUISICAO      VARCHAR2(10);  
   V_SERVICO         VARCHAR2(5);  
   V_DTFINAL         DATE;
BEGIN  
   /*SE CALCULA ATE A DATA DE PROCESSAMENTO ATUALIZA VARIAVEL V_DTFINAL*/
    IF P_DTPROCESSA = 'S' THEN
       BEGIN
       SELECT TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE)))-1 
         INTO V_DTFINAL             
         FROM PARAMGERAL PG, PARAMFILIAL PF 
        WHERE PF.CDFILIAL = P_CDFILIAL;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DTFINAL := P_DTFINAL;
      END; 
      /*SE PERIODO ENGLOBA A DATA DE PROCESSAMENTO ENTAO A DATA FINAL E IGUAL A DATAPROC - 1*/
      IF NOT (P_DTINICIAL < V_DTFINAL AND P_DTFINAL > V_DTFINAL) THEN
         V_DTFINAL := P_DTFINAL;       
      END IF;
   ELSE
     V_DTFINAL := P_DTFINAL; 
   END IF;


   V_DTINICIAL  := P_DTINICIAL;  
   V_CONTDIA    := 0;  
         BEGIN  
            SELECT IDFDIAENTREST,IDFDIASAIDEST,IDFDIARETIEST,IDFDIACARDAP,  
                   IDFDIAREQEST, IDFDIAREQPLA, IDFDIAEFEFAT, IDFDIAEFEPLA  
              INTO V_IDFDIAENTREST,V_IDFDIASAIDEST,V_IDFDIARETIEST,V_IDFDIACARDAP,  
                   V_IDFDIAREQEST, V_IDFDIAREQPLA, V_IDFDIAEFEFAT, V_IDFDIAEFEPLA  
              FROM PARAMFILIAL  
             WHERE CDFILIAL = P_CDFILIAL;  
          EXCEPTION  
             WHEN NO_DATA_FOUND THEN BEGIN  
                V_IDFDIAENTREST := NULL;  
                V_IDFDIASAIDEST := NULL;  
                V_IDFDIARETIEST := NULL;  
                V_IDFDIACARDAP  := NULL;  
                V_IDFDIAREQEST  := NULL;  
                V_IDFDIAREQPLA  := NULL;  
                V_IDFDIAEFEFAT  := NULL;  
                V_IDFDIAEFEPLA  := NULL;  
             END;  
          END;  
   WHILE V_DTINICIAL <= V_DTFINAL LOOP  
         V_BOFDIA    := FALSE;  
         IF (V_IDFDIAENTREST = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
               SELECT MAX(NRLANCESTQ)  
                 INTO V_LANCEENTR  
                 FROM LANCTOESTOQ L  
                WHERE L.CDFILIMOVI   = P_CDFILIAL  
                  AND L.DTLANCESTQ   = V_DTINICIAL  
                  AND L.IDTPLANCTO  IN ('0','1','2','E');  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_LANCEENTR := NULL;  
               END;  
            END;  
            IF (V_LANCEENTR IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF (V_IDFDIASAIDEST = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
               SELECT MAX(NRLANCESTQ)  
                 INTO V_LANCESAID  
                 FROM LANCTOESTOQ L  
                WHERE L.CDFILIMOVI   = P_CDFILIAL  
                  AND L.DTLANCESTQ   = V_DTINICIAL  
                  AND L.IDTPLANCTO  IN ('0','1','4','S');  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_LANCESAID := NULL;  
               END;  
            END;  
            IF  (V_LANCESAID IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF (V_IDFDIARETIEST = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
                 SELECT MAX(NRLANCESTQ)  
                   INTO V_RETIRADA  
                   FROM  
                        (SELECT L.NRLANCESTQ  
                           FROM LANCTOESTOQ L, RETIRADA R  
                          WHERE L.CDFILIMOVI   = P_CDFILIAL  
                            AND L.DTLANCESTQ   = V_DTINICIAL  
                            AND L. IDTPLANCTO  = '3'  
                            AND L.CDFILIAL     = R.CDFILANCESTQ  
                            AND L.NRLANCESTQ   = R.NRLANCESTQ  
                          UNION ALL  
                         SELECT L.NRLANCESTQ  
                           FROM LANCTOESTOQ L, RETIRADAESTO R  
                          WHERE L.CDFILIMOVI   = P_CDFILIAL  
                            AND L.DTLANCESTQ   = V_DTINICIAL  
                            AND L. IDTPLANCTO  = '3'  
                            AND L.CDFILIAL     = R.CDFILIALLAN  
                            AND L.NRLANCESTQ   = R.NRLANCESTQ);  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_RETIRADA := NULL;  
               END;  
            END;  
            IF (V_RETIRADA IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF (V_IDFDIACARDAP = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
               SELECT MAX(NRCARDAPIO)  
                 INTO V_NRCARDAPIO  
                 FROM CARDADIA  
                WHERE CDFILIAL    = P_CDFILIAL  
                  AND DTCARDAPIO  = V_DTINICIAL;  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_NRCARDAPIO := NULL;  
               END;  
            END;  
            IF  (V_NRCARDAPIO IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF (V_IDFDIAREQEST = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
               SELECT MAX(NRREQUESTO)  
                 INTO V_REQUISICAO  
                 FROM REQUESTO R  
                WHERE R.CDFILIAL    = P_CDFILIAL  
                  AND R.DTREQUESTO  = V_DTINICIAL;  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_REQUISICAO := NULL;  
               END;  
            END;  
            IF (V_REQUISICAO IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         
         IF (V_IDFDIAEFEFAT = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
                SELECT MAX(CDSERVFILI)  
                  INTO V_SERVICO  
                  FROM  
                      (SELECT CDSERVFILI  
                        FROM EFECLIFAT  
                       WHERE CDFILIAL   = P_CDFILIAL  
                         AND DTFATURAM  = V_DTINICIAL  
                      UNION ALL  
                      SELECT CDSERVFILI  
                       FROM EFECLIFATTUR  
                      WHERE CDFILIAL     = P_CDFILIAL  
                        AND DTFATURAMTUR = V_DTINICIAL);  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_SERVICO := NULL;  
               END;  
            END;  
            IF (V_SERVICO IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF (V_IDFDIAEFEPLA = 'S') AND (V_BOFDIA = FALSE) THEN   
            BEGIN  
                SELECT MAX(CDSERVFILI)  
                  INTO V_SERVICO  
                  FROM EFETCLIE  
                 WHERE CDFILIAL     = P_CDFILIAL  
                   AND DTEFETCLIE   = V_DTINICIAL;  
            EXCEPTION  
               WHEN NO_DATA_FOUND THEN BEGIN  
                  V_SERVICO := NULL;  
               END;  
            END;  
            IF  (V_SERVICO IS NOT NULL) THEN  
               V_BOFDIA := TRUE;  
            END IF;  
         END IF;  
         IF V_BOFDIA THEN  
            V_CONTDIA := V_CONTDIA + 1;  
         END IF;  
      V_DTINICIAL := V_DTINICIAL + 1;  
   END LOOP;  
   RETURN(V_CONTDIA);  
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('RETORNA_DIAS_REALIZADOS','5.85.000'); END;
/