create or replace
FUNCTION E_AGREGADA( 
P_CDCENTRAL IN FILIAL.CDFILIAL%TYPE, 
P_CDFILIAL  IN FILIAL.CDFILIAL%TYPE, 
P_CDSERVFILI IN SERVICO.CDSERVFILI%TYPE, 
P_CDTIPOPRAT IN TIPOPRAT.CDTIPOPRAT%TYPE, 
P_DTPRODUCAO IN DATE) RETURN NUMBER 
AS 
V_FERIADO VARCHAR2(1); 
V_EXISTE VARCHAR2(1); 
V_AGREGADA VARCHAR2(1); 
 
BEGIN 
   SELECT SUM(NRO)  
     INTO V_FERIADO 
     FROM  
     (SELECT COUNT(*) NRO 
        FROM FERIFILI 
       WHERE CDFILIAL   = P_CDFILIAL 
         AND DTFERIFILI = P_DTPRODUCAO 
       UNION ALL 
       SELECT COUNT(*) NRO 
        FROM FERICARD 
       WHERE CDFILIAL   = P_CDFILIAL 
         AND DTFERICARD = P_DTPRODUCAO); 
          
   IF (V_FERIADO > 0) THEN 
     V_FERIADO := '8'; 
   ELSE 
     V_FERIADO := TO_CHAR(TO_DATE(P_DTPRODUCAO),'D'); 
   END IF; 
 
   SELECT COUNT(*) 
     INTO V_EXISTE 
     FROM CELULAPROD CP INNER JOIN SERVICO S ON ((CP.CDFILIAGREGA = S.CDFILIAL) AND (CP.CDSERVFILI = S.CDSERVFILI)) 
    WHERE (CP.CDCENTRAL    = P_CDCENTRAL) 
      AND (CP.CDFILIAGREGA = P_CDFILIAL) 
      AND (CP.CDTIPOPRAT   = P_CDTIPOPRAT) 
      AND (CP.CDSERVFILI   = P_CDSERVFILI) 
      AND (CP.DTPRODUCAO   = P_DTPRODUCAO) 
      AND (CP.NRDIASEMANA  = '9') 
      AND (S.IDSERVTRAN = 'S'); 
       
   IF (V_EXISTE > 0) THEN 
      RETURN 1; 
   ELSE 
    
      SELECT COUNT(*) 
        INTO V_EXISTE 
        FROM CELULAPROD CP INNER JOIN SERVICO S ON ((CP.CDFILIAGREGA = S.CDFILIAL)AND(CP.CDSERVFILI = S.CDSERVFILI)) 
       WHERE (CP.CDCENTRAL    = P_CDCENTRAL) 
         AND (CP.CDFILIAGREGA = P_CDFILIAL) 
         AND (CP.CDTIPOPRAT   = P_CDTIPOPRAT) 
         AND (CP.CDSERVFILI   = P_CDSERVFILI) 
         AND (CP.NRDIASEMANA  = V_FERIADO) 
         AND (S.IDSERVTRAN = 'S') 
         AND (CP.DTPRODUCAO   = (SELECT MAX(C.DTPRODUCAO) 
                                 FROM CELULAPROD C 
                                WHERE -- (C.CDCENTRAL     = P_CDCENTRAL) AND 
									  (C.CDFILIAGREGA  = P_CDFILIAL) 
                                  AND (C.CDTIPOPRAT    = P_CDTIPOPRAT) 
                                  AND (C.CDSERVFILI    = P_CDSERVFILI) 
                                  AND (C.NRDIASEMANA   = V_FERIADO) 
                                  AND (C.DTPRODUCAO   <= P_DTPRODUCAO))); 
      IF (V_EXISTE > 0) THEN 
         RETURN 1; 
      END IF; 
       
   END IF; 
    
   SELECT COUNT(*) 
     INTO V_AGREGADA 
     FROM FILIAL  
    WHERE CDFILIAL     = P_CDFILIAL AND 
          CDFILCENTPME = P_CDCENTRAL; 
           
           
   IF (V_AGREGADA > 0) THEN 
      SELECT COUNT(*) 
        INTO V_EXISTE 
        FROM CELULAPROD CP INNER JOIN SERVICO S ON ((CP.CDFILIAGREGA = S.CDFILIAL)AND(CP.CDSERVFILI = S.CDSERVFILI)) 
       WHERE (CP.CDCENTRAL    <> P_CDCENTRAL) 
         AND (CP.CDFILIAGREGA = P_CDFILIAL) 
         AND (CP.CDTIPOPRAT   = P_CDTIPOPRAT) 
         AND (CP.CDSERVFILI   = P_CDSERVFILI) 
         AND (CP.DTPRODUCAO   = P_DTPRODUCAO) 
         AND (CP.NRDIASEMANA  = '9') 
         AND (S.IDSERVTRAN = 'S'); 
         
      IF (V_EXISTE > 0) THEN 
         RETURN 0; 
      ELSE  
       
         SELECT COUNT(*) 
           INTO V_EXISTE 
           FROM CELULAPROD CP INNER JOIN SERVICO S ON ((CP.CDFILIAGREGA = S.CDFILIAL)AND(CP.CDSERVFILI = S.CDSERVFILI)) 
          WHERE (CP.CDCENTRAL    <> P_CDCENTRAL) 
            AND (CP.CDFILIAGREGA = P_CDFILIAL) 
            AND (CP.CDTIPOPRAT   = P_CDTIPOPRAT) 
            AND (CP.CDSERVFILI   = P_CDSERVFILI) 
            AND (CP.NRDIASEMANA  = V_FERIADO) 
            AND (S.IDSERVTRAN = 'S') 
            AND (CP.DTPRODUCAO   = (SELECT MAX(C.DTPRODUCAO) 
                                    FROM CELULAPROD C  
                                   WHERE (C.CDCENTRAL     <> P_CDCENTRAL) 
                                     AND (C.CDFILIAGREGA  = P_CDFILIAL) 
                                     AND (C.CDTIPOPRAT    = P_CDTIPOPRAT) 
                                     AND (C.CDSERVFILI    = P_CDSERVFILI) 
                                     AND (C.NRDIASEMANA   = V_FERIADO) 
                                     AND (C.DTPRODUCAO   <= P_DTPRODUCAO))); 
         IF (V_EXISTE > 0) THEN 
            RETURN 0; 
         ELSE 
            SELECT COUNT(*)
              INTO V_EXISTE
              FROM SERVICO 
             WHERE CDFILIAL = P_CDFILIAL AND
                   CDSERVFILI = P_CDSERVFILI AND
                   IDSERVTRAN = 'S';
            IF (V_EXISTE > 0) THEN 
               RETURN 1;
            ELSE
               RETURN 0;
            END IF; 
         END IF;  
      END IF; 
   END IF; 
    
   RETURN 0; 
END; 