IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
                    WHERE
	        O.NAME = 'GERA_RAZAOGER_TEMP_INSCESTAD'  AND
	        TYPE = 'P' 			            AND
	        U.UID = O.UID             		AND
	        U.NAME = USER)

  DROP PROCEDURE GERA_RAZAOGER_TEMP_INSCESTAD
  GO

CREATE PROCEDURE GERA_RAZAOGER_TEMP_INSCESTAD @P_CDEMPRESA  VARCHAR(2),
					      @P_DATAINIC   DATETIME,
					      @P_DATAFIM    DATETIME,
					      @P_NRCTBTEMP  VARCHAR(6),
					      @P_CDINSCESTA VARCHAR(16),
					      @P_CDCENTCUST VARCHAR(4),
					      @P_CDCONTINI  VARCHAR(20),
					      @P_CDCONTFIM  VARCHAR(20),
                     @P_CDFILIAL   VARCHAR(5000)
AS
   DECLARE @V_DTSOMA DATETIME;
   DECLARE @V_DTCOMPCUSTO DATETIME;
   DECLARE @V_NRSEQ INTEGER;
   DECLARE @V_CONT INTEGER;

   DECLARE @V_CDCENTCUST VARCHAR(4);

   DECLARE @V_DTSALDCCUS DATETIME;
   DECLARE @V_CDFILIAL VARCHAR(4);
   DECLARE @V_VRSALDCCUS FLOAT;
   DECLARE @V_CDCONTCTBL VARCHAR(20);
   DECLARE @v_CDCONTCOME VARCHAR(21);

   DECLARE @V_VRLANCCUST FLOAT;
   DECLARE @V_CDDOCUCONT VARCHAR(20);
   DECLARE @V_DSHISTLANC VARCHAR(200);
   DECLARE @V_NRLANCCONT VARCHAR(12);
   DECLARE @V_DTLANCCONT DATETIME;

   DECLARE @V_VALOR FLOAT;
