IF EXISTS(SELECT NAME FROM SYSOBJECTS
          WHERE NAME = 'GERA_RAZAOGER_TEMP' AND TYPE = 'P')
   DROP PROCEDURE GERA_RAZAOGER_TEMP
GO
                                                                                                                      
CREATE PROCEDURE GERA_RAZAOGER_TEMP(
   @P_CDEMPRESA  VARCHAR(02),
   @P_DATAINIC   DATETIME,
   @P_DATAFIM    DATETIME,
   @P_NRCTBTEMP  VARCHAR(06),
   @P_CDFILIAL   VARCHAR(2000),
   @P_CDCENTCUST VARCHAR(2000),
   @P_CDCONTINI  VARCHAR(20),
   @P_CDCONTFIM  VARCHAR(20))
AS
   DECLARE @V_DTSOMA       DATETIME;
   DECLARE @V_NRSEQ        INTEGER;
   DECLARE @V_CONT         INTEGER;
   DECLARE @V_CDCONTCOME   VARCHAR(21);
                                                                                                                      
   /* VARIAVEIS DO CURSOR C_SALDANT */
   DECLARE @C_SALDANT_DTSALDCCUS   DATETIME;
   DECLARE @C_SALDANT_CDFILIAL     VARCHAR(04);
   DECLARE @C_SALDANT_CDCENTCUST   VARCHAR(04);
   DECLARE @C_SALDANT_CDCONTCTBL   VARCHAR(20);
   DECLARE @C_SALDANT_VRSALDCCUS   DOUBLE PRECISION;
                                                                                                                      
   /* VARIAVEIS DO CURSOR C_SOMALANC */
   DECLARE @C_SOMALANC_CDCONTCTBL  VARCHAR(20);
   DECLARE @C_SOMALANC_CDFILIAL    VARCHAR(04);
   DECLARE @C_SOMALANC_CDCENTCUST  VARCHAR(04);
   DECLARE @C_SOMALANC_VALOR       DOUBLE PRECISION;
                                                                                                                      
   /* VARIAVEIS DO CURSOR C_LANC */
   DECLARE @C_LANC_CDFILIAL       VARCHAR(04);
   DECLARE @C_LANC_CDCENTCUST     VARCHAR(04);
   DECLARE @C_LANC_CDCONTCTBL     VARCHAR(20);
   DECLARE @C_LANC_VRLANCCUST     DOUBLE PRECISION;
   DECLARE @C_LANC_CDDOCUCONT     VARCHAR(20);
   DECLARE @C_LANC_DSHISTLANC     VARCHAR(70);
   DECLARE @C_LANC_NRLANCCONT     VARCHAR(12);
   DECLARE @C_LANC_DTCOMPCUSTO    DATETIME;
   DECLARE @C_LANC_CDCONTCOME     VARCHAR(21);
                                                                                                                      
   /* VARIAVEIS DO CURSOR C_FILIAL */ 
   DECLARE @C_FILIAL_CDFILIAL      VARCHAR(04);
   DECLARE @C_FILIAL_CDCENTCUST    VARCHAR(04);
                                                                                                                      
