create or replace
PROCEDURE GERA_NOTAFISCAL_FECHAMENTO 
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE, 
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE, 
    P_DATAPROC   IN DATE                         DEFAULT NULL, 
    P_NRLANCESTQ IN LANCTOESTOQ.NRLANCESTQ%TYPE  DEFAULT NULL, 
    P_EVOLESTOQ  IN ITLANCTOEST.IDEVOLESTOQ%TYPE DEFAULT 'S', 
    P_CDOPERADOR IN LANCTOESTOQ.CDOPERADOR%TYPE  DEFAULT NULL,
    P_IDBLOQTRANS IN ITLANCTOEST.IDEVOLESTOQ%TYPE DEFAULT 'N',
    P_IDATUESTOQ IN ITLANCTOEST.IDEVOLESTOQ%TYPE DEFAULT 'S')
AS 
   V_CDEMPRESA    VARCHAR2(02); 
   V_CDINSCESTA   VARCHAR2(16); 
   V_CDTIPOOPER   VARCHAR2(02); 
   V_CDCLIENTE    VARCHAR2(21); 
   V_CDFORNECED   VARCHAR2(21); 
   V_CDFILIRECE   VARCHAR2(04); 
   V_CLIENTENF    VARCHAR2(21); 
   V_ORIGDESTNF   VARCHAR2(01); 
   V_CDFILIEMIT   VARCHAR2(04); 
   V_IDGERAVENC   VARCHAR2(01); 
   V_IDSISTEMA    VARCHAR2(03); 
   V_NRLANCTO     VARCHAR2(10); 
   V_IDATUESTTPOP VARCHAR2(01); 
   V_NRLANCESTQ   VARCHAR2(10); 
   V_DTINICIAL    DATE; 
   V_DTFINAL      DATE; 
   V_DTENTRSAID   DATE;
   V_VRNOTAFISC   NUMBER; 
   V_VRLIQUIDO    NUMBER; 
   V_DIFERENCA    NUMBER; 
   V_RESULT_DADOS_FIN VARCHAR2(50); 
   V_DADOADIC     VARCHAR2(03); 
   V_QTADIC       FLOAT; 
   V_RECADIC      VARCHAR2(4000);
   V_RECADIC2     VARCHAR2(4000);
   V_PRODREC      VARCHAR2(20);
   V_RECADICAUX   VARCHAR2(4000);
   V_TEMDADOS     VARCHAR2(255);
   V_COMPADIC     VARCHAR2(255);
   V_PRODANTE     VARCHAR2(30);
	V_IDUTCTRLSNCM VARCHAR2(3);
	V_COUNTPRODRSNCM NUMBER;
   V_COUNTPRVOL   INTEGER;
BEGIN
   V_NRLANCESTQ := P_NRLANCESTQ;
   SELECT MAX(N.CDEMPRESA),    MAX(N.CDINSCESTA),   MAX(N.CDTIPOOPER),
          MAX(N.CDCLIENTE),    MAX(N.CDFORNECED),   MAX(N.CDFILIRECE),
          MAX(N.DTENTRSAID),   MAX(N.IDORIGDESTNF), MAX(N.CDFILIEMIT),
          MAX(N.IDSISTEMA)
   INTO   V_CDEMPRESA,    V_CDINSCESTA,   V_CDTIPOOPER,
          V_CDCLIENTE,    V_CDFORNECED,   V_CDFILIRECE,
          V_DTENTRSAID,   V_ORIGDESTNF,   V_CDFILIEMIT,
          V_IDSISTEMA
   FROM NOTAFISCAL N
   WHERE N.CDFILIAL   = P_CDFILIAL   AND
         N.NRLANCTONF = P_NRLANCTONF;

	IF V_IDSISTEMA = 'EXP' THEN