BEGIN

   SET @V_NRSEQ = 1;
   SELECT @V_DTSOMA = MAX(DTDATA)
     FROM DATA
    WHERE DTMES = '01/' + SUBSTRING(CONVERT(VARCHAR, @P_DATAINIC, 103), 04, 07);
   SET @V_DTSOMA = @V_DTSOMA + 1;
   SET @V_DTSOMA = DATEADD(MM,-1, @V_DTSOMA);

   DECLARE SALDANT CURSOR FOR
      (SELECT S.DTSALDCCUS, S.CDFILIAL, S.CDCENTCUST, S.CDCONTCTBL, S.VRSALDCCUS
       FROM SALDCCUS S, CONTCTBL, FILIAL F
       WHERE (S.CDEMPRESA  = @P_CDEMPRESA)                                     AND
	     (S.VRSALDCCUS <> 0)                                                    AND
	     (F.CDFILIAL  = S.CDFILIAL)                                             AND
        ((CHARINDEX(F.CDFILIAL, @P_CDFILIAL) <> 0) OR @P_CDFILIAL = 'T')       AND
	     ((CHARINDEX(F.CDINSCESTA, @P_CDINSCESTA) <> 0) OR @P_CDINSCESTA = 'T') AND
	     ((CHARINDEX(S.CDCENTCUST, @P_CDCENTCUST) <> 0) OR @P_CDCENTCUST = 'T') AND
             (S.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)               AND
	     (S.CDCONTCTBL = CONTCTBL.CDCONTCTBL)                             AND
             (CONTCTBL.IDTIPOCONT = 'A')                                      AND
             (S.DTSALDCCUS IN (SELECT MAX(DTSALDCCUS)
	                       FROM SALDCCUS
	                       WHERE(DTSALDCCUS <= @P_DATAINIC)  AND
	                            (CDEMPRESA  = @P_CDEMPRESA)  AND
	                            (CDFILIAL   = S.CDFILIAL)   AND
		                    (CDCENTCUST = S.CDCENTCUST) AND
                                    (CDCONTCTBL = S.CDCONTCTBL)))
       )
   OPEN SALDANT;
   FETCH NEXT FROM SALDANT INTO @V_DTSALDCCUS, @V_CDFILIAL, @V_CDCENTCUST,
                                @V_CDCONTCTBL, @V_VRSALDCCUS;
   WHILE (@@FETCH_STATUS = 0) BEGIN
        INSERT INTO CTBTEMP
           (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
            CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
            CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
        VALUES
           (@P_NRCTBTEMP, @V_CDCONTCTBL, @V_CDFILIAL, @V_CDCENTCUST,
            '', @V_DTSALDCCUS, '', @V_VRSALDCCUS,
            '', '', @V_NRSEQ, '0');

        SET @V_NRSEQ = @V_NRSEQ+1;
        FETCH NEXT FROM SALDANT INTO @V_DTSALDCCUS, @V_CDFILIAL, @V_CDCENTCUST,
                                     @V_CDCONTCTBL, @V_VRSALDCCUS;
   END;
   CLOSE SALDANT;
   DEALLOCATE SALDANT;

   IF @P_DATAINIC <> @V_DTSOMA BEGIN
      DECLARE SOMALANC CURSOR FOR
         (SELECT LANCCONT.CDCONTCTBL, LANCCCUSTO.CDFILIAL,
                 LANCCCUSTO.CDCENTCUST,LANCCONT.CDCONTCOME, SUM(LANCCCUSTO.VRLANCCUST) AS VALOR
          FROM LANCCONT, LANCCCUSTO, FILIAL F
          WHERE(LANCCONT.CDEMPRESA   = @P_CDEMPRESA)                            AND
	       (LANCCONT.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)           AND
	       (LANCCCUSTO.CDEMPRESA = @P_CDEMPRESA)                                 AND
	       (F.CDFILIAL  = LANCCCUSTO.CDFILIAL)                                   AND
          ((CHARINDEX(F.CDFILIAL,     @P_CDFILIAL) <> 0) OR @P_CDFILIAL = 'T')  AND
	       ((CHARINDEX(F.CDINSCESTA, @P_CDINSCESTA) <> 0) OR @P_CDINSCESTA = 'T')          AND
	       ((CHARINDEX(LANCCCUSTO.CDCENTCUST, @P_CDCENTCUST) <> 0) OR @P_CDCENTCUST = 'T') AND
	       (LANCCCUSTO.DTCOMPCUSTO BETWEEN @V_DTSOMA AND @P_DATAINIC - 1)                 AND
	       (LANCCONT.NRLANCCONT   = LANCCCUSTO.NRLANCCONT)                           AND
	       (LANCCONT.NRSEQLANC    = LANCCCUSTO.NRSEQLANC)
	   GROUP BY LANCCONT.CDCONTCTBL, LANCCCUSTO.CDFILIAL,
                    LANCCCUSTO.CDCENTCUST,LANCCONT.CDCONTCOME
         )
      OPEN SOMALANC;
      FETCH NEXT FROM SOMALANC INTO @V_CDCONTCTBL, @v_CDFILIAL,
                                    @V_CDCENTCUST,@v_CDCONTCOME, @V_VALOR;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         SELECT @V_CONT = COUNT(*)
         FROM CTBTEMP
         WHERE (CTBTEMP.NRCTBTEMP = @P_NRCTBTEMP) AND
               (CTBTEMP.CDCENTCUSTT = @V_CDCENTCUST) AND
               (CTBTEMP.CDFILIALT   = @V_CDFILIAL)   AND
               (CTBTEMP.CDCONTCTBLT = @V_CDCONTCTBL);

         IF @V_CONT = 0 BEGIN
            INSERT INTO CTBTEMP
               (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
                CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
                CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
            VALUES
               (@P_NRCTBTEMP, @V_CDCONTCTBL, @V_CDFILIAL, @V_CDCENTCUST,
                @v_CDCONTCOME, (@P_DATAINIC - 1), '', @V_VALOR,
                '', '', @V_NRSEQ, '0');

            SET @V_NRSEQ = @V_NRSEQ + 1;
         END
         ELSE BEGIN
             UPDATE CTBTEMP
             SET VRLANCAMENTT = VRLANCAMENTT + @V_VALOR
             WHERE (CTBTEMP.NRCTBTEMP = @P_NRCTBTEMP) AND
                   (CTBTEMP.CDFILIALT   = @V_CDFILIAL)  AND
                   (CTBTEMP.CDCENTCUSTT = @V_CDCENTCUST)AND
                   (CTBTEMP.CDCONTCTBLT = @V_CDCONTCTBL);

         END;
         FETCH NEXT FROM SOMALANC INTO @V_CDCONTCTBL, @v_CDFILIAL,
                                       @V_CDCENTCUST,@v_CDCONTCOME, @V_VALOR;
      END;

      CLOSE SOMALANC;
      DEALLOCATE SOMALANC;
   END;

   DECLARE LANC CURSOR FOR SELECT LANCCCUSTO.CDFILIAL,   LANCCCUSTO.CDCENTCUST, LANCCONT.CDCONTCTBL,
		                  LANCCCUSTO.VRLANCCUST, LANCCONT.CDDOCUCONT,   LANCCONT.DSHISTLANC,
			          LANCCONT.NRLANCCONT,   LANCCCUSTO.DTCOMPCUSTO,LANCCONT.CDCONTCOME 
			   FROM LANCCONT, LANCCCUSTO, FILIAL F
			   WHERE(LANCCONT.CDEMPRESA = @P_CDEMPRESA)                                       AND
		                (LANCCONT.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)          AND
			        (LANCCCUSTO.CDEMPRESA = @P_CDEMPRESA)                                     AND
			        (LANCCCUSTO.DTCOMPCUSTO BETWEEN @P_DATAINIC AND @P_DATAFIM)               AND
			        (F.CDFILIAL  = LANCCCUSTO.CDFILIAL)                                       AND
                 ((CHARINDEX(F.CDFILIAL,   @P_CDFILIAL) <> 0) OR @P_CDFILIAL = 'T')        AND
			        ((CHARINDEX(F.CDINSCESTA, @P_CDINSCESTA) <> 0) OR @P_CDINSCESTA = 'T')    AND
			        ((CHARINDEX(LANCCCUSTO.CDCENTCUST, @P_CDCENTCUST) <> 0) OR @P_CDCENTCUST = 'T') AND
			        (LANCCCUSTO.NRLANCCONT = LANCCONT.NRLANCCONT)                             AND
			        (LANCCCUSTO.NRSEQLANC  = LANCCONT.NRSEQLANC);

   OPEN LANC;
   FETCH NEXT FROM LANC INTO @V_CDFILIAL, @V_CDCENTCUST, @V_CDCONTCTBL,
		             @V_VRLANCCUST, @V_CDDOCUCONT, @V_DSHISTLANC,
			     @V_NRLANCCONT, @V_DTCOMPCUSTO,@v_CDCONTCOME;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO CTBTEMP
          (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,  CDCENTCUSTT,
           CDCONTCOMET, DTLANCCONTT, NRLANCCONTT,VRLANCAMENTT,
           CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,   IDCTBTEMP)
          VALUES(@P_NRCTBTEMP, @V_CDCONTCTBL, @V_CDFILIAL, @V_CDCENTCUST,
                 @v_CDCONTCOME, @V_DTCOMPCUSTO, @V_NRLANCCONT, @V_VRLANCCUST,
                 @V_CDDOCUCONT, @V_DSHISTLANC, @V_NRSEQ, '1');

          SET @V_NRSEQ = @V_NRSEQ+1;

      FETCH NEXT FROM LANC INTO @V_CDFILIAL, @V_CDCENTCUST, @V_CDCONTCTBL,
                                @V_VRLANCCUST, @V_CDDOCUCONT, @V_DSHISTLANC,
                                @V_NRLANCCONT, @V_DTCOMPCUSTO,@v_CDCONTCOME;
   END;
   CLOSE LANC;
   DEALLOCATE LANC;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'GERA_RAZAOGER_TEMP_INSCESTAD','5.30.000';
GO

