IF EXISTS(SELECT NAME FROM SYSOBJECTS
          WHERE NAME = 'GERA_RAZAO_TEMP' AND TYPE = 'P')
   DROP PROCEDURE GERA_RAZAO_TEMP
   GO

CREATE PROCEDURE GERA_RAZAO_TEMP
                    @P_CDEMPRESA  VARCHAR(02),
                    @P_DATAINIC   DATETIME,
                    @P_DATAFIM    DATETIME,
                    @P_NRCTBTEMP  VARCHAR(06),
                    @P_IDSALD     VARCHAR(01),
                    @P_CDCONTCOME VARCHAR(2000),
                    @P_CDCONTINI  VARCHAR(20),
                    @P_CDCONTFIN  VARCHAR(20)
AS

DECLARE @V_DTSOMA DATETIME;
DECLARE @V_NRSEQ INTEGER;
DECLARE @V_CONT INTEGER;

DECLARE @SALDANT_DTSALDCOME DATETIME;
DECLARE @SALDANT_CDSALDCOME VARCHAR(21);
DECLARE @SALDANT_CDCONTCTBL VARCHAR(20);
DECLARE @SALDANT_VRSALDCOME DOUBLE PRECISION;

DECLARE @SOMALANC_CDCONTCOME VARCHAR(21);
DECLARE @SOMALANC_CDCONTCTBL VARCHAR(20);
DECLARE @SOMALANC_VALOR DOUBLE PRECISION;

DECLARE @LANC_CDCONTCOME  VARCHAR(21);
DECLARE @LANC_CDCONTCTBL  VARCHAR(20);
DECLARE @LANC_VRLANCAMENT DOUBLE PRECISION;
DECLARE @LANC_CDDOCUCONT  VARCHAR(20);
DECLARE @LANC_DSHISTLANC  VARCHAR(70);
DECLARE @LANC_NRLANCCONT  VARCHAR(12);
DECLARE @LANC_DTLANCCONT  DATETIME;