/*
	--------------------------- Rotina: Rastreabilidade de medicamentos ---------------------------
*/

		BEGIN
			SELECT NVL(IDUTCTRLSNCM, 'NAO')
			  INTO V_IDUTCTRLSNCM
			  FROM FILIAL
			 WHERE CDFILIAL = V_CDFILIEMIT;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				V_IDUTCTRLSNCM := 'NAO';
		END;

		IF V_IDUTCTRLSNCM = 'SIM' THEN

			SELECT COUNT(1)
			  INTO V_COUNTPRODRSNCM
			  FROM ITEMPRODUTO IP
			 WHERE IP.CDFILIAL   = P_CDFILIAL
				AND IP.NRLANCTONF = P_NRLANCTONF
				AND EXISTS (SELECT 1
								  FROM INFADICPROD I
								 WHERE I.IDPRODRASTRSNCM = 'SIM'
									AND IP.CDPRODUTO      = I.CDPRODUTO);

			IF V_COUNTPRODRSNCM > 0 THEN
				UPDATE NOTAFISCAL
					SET IDNFDRASTRSNCM = 'SIM'
				 WHERE CDFILIAL   = P_CDFILIAL
					AND NRLANCTONF = P_NRLANCTONF;
			END IF;
		END IF;
/*
	--------------------------- Rotina: Calcula quantidade de volumes na nota ---------------------------
*/

      SELECT COUNT(1)
        INTO V_COUNTPRVOL
        FROM PRODUTO PR, ITEMPRODUTO IT
       WHERE PR.CDPRODUTO = IT.CDPRODUTO
         AND NRLANCTONF   = P_NRLANCTONF
         AND CDFILIAL     = P_CDFILIAL
         AND IDATUESTPROD = 'S'
         AND QTVOLUMES IS NOT NULL;

      IF V_COUNTPRVOL > 0 THEN
        UPDATE NOTAFISCAL
           SET DSQTDEVOLU = (SELECT SUM(CEIL(VOLUMENOTA)) VOLUMENOTA
                               FROM (
                               SELECT  IT.CDPRODUTO, SUM((NVL(QTVOLUMES,1)*IT.QTITEMPROD)/NVL(QTPROPVOLUME,1)) VOLUMENOTA
                                 FROM PRODUTO PR, ITEMPRODUTO IT
                                WHERE PR.CDPRODUTO = IT.CDPRODUTO
                                  AND NRLANCTONF   = P_NRLANCTONF
                                  AND CDFILIAL     = P_CDFILIAL
                                  AND IDATUESTPROD = 'S'
                              GROUP BY IT.CDPRODUTO))
         WHERE NRLANCTONF = P_NRLANCTONF
           AND CDFILIAL   = P_CDFILIAL;
      END IF;

	END IF;