BEGIN
   SET @V_NRSEQ  = 1;
                                                                                                                      
	/* ULTIMO DIA DO MES */
   SELECT MAX(DTDATA) AS DTDATA
     FROM DATA
    WHERE DTMES = ('01/' + SUBSTRING(CONVERT(VARCHAR, DATEADD(MM, -1, @P_DATAINIC), 103), 4, 7));  
                                                                                                                                                                                                                                        
   DECLARE C_FILIAL CURSOR FOR 
      SELECT CDFILIAL, CDCENTCUST
        FROM CENTCUSTFIL
       WHERE ((CHARINDEX(CDFILIAL, @P_CDFILIAL) <> 0) OR (@P_CDFILIAL = 'T'))
         AND ((CHARINDEX(CDCENTCUST, @P_CDCENTCUST) <> 0) OR (@P_CDCENTCUST = 'T'));
                                                                                                                      
   OPEN C_FILIAL;
   FETCH NEXT FROM C_FILIAL INTO @C_FILIAL_CDFILIAL, @C_FILIAL_CDCENTCUST;
   WHILE (@@FETCH_STATUS = 0) BEGIN
                                                                                                                      
      DECLARE C_SALDANT CURSOR FOR
         SELECT S.DTSALDCCUS, S.CDFILIAL, S.CDCENTCUST, S.CDCONTCTBL, S.VRSALDCCUS
           FROM SALDCCUS S, CONTCTBL C
          WHERE (S.CDEMPRESA  = @P_CDEMPRESA)
            AND (S.VRSALDCCUS <> 0)
            AND (S.CDFILIAL   = @C_FILIAL_CDFILIAL)
            AND (S.CDCENTCUST = @C_FILIAL_CDCENTCUST)
            AND (S.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)
            AND (S.CDCONTCTBL = C.CDCONTCTBL)
            AND (C.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 C_SALDANT;
      FETCH NEXT FROM C_SALDANT INTO @C_SALDANT_DTSALDCCUS, @C_SALDANT_CDFILIAL, @C_SALDANT_CDCENTCUST,
                                     @C_SALDANT_CDCONTCTBL, @C_SALDANT_VRSALDCCUS;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         INSERT INTO CTBTEMP
            (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
             CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
             CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
         VALUES
            (@P_NRCTBTEMP, @C_SALDANT_CDCONTCTBL, @C_SALDANT_CDFILIAL, @C_SALDANT_CDCENTCUST,
             '', @C_SALDANT_DTSALDCCUS, '', @C_SALDANT_VRSALDCCUS,
             '', '', @V_NRSEQ, '0');
                                                                                                                      
         SET @V_NRSEQ = @V_NRSEQ + 1;
                                                                                                                      
         FETCH NEXT FROM C_SALDANT INTO @C_SALDANT_DTSALDCCUS, @C_SALDANT_CDFILIAL, @C_SALDANT_CDCENTCUST,
                                        @C_SALDANT_CDCONTCTBL, @C_SALDANT_VRSALDCCUS;
      END;
      CLOSE C_SALDANT;
      DEALLOCATE C_SALDANT;
                                                                                                                      
      IF (@P_DATAINIC <> @V_DTSOMA) BEGIN
         DECLARE C_SOMALANC CURSOR FOR
            SELECT LA.CDCONTCTBL,   LC.CDFILIAL,
                   LC.CDCENTCUST, SUM(LC.VRLANCCUST) AS VALOR
              FROM LANCCONT LA, LANCCCUSTO LC
             WHERE (LA.CDEMPRESA   = @P_CDEMPRESA)
               AND (LA.CDCONTCTBL  BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)
               AND (LC.CDEMPRESA   = @P_CDEMPRESA)
               AND (LC.CDFILIAL    = @C_FILIAL_CDFILIAL)
               AND (LC.CDCENTCUST  = @C_FILIAL_CDCENTCUST)
               AND (LC.DTCOMPCUSTO BETWEEN @V_DTSOMA AND @P_DATAINIC - 1)
               AND (LA.NRLANCCONT  = LC.NRLANCCONT)
               AND (LA.NRSEQLANC   = LC.NRSEQLANC)
             GROUP BY LA.CDCONTCTBL, LC.CDFILIAL, LC.CDCENTCUST;
                                                                                                                      
         OPEN C_SOMALANC;
         FETCH NEXT FROM C_SOMALANC INTO @C_SOMALANC_CDCONTCTBL, @C_SOMALANC_CDFILIAL, @C_SOMALANC_CDCENTCUST,
                                         @C_SOMALANC_VALOR;
                                                                                                                      
         WHILE (@@FETCH_STATUS = 0) BEGIN
                                                                                                                      
             SELECT @V_CONT = COUNT(*) 
               FROM CTBTEMP C
              WHERE (C.NRCTBTEMP   = @P_NRCTBTEMP) 
                AND (C.CDCENTCUSTT = @C_SOMALANC_CDCENTCUST) 
                AND (C.CDFILIALT   = @C_SOMALANC_CDFILIAL)   
                AND (C.CDCONTCTBLT = @C_SOMALANC_CDCONTCTBL);
                                                                                                                      
             IF (@V_CONT = 0) BEGIN
                INSERT INTO CTBTEMP
                   (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
                    CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
                    CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
                VALUES
                   (@P_NRCTBTEMP, @C_SOMALANC_CDCONTCTBL, @C_SOMALANC_CDFILIAL, @C_SOMALANC_CDCENTCUST,
                    '', (@P_DATAINIC - 1), '', @C_SOMALANC_VALOR,
                    '', '', @V_NRSEQ, '0');
                                                                                                                      
                SET @V_NRSEQ = @V_NRSEQ + 1;
             END
             ELSE BEGIN
                UPDATE CTBTEMP
                   SET VRLANCAMENTT = (VRLANCAMENTT + @C_SOMALANC_VALOR)
                 WHERE (CTBTEMP.NRCTBTEMP   = @P_NRCTBTEMP) 
                   AND (CTBTEMP.CDFILIALT   = @C_SOMALANC_CDFILIAL)  
                   AND (CTBTEMP.CDCENTCUSTT = @C_SOMALANC_CDCENTCUST)
                   AND (CTBTEMP.CDCONTCTBLT = @C_SOMALANC_CDCONTCTBL);
             END;
             FETCH NEXT FROM C_SOMALANC INTO @C_SOMALANC_CDCONTCTBL, @C_SOMALANC_CDFILIAL, @C_SOMALANC_CDCENTCUST,
                                             @C_SOMALANC_VALOR;
         END;
         CLOSE C_SOMALANC;
         DEALLOCATE C_SOMALANC;
      END; /* END IF*/
                                                                                                                      
      DECLARE C_LANC CURSOR FOR 
         SELECT LC.CDFILIAL,   LC.CDCENTCUST,  LA.CDCONTCTBL,
                LC.VRLANCCUST, LA.CDDOCUCONT,  LA.DSHISTLANC,
                LA.NRLANCCONT, LC.DTCOMPCUSTO, LA.CDCONTCOME
           FROM LANCCCUSTO LC, (SELECT L.CDEMPRESA,  L.DTLANCCONT, L.CDCONTCTBL, L.CDDOCUCONT, L.DSHISTLANC,
                                       L.NRLANCCONT, L.CDCONTCOME, L.NRSEQLANC
                                  FROM LANCCONT L
                                 WHERE (L.CDEMPRESA  = @P_CDEMPRESA)
                                   AND (L.CDCONTCTBL BETWEEN @P_CDCONTINI AND @P_CDCONTFIM)) LA
          WHERE (LA.CDEMPRESA   = LC.CDEMPRESA)
            AND (LC.DTCOMPCUSTO BETWEEN @P_DATAINIC  AND @P_DATAFIM)
            AND (LC.CDFILIAL    = @C_FILIAL_CDFILIAL)
            AND (LC.CDCENTCUST  = @C_FILIAL_CDCENTCUST)
            AND (LC.NRLANCCONT  = LA.NRLANCCONT)
            AND (LC.NRSEQLANC   = LA.NRSEQLANC);
                                                                                                                      
      OPEN C_LANC;
      FETCH NEXT FROM C_LANC INTO @C_LANC_CDFILIAL,   @C_LANC_CDCENTCUST,  @C_LANC_CDCONTCTBL,
                                  @C_LANC_VRLANCCUST, @C_LANC_CDDOCUCONT,  @C_LANC_DSHISTLANC,
                                  @C_LANC_NRLANCCONT, @C_LANC_DTCOMPCUSTO, @C_LANC_CDCONTCOME;
                                                                                                                      
      WHILE (@@FETCH_STATUS = 0) BEGIN
         IF (@C_LANC_CDCONTCOME IS NULL) BEGIN
            SELECT @V_CDCONTCOME = MAX(CDCONTCOME)
              FROM LANCCONT
             WHERE CDEMPRESA  = @P_CDEMPRESA
               AND NRLANCCONT = @C_LANC_NRLANCCONT;
         END
         ELSE BEGIN
            SET @V_CDCONTCOME = @C_LANC_CDCONTCOME;
         END;
                                                                                                                      
         INSERT INTO CTBTEMP
             (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
              CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
              CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
         VALUES
             (@P_NRCTBTEMP, @C_LANC_CDCONTCTBL, @C_LANC_CDFILIAL, @C_LANC_CDCENTCUST,
              @V_CDCONTCOME, @C_LANC_DTCOMPCUSTO,@C_LANC_NRLANCCONT, @C_LANC_VRLANCCUST,
              @C_LANC_CDDOCUCONT, @C_LANC_DSHISTLANC, @V_NRSEQ, '1');
                                                                                                                      
         SET @V_NRSEQ = @V_NRSEQ + 1;
         FETCH NEXT FROM C_LANC INTO @C_LANC_CDFILIAL,   @C_LANC_CDCENTCUST,  @C_LANC_CDCONTCTBL,
                                     @C_LANC_VRLANCCUST, @C_LANC_CDDOCUCONT,  @C_LANC_DSHISTLANC,
                                     @C_LANC_NRLANCCONT, @C_LANC_DTCOMPCUSTO, @C_LANC_CDCONTCOME;
      END;
      CLOSE C_LANC;
      DEALLOCATE C_LANC;
                                                                                                                      
      COMMIT;
      FETCH NEXT FROM C_FILIAL INTO @C_FILIAL_CDFILIAL, @C_FILIAL_CDCENTCUST;
   END;
   CLOSE C_FILIAL;
   DEALLOCATE C_FILIAL;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'GERA_RAZAOGER_TEMP','5.05.020';
GO