BEGIN
   SET @V_NRSEQ  = 1;
   SET @V_DTSOMA = (DATEADD(MM, 1, @P_DATAINIC) - DAY(DATEADD(MM, 1, @P_DATAINIC))) +1;
   SET @V_DTSOMA = DATEADD(MM, -1, @V_DTSOMA);

   DECLARE SALDANT CURSOR FOR
      SELECT S.DTSALDCOME, S.CDSALDCOME, S.CDCONTCTBL, S.VRSALDCOME
      FROM SALDCOME S
      WHERE (S.CDEMPRESA  = @P_CDEMPRESA) AND
            (S.IDSALDCOME = @P_IDSALD)    AND
            (S.VRSALDCOME <> 0.0)         AND
            ((CHARINDEX('_'+S.CDSALDCOME+'_', @P_CDCONTCOME) > 0) OR (@P_CDCONTCOME = 'T')) AND
            (CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIN) AND
            (S.DTSALDCOME IN (SELECT MAX(DTSALDCOME)
                              FROM SALDCOME
                              WHERE(DTSALDCOME <= @P_DATAINIC) AND
                                   (CDEMPRESA  = @P_CDEMPRESA) AND
                                   (CDSALDCOME = S.CDSALDCOME) AND
                                   (IDSALDCOME = @P_IDSALD)    AND
                                   (CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIN) AND
                                   (CDCONTCTBL = S.CDCONTCTBL)));
   OPEN SALDANT;
   FETCH NEXT FROM SALDANT INTO @SALDANT_DTSALDCOME, @SALDANT_CDSALDCOME, @SALDANT_CDCONTCTBL, @SALDANT_VRSALDCOME
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO CTBTEMP
         (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET,
          DTLANCCONTT,NRLANCCONTT,VRLANCAMENTT,CDDOCUCONTT,
          DSHISTLANCT, NRSEQCTB, IDCTBTEMP)
      VALUES(@P_NRCTBTEMP, @SALDANT_CDCONTCTBL, @SALDANT_CDSALDCOME,
             @SALDANT_DTSALDCOME, '', @SALDANT_VRSALDCOME, '', '', CONVERT(VARCHAR, @V_NRSEQ), '0');


      SET @V_NRSEQ = @V_NRSEQ+1;
      FETCH NEXT FROM SALDANT INTO @SALDANT_DTSALDCOME, @SALDANT_CDSALDCOME, @SALDANT_CDCONTCTBL, @SALDANT_VRSALDCOME
   END;
   CLOSE SALDANT;
   DEALLOCATE SALDANT;

   IF (@P_DATAINIC <> @V_DTSOMA) BEGIN
      DECLARE SOMALANC CURSOR FOR
         SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, SUM(VRLANCAMENT) AS VALOR
         FROM LANCCONT,CONTCTBL
         WHERE(CDEMPRESA  = @P_CDEMPRESA)                        AND
              (IDLANCCOME = @P_IDSALD)                           AND
              (DTLANCCONT BETWEEN @V_DTSOMA AND @P_DATAINIC - 1) AND
              (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL)        AND
              (CONTCTBL.IDCONTCCUS <> 'R')                       AND
              ((CHARINDEX('_' + CDCONTCOME + '_', @P_CDCONTCOME) > 0) OR (@P_CDCONTCOME = 'T')) AND
              (LANCCONT.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIN)
         GROUP BY LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL;

      OPEN SOMALANC;
      FETCH NEXT FROM SOMALANC INTO @SOMALANC_CDCONTCOME, @SOMALANC_CDCONTCTBL, @SOMALANC_VALOR;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         SELECT @V_CONT = COUNT(*)
         FROM CTBTEMP
         WHERE (CTBTEMP.NRCTBTEMP = @P_NRCTBTEMP) AND
               (CTBTEMP.CDCONTCOMET = @SOMALANC_CDCONTCOME)AND
               (CTBTEMP.CDCONTCTBLT = @SOMALANC_CDCONTCTBL);

         IF @V_CONT = 0.0 BEGIN
            INSERT INTO CTBTEMP
               (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, DTLANCCONTT,NRLANCCONTT,
                VRLANCAMENTT,CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP)
            VALUES(@P_NRCTBTEMP, @SOMALANC_CDCONTCTBL, @SOMALANC_CDCONTCOME,
               (@P_DATAINIC - 1), '', @SOMALANC_VALOR, '', '', RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRSEQ), 10), '0');

            SET @V_NRSEQ = @V_NRSEQ +1;
         END
         ELSE BEGIN
            UPDATE CTBTEMP
            SET VRLANCAMENTT = VRLANCAMENTT + @SOMALANC_VALOR
            WHERE (CTBTEMP.NRCTBTEMP = @P_NRCTBTEMP) AND
                  (CTBTEMP.CDCONTCOMET = @SOMALANC_CDCONTCOME) AND
                  (CTBTEMP.CDCONTCTBLT = @SOMALANC_CDCONTCTBL);
         END;
         FETCH NEXT FROM SOMALANC INTO @SOMALANC_CDCONTCOME, @SOMALANC_CDCONTCTBL, @SOMALANC_VALOR;
      END;
      CLOSE SOMALANC;
      DEALLOCATE SOMALANC;
   END;

   DECLARE LANC CURSOR FOR
      SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, VRLANCAMENT, CDDOCUCONT,
             DSHISTLANC, NRLANCCONT, DTLANCCONT
       FROM LANCCONT, CONTCTBL
      WHERE(CDEMPRESA  = @P_CDEMPRESA)                     AND
           (IDLANCCOME = @P_IDSALD)                        AND
           (DTLANCCONT BETWEEN @P_DATAINIC AND @P_DATAFIM) AND
           (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL)     AND
           (CONTCTBL.IDCONTCCUS <> 'R')                    AND
           ((CHARINDEX('_'+CDCONTCOME+'_', @P_CDCONTCOME) > 0) OR (@P_CDCONTCOME = 'T')) AND
           (LANCCONT.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIN);

   OPEN LANC;
   FETCH NEXT FROM LANC INTO @LANC_CDCONTCOME, @LANC_CDCONTCTBL, @LANC_VRLANCAMENT, @LANC_CDDOCUCONT,
                             @LANC_DSHISTLANC, @LANC_NRLANCCONT, @LANC_DTLANCCONT;
   WHILE(@@FETCH_STATUS = 0) BEGIN
      INSERT INTO CTBTEMP
         (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, DTLANCCONTT,NRLANCCONTT,
          VRLANCAMENTT,CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP)
      VALUES
         (@P_NRCTBTEMP, @LANC_CDCONTCTBL, @LANC_CDCONTCOME, @LANC_DTLANCCONT,
          @LANC_NRLANCCONT, @LANC_VRLANCAMENT, @LANC_CDDOCUCONT, @LANC_DSHISTLANC, RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRSEQ), 10), '1');

      SET @V_NRSEQ = @V_NRSEQ +1;
      FETCH NEXT FROM LANC INTO @LANC_CDCONTCOME, @LANC_CDCONTCTBL, @LANC_VRLANCAMENT, @LANC_CDDOCUCONT,
                                @LANC_DSHISTLANC, @LANC_NRLANCCONT, @LANC_DTLANCCONT;
   END;
   CLOSE LANC;
   DEALLOCATE LANC;
END;
GO


-- VERSAO --
EXEC ATUVEROBJ 'GERA_RAZAO_TEMP','5.07.002';
GO