/*
	---------------------------------------------------------------------------------------
*/

   SELECT MAX(NRLANCTONF)
     INTO V_NRLANCTO
    FROM CONTARECEBER
   WHERE CDFILIAL   = P_CDFILIAL   AND
         NRLANCTONF = P_NRLANCTONF;

   IF V_ORIGDESTNF = 'F' THEN
      V_CLIENTENF := V_CDFORNECED;
   ELSIF V_ORIGDESTNF = 'L' THEN
      V_CLIENTENF := V_CDFILIRECE;
   ELSE 
      V_CLIENTENF := V_CDCLIENTE; 
   END IF; 
   
   SELECT MAX(IDATUESTTPOP) 
   INTO V_IDATUESTTPOP 
   FROM TIPOOPERAC 
   WHERE IDENTRSAIDOP = 'S'  AND 
         CDTIPOOPER = V_CDTIPOOPER ; 
   
   IF V_IDATUESTTPOP = 'N' THEN 
      V_NRLANCESTQ := ''; 
   END IF; 
   
   INSERT INTO ITEMENCARGO 
      (CDFILIAL, NRLANCTONF, CDENCARGO, VRITEMENCA) 
      (SELECT P_CDFILIAL, P_NRLANCTONF, CDENCARGO, 0 
       FROM ENCARGO 
       WHERE CDENCARGO NOT IN (SELECT CDENCARGO 
                               FROM ITEMENCARGO 
                               WHERE CDFILIAL   = P_CDFILIAL AND 
                                     NRLANCTONF = P_NRLANCTONF)); 
   
   IMPOSTO_SAIDA(V_CDEMPRESA, V_CDINSCESTA, V_CDTIPOOPER, V_CLIENTENF, 
                 P_CDFILIAL,  P_NRLANCTONF, V_DTENTRSAID, V_VRNOTAFISC, 
                 V_NRLANCESTQ, P_CDOPERADOR); 
   
   SELECT MAX(IDGERAVENC) 
     INTO V_IDGERAVENC 
     FROM TIPOOPERAC 
    WHERE CDTIPOOPER   = V_CDTIPOOPER AND 
          IDENTRSAIDOP = 'S'; 
   
   IF  (V_IDGERAVENC = 'S') AND ((V_IDSISTEMA <> 'PDV') AND (V_NRLANCTO IS NULL))  THEN 
      IF (V_IDSISTEMA = 'FAI') OR (V_IDSISTEMA = 'DFS') OR (V_IDSISTEMA = 'EXP') OR (V_IDSISTEMA = 'SRV') THEN 
     
         GERA_DADOS_FINANCEIRO(P_CDFILIAL,            
                               P_NRLANCTONF,          
                               V_CDFILIEMIT,          
                               'L',                   
                               V_RESULT_DADOS_FIN);   
      ELSE 
     
         APURAC_VENC(P_CDFILIAL, V_CLIENTENF, P_NRLANCTONF); 
     
      END IF; 
   END IF;
   
   IF V_NRLANCESTQ IS NOT NULL THEN 
      IF NVL(P_EVOLESTOQ, 'S') <> 'N' THEN 
   
         SELECT SUM(VRTOTAITEM - VRDESCITEM) AS VRLIQUIDO 
         INTO V_VRLIQUIDO 
         FROM ITEMPRODUTO 
         WHERE CDFILIAL   = P_CDFILIAL AND 
               NRLANCTONF = P_NRLANCTONF; 
   
         V_DIFERENCA := V_VRNOTAFISC - V_VRLIQUIDO; 
   
         CUSTO_LIQUIDO(P_CDFILIAL, V_NRLANCESTQ, V_VRLIQUIDO, V_DIFERENCA, 'N'); 
   
         IF V_DTENTRSAID < P_DATAPROC THEN 
            V_DTINICIAL := V_DTENTRSAID; 
         ELSE 
            V_DTINICIAL := P_DATAPROC; 
         END IF; 
         
         IF V_DTENTRSAID > P_DATAPROC THEN 
            V_DTFINAL := V_DTENTRSAID; 
         ELSE 
            V_DTFINAL := P_DATAPROC; 
         END IF; 
         
         UPDATE ITLANCTOEST 
         SET  IDEVOLESTOQ = 'S' 
         WHERE CDFILIAL    = P_CDFILIAL AND 
               NRLANCESTQ  = V_NRLANCESTQ; 

         IF NVL(P_IDATUESTOQ,'S') <> 'N' THEN
            ATUALIZA_ESTOQUE(V_CDFILIEMIT, V_DTINICIAL, V_DTFINAL, P_CDFILIAL, V_NRLANCESTQ, P_IDBLOQTRANS);
         END IF;
      
      ELSE 
      
         UPDATE ITLANCTOEST 
         SET  IDEVOLESTOQ = 'N' 
         WHERE CDFILIAL    = P_CDFILIAL AND 
               NRLANCESTQ  = V_NRLANCESTQ; 
      END IF; 
   END IF; 
   
  /* Rotina para gerar os dados adicionais da NF, serao exibidos os ativos alocados no contrato que foi expedido*/
   V_RECADIC:= NULL;
   V_RECADIC2:= NULL;
   V_PRODREC := NULL;
   V_RECADICAUX := NULL;   
   /* ENDERECO */
   
   SELECT COUNT(*) 
     INTO V_DADOADIC 
     FROM DADOSADIC 
    WHERE (CDFILIAL   = P_CDFILIAL) 
      AND (NRLANCTONF = P_NRLANCTONF)
      AND (DSDADOADIC LIKE '%ENTREGA:%');
    
   IF V_DADOADIC = 0 THEN
   
       BEGIN
        SELECT DISTINCT (ENP.DSENDERECO||' - '||ENP.NMBAIRROENDERECO||' - '||MN.NMMUNICIPIO||' - '||ENP.SGESTADO) AS ENDERECO
          INTO V_RECADIC
          FROM FCT_ENDERECOORCA ENP, MUNICIPIO MN, FCT_REFEORCACONT RF, FCT_CONTRATO CT, FCT_RECURSOSCONTR RC,  ITEMPRODUTO IT
         WHERE CT.NRSEQCONTRATO = RC.NRSEQCONTRATO
           AND RF.NRSEQCONTRATO = CT.NRSEQCONTRATO
           AND ENP.NRSEQORCAMENTO = RF.NRSEQORCAMENTO
           AND MN.CDMUNICIPIO(+) = ENP.CDMUNICIPIO
           AND ENP.CDTIPOENDERECO = 'ENTREGA'
           AND IT.CDFILIAL = P_CDFILIAL
           AND IT.NRLANCTONF = P_NRLANCTONF 
           AND RC.NRPEDIDO = IT.NRPEDIDO
           AND RC.CDFILIALPED = IT.CDFILIALPED
           AND RC.NRITEMPED = IT.NRITEMPED;
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              V_RECADIC := NULL;
        END;
        
        
        IF V_RECADIC IS NOT NULL THEN
               V_RECADIC := 'ENTREGA: '||V_RECADIC;
               WHILE V_RECADIC IS NOT NULL LOOP 
                    SELECT MAX(NRDADOADIC) 
                      INTO V_DADOADIC 
                      FROM DADOSADIC 
                    WHERE (CDFILIAL   = P_CDFILIAL) AND 
                          (NRLANCTONF = P_NRLANCTONF); 
                    IF V_DADOADIC IS NULL THEN 
                       V_DADOADIC := '001'; 
                    ELSE
                       V_DADOADIC := V_DADOADIC+1;             
                    END IF; 
                 
                    IF LENGTH(V_RECADIC) <= 255 THEN
                 
                       INSERT INTO DADOSADIC(CDFILIAL, NRLANCTONF, NRDADOADIC, DSDADOADIC) 
                          VALUES(P_CDFILIAL, P_NRLANCTONF, V_DADOADIC, V_RECADIC); 
                    
                       V_RECADIC := NULL; 
                    ELSE 
                       V_RECADICAUX := SUBSTR(V_RECADIC, 0, 255);
                    
                       INSERT INTO DADOSADIC(CDFILIAL, NRLANCTONF, NRDADOADIC, DSDADOADIC) 
                          VALUES(P_CDFILIAL, P_NRLANCTONF, V_DADOADIC, V_RECADICAUX);
                    
                       V_RECADIC := REPLACE(V_RECADIC,V_RECADICAUX,'');  
                       
                  END IF;   
             END LOOP;                                         
        END IF; 
    END IF;
   
   V_RECADIC := NULL;
    FOR C_ATIVREC IN (SELECT RC.NRSEQRECUCONT, RC.NRSEQCONTRATO, BA.CDPATRIBEMATIVO, P.CDARVPROD
                     FROM FCT_RECURSOSCONTR RC, BEMATIVO BA, ITEMPRODUTO IT, PRODUTO P
                    WHERE IT.CDFILIAL = P_CDFILIAL
                      AND IT.NRLANCTONF = P_NRLANCTONF 
                      AND RC.NRPEDIDO = IT.NRPEDIDO
                      AND RC.CDFILIALPED = IT.CDFILIALPED
                      AND RC.NRITEMPED = IT.NRITEMPED
                      AND BA.CDBEMATIVO = RC.CDBEMATIVO
                      AND IT.CDPRODUTO  = P.CDPRODUTO 
                    ORDER BY P.CDARVPROD) LOOP

      IF V_RECADIC IS NULL THEN                   
         V_RECADIC := V_RECADIC||C_ATIVREC.CDARVPROD||': '||C_ATIVREC.CDPATRIBEMATIVO;        
      ELSE
         IF V_PRODANTE <> C_ATIVREC.CDARVPROD THEN 
            V_RECADIC := V_RECADIC||'/ '||C_ATIVREC.CDARVPROD||': '||C_ATIVREC.CDPATRIBEMATIVO;
         ELSE
            V_RECADIC := V_RECADIC||'/ '||C_ATIVREC.CDPATRIBEMATIVO;
         END IF;     
      END IF;
      
      V_PRODANTE := C_ATIVREC.CDARVPROD;
      
  END LOOP;
  
   IF V_RECADIC IS NOT NULL THEN 
      BEGIN
         SELECT DSDADOADIC
           INTO V_TEMDADOS
            FROM DADOSADIC
           WHERE (CDFILIAL   = P_CDFILIAL) 
             AND (NRLANCTONF = P_NRLANCTONF)         
             AND (DSDADOADIC LIKE '%ATIVOS:%');
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_TEMDADOS := NULL;
      END;
      IF V_TEMDADOS IS NULL THEN
         V_RECADIC := 'ATIVOS: '||V_RECADIC;
         WHILE V_RECADIC IS NOT NULL LOOP 
            SELECT MAX(NRDADOADIC) 
              INTO V_DADOADIC 
              FROM DADOSADIC 
            WHERE (CDFILIAL   = P_CDFILIAL) AND 
                  (NRLANCTONF = P_NRLANCTONF); 
            IF V_DADOADIC IS NULL THEN 
               V_DADOADIC := '001'; 
            ELSE
               V_DADOADIC := V_DADOADIC+1;             
            END IF; 
         
            IF LENGTH(V_RECADIC) <= 255 THEN
         
               INSERT INTO DADOSADIC(CDFILIAL, NRLANCTONF, NRDADOADIC, DSDADOADIC) 
                  VALUES(P_CDFILIAL, P_NRLANCTONF, V_DADOADIC, V_RECADIC); 
            
               V_RECADIC := NULL; 
            ELSE 
               V_RECADICAUX := SUBSTR(V_RECADIC, 0, 255);
            
               INSERT INTO DADOSADIC(CDFILIAL, NRLANCTONF, NRDADOADIC, DSDADOADIC) 
                  VALUES(P_CDFILIAL, P_NRLANCTONF, V_DADOADIC, V_RECADICAUX);
            
               V_RECADIC := REPLACE(V_RECADIC,V_RECADICAUX,'');  
               
            END IF;   
         END LOOP;
      ELSE 
         SELECT DSDADOADIC, NRDADOADIC
           INTO V_COMPADIC, V_DADOADIC
           FROM DADOSADIC
          WHERE (CDFILIAL   = P_CDFILIAL) 
            AND (NRLANCTONF = P_NRLANCTONF) 
            AND  NRDADOADIC = (SELECT MAX(NRDADOADIC)
                              FROM DADOSADIC 
                             WHERE (CDFILIAL   = P_CDFILIAL) 
                               AND (NRLANCTONF = P_NRLANCTONF)
                               AND (DSDADOADIC LIKE '%ATIVOS:%'));
                               
         V_RECADIC2 := V_COMPADIC||' // '||V_RECADIC;
         
            IF LENGTH(V_RECADIC2) <= 255 THEN
         
               UPDATE DADOSADIC
                  SET DSDADOADIC = V_RECADIC2
                WHERE CDFILIAL = P_CDFILIAL
                  AND NRLANCTONF = P_NRLANCTONF 
                  AND NRDADOADIC = V_DADOADIC;
            
               V_RECADIC := NULL; 
            ELSE 
               WHILE (V_RECADIC IS NOT NULL) LOOP 
                  V_RECADICAUX := V_RECADIC; 
               
                  IF LENGTH(V_RECADICAUX) >= 225 THEN
                      V_RECADICAUX := SUBSTR(V_RECADIC, 0, 255);
                   END IF;
               
                  SELECT NVL(MAX(TO_NUMBER(NRDADOADIC)),0)+1
                    INTO V_DADOADIC 
                    FROM DADOSADIC 
                   WHERE (CDFILIAL   = P_CDFILIAL) AND 
                         (NRLANCTONF = P_NRLANCTONF); 

                  INSERT INTO DADOSADIC(CDFILIAL, NRLANCTONF, NRDADOADIC, DSDADOADIC)
                     VALUES(P_CDFILIAL, P_NRLANCTONF, V_DADOADIC, V_RECADICAUX);

                  V_RECADIC := REPLACE(V_RECADIC,V_RECADICAUX,'');  
               END LOOP;     
           END IF;
      END IF;
    END IF;       
END;