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

CREATE PROCEDURE GERA_DREGERENCIAL
    @P_CDEMPRESA   VARCHAR(02),
    @P_NRLISTA     VARCHAR(10),
    @P_CDTIPODEMO  VARCHAR(02),
    @P_DTINICIAL   DATETIME,
    @P_DTFINAL     DATETIME,
    @P_NRCONTADOR  VARCHAR(10),
    @P_VRTXADMCENT DOUBLE PRECISION = 0,
    @P_NRCONTABGER VARCHAR(10) OUTPUT

AS

   DECLARE @V_VRCONTDATA   NUMERIC;
   DECLARE @V_CDCHAVE      VARCHAR(04);
   DECLARE @V_VRRESULTADO  DOUBLE PRECISION;
   DECLARE @V_SALDITVAR    DOUBLE PRECISION;
   DECLARE @V_VRDIVISAO1   DOUBLE PRECISION;
   DECLARE @V_VRDIVISAO2   DOUBLE PRECISION;
   DECLARE @V_VRPARCELA1   DOUBLE PRECISION;
   DECLARE @V_VRPARCELA2   DOUBLE PRECISION;
   DECLARE @V_VRTXADMCENT  DOUBLE PRECISION;

   DECLARE @V_NRCONTABGER  VARCHAR(20);

   /*Variaveis cursor C_FILIAL*/
   DECLARE @V_CDFILIAL   VARCHAR(04);
   DECLARE @V_CDAREAGERE VARCHAR(03);

   /*Variaveis cursor C_ITEMSALD, C_ITEMVAR e C_VALORITCTB*/
   DECLARE @V_CDCONTCTBL   VARCHAR(20);
   DECLARE @V_CDITEMSTRU   VARCHAR(04);

   /*Variaveis cursor C_DATE*/
   DECLARE @V_DTMES        DATETIME;

   /*Variaveis cursor C_VALORITCTB*/
   DECLARE @V_VRSALDCONT   DOUBLE PRECISION;

   /*Variaveis cursor C_VALORITVAR*/
   DECLARE @V_CDORIGDEMO   VARCHAR(23);
   DECLARE @V_IDOPERACAO   VARCHAR(01);
   DECLARE @V_CDSEGUAPUR   VARCHAR(03);
   DECLARE @V_NMITEMSTRU   VARCHAR(50);
   DECLARE @V_VRREFETMP    DOUBLE PRECISION;

BEGIN
   SET @V_VRCONTDATA = 0;
   IF NOT @P_NRCONTADOR IS NULL BEGIN
      SET @V_NRCONTABGER = @P_NRCONTADOR;

      DELETE
        FROM TMPCONTABGER
       WHERE NRCONTABGER = @P_NRCONTADOR;
   END
   ELSE BEGIN
      EXEC TEKNISA.NOVO_CODIGO 'GERA_DREGERENCIAL', @P_SEQUENCIAL = @V_NRCONTABGER OUTPUT, @P_QTDE = 1;
      SET @V_NRCONTABGER = SUBSTRING(@V_NRCONTABGER, 11, 10);
   END;

   DECLARE C_FILIAL CURSOR FOR
      SELECT L.CDLISTA AS CDFILIAL, F.CDAREAGERE
        FROM LISTA L, FILIAL F
       WHERE L.NRLISTA = @P_NRLISTA
         AND L.CDLISTA = @V_CDFILIAL
       ORDER BY CDLISTA
   OPEN C_ITEMSALD;

   FETCH NEXT FROM C_FILIAL INTO @V_CDFILIAL, @V_CDAREAGERE
   WHILE (@@FETCH_STATUS = 0) BEGIN
      SET @V_VRCONTDATA = 0;

      DECLARE C_ITEMSALD CURSOR FOR
         SELECT DISTINCT C.CDCONTCTBL, I.CDITEMSTRU
           FROM ITEMAPUCTB I, CONTCTBL C
          WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO + '%'
            AND I.IDTIPOORIG = 'S'
            AND I.CDTIPODEMO = @P_CDTIPODEMO
            AND C.IDTIPOCONT = 'A'
          ORDER BY C.CDCONTCTBL
      OPEN C_ITEMSALD;

      FETCH NEXT FROM C_ITEMSALD INTO @V_CDCONTCTBL, @V_CDITEMSTRU;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         INSERT INTO TMPCONTABGER
            (NRCONTABGER, CDEMPRESATMP, CDFILIALTMP, CDTIPODEMOTMP,
             CDITEMSTRUTMP, CDCONTCTBLTMP,
             DTREFETMP01, DTREFETMP02, DTREFETMP03, DTREFETMP04, DTREFETMP05, DTREFETMP06,
             DTREFETMP07, DTREFETMP08, DTREFETMP09, DTREFETMP10, DTREFETMP11, DTREFETMP12,
             VRREFETMP01, VRREFETMP02, VRREFETMP03, VRREFETMP04, VRREFETMP05, VRREFETMP06,
             VRREFETMP07, VRREFETMP08, VRREFETMP09, VRREFETMP10, VRREFETMP11, VRREFETMP12)
         VALUES
            (@V_NRCONTABGER, @P_CDEMPRESA, @V_CDFILIAL, @P_CDTIPODEMO,
             @V_CDITEMSTRU, @V_CDCONTCTBL,
             @P_DTINICIAL, DATEADD(MM, 1, @P_DTINICIAL), DATEADD(MM, 2, @P_DTINICIAL), DATEADD(MM, 3, @P_DTINICIAL),
             DATEADD(MM, 4, @P_DTINICIAL), DATEADD(MM, 5, @P_DTINICIAL), DATEADD(MM, 6, @P_DTINICIAL),
             DATEADD(MM, 7, @P_DTINICIAL), DATEADD(MM, 8, @P_DTINICIAL), DATEADD(MM, 9, @P_DTINICIAL),
             DATEADD(MM, 10, @P_DTINICIAL), DATEADD(MM, 11, @P_DTINICIAL),
             0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

         FETCH NEXT FROM C_ITEMSALD INTO @V_CDCONTCTBL, @V_CDITEMSTRU;
      END;
      CLOSE C_ITEMSALD;
      DEALLOCATE C_ITEMSALD;

      DECLARE C_ITEMVAR CURSOR FOR
         SELECT DISTINCT I.CDITEMSTRU
           FROM ITEMDEMOF D,
                ITEMAPUCTB I LEFT JOIN (SELECT DISTINCT T.CDTIPODEMOTMP, T.CDITEMSTRUTMP
                                          FROM TMPCONTABGER T
                                         WHERE T.CDEMPRESATMP = @P_CDEMPRESA
                                           AND T.NRCONTABGER = @V_NRCONTABGER
                                           AND T.CDFILIALTMP = @V_CDFILIAL) T ON (SUBSTRING(I.CDORIGDEMO,1,2) = T.CDTIPODEMOTMP AND
                                                                                  SUBSTRING(I.CDORIGDEMO,3,4) = T.CDITEMSTRUTMP)
          WHERE I.CDTIPODEMO = D.CDTIPODEMO
            AND I.CDITEMSTRU = D.CDITEMSTRU
            AND I.IDTIPOORIG = 'V'
          ORDER BY I.CDITEMSTRU
      OPEN C_ITEMVAR;

      FETCH NEXT FROM C_ITEMVAR INTO @V_CDITEMSTRU;
      WHILE (@@FETCH_STATUS = 0) BEGIN

         INSERT INTO TMPCONTABGER
            (NRCONTABGER, CDEMPRESATMP, CDFILIALTMP, CDTIPODEMOTMP,
             CDITEMSTRUTMP, CDCONTCTBLTMP,
             DTREFETMP01, DTREFETMP02, DTREFETMP03, DTREFETMP04, DTREFETMP05, DTREFETMP06,
             DTREFETMP07, DTREFETMP08, DTREFETMP09, DTREFETMP10, DTREFETMP11, DTREFETMP12,
             VRREFETMP01, VRREFETMP02, VRREFETMP03, VRREFETMP04, VRREFETMP05, VRREFETMP06,
             VRREFETMP07, VRREFETMP08, VRREFETMP09, VRREFETMP10, VRREFETMP11, VRREFETMP12)
         VALUES
            (@V_NRCONTABGER, @P_CDEMPRESA, @V_CDFILIAL, @P_CDTIPODEMO,
             @V_CDITEMSTRU, '0000000000',
             @P_DTINICIAL, DATEADD(MM, 1, @P_DTINICIAL), DATEADD(MM, 2, @P_DTINICIAL), DATEADD(MM, 3, @P_DTINICIAL),
             DATEADD(MM, 4, @P_DTINICIAL), DATEADD(MM, 5, @P_DTINICIAL), DATEADD(MM, 6, @P_DTINICIAL),
             DATEADD(MM, 7, @P_DTINICIAL), DATEADD(MM, 8, @P_DTINICIAL), DATEADD(MM, 9, @P_DTINICIAL),
             DATEADD(MM, 10, @P_DTINICIAL), DATEADD(MM, 11, @P_DTINICIAL),
             0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
         FETCH NEXT FROM C_ITEMVAR INTO @V_CDITEMSTRU;
      END;
      CLOSE C_ITEMVAR;
      DEALLOCATE C_ITEMVAR;

      DECLARE C_DATE CURSOR FOR
         SELECT DISTINCT DTMES
           FROM DATA
          WHERE DTMES BETWEEN @P_DTINICIAL AND @P_DTFINAL
          ORDER BY DTMES
      OPEN C_DATE;

      FETCH NEXT FROM C_DATE INTO @V_DTMES;
      WHILE (@@FETCH_STATUS = 0) BEGIN

         SET @V_VRCONTDATA = @V_VRCONTDATA + 1;

         DECLARE C_VALORITCTB CURSOR FOR
            SELECT L.CDCONTCTBL, I.CDITEMSTRU, SUM(VRLANCCUST) AS VRSALDCONT
              FROM LANCCONT L, LANCCCUSTO LC, ITEMAPUCTB I
             WHERE L.CDEMPRESA = @P_CDEMPRESA
               AND L.CDEMPRESA = LC.CDEMPRESA
               AND L.NRLANCCONT = LC.NRLANCCONT
               AND L.NRSEQLANC = LC.NRSEQLANC
               AND LC.CDFILIAL = @V_CDFILIAL
               AND LC.DTCOMPCUSTO BETWEEN @V_DTMES AND (DATEADD(MM, 1, @V_DTMES) - DAY(DATEADD(MM, 1, @V_DTMES)))
               AND L.CDCONTCTBL LIKE I.CDORIGDEMO + '%'
               AND I.IDTIPOORIG = 'S'
               AND I.CDTIPODEMO = @P_CDTIPODEMO
             GROUP BY L.CDCONTCTBL, I.CDITEMSTRU
             ORDER BY L.CDCONTCTBL
         OPEN C_VALORITCTB;

         FETCH NEXT FROM C_VALORITCTB INTO @V_CDCONTCTBL, @V_CDITEMSTRU, @V_VRSALDCONT;
         WHILE (@@FETCH_STATUS = 0) BEGIN

            IF @V_VRCONTDATA = 1 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP01 = @V_DTMES,
                      VRREFETMP01 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 2 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP02 = @V_DTMES,
                      VRREFETMP02 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 3 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP03 = @V_DTMES,
                      VRREFETMP03 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 4 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP04 = @V_DTMES,
                      VRREFETMP04 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 5 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP05 = @V_DTMES,
                      VRREFETMP05 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 6 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP06 = @V_DTMES,
                      VRREFETMP06 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 7 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP07 = @V_DTMES,
                      VRREFETMP07 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 8 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP08 = @V_DTMES,
                      VRREFETMP08 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 9 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP09 = @V_DTMES,
                      VRREFETMP09 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 10 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP10 = @V_DTMES,
                      VRREFETMP10 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 11 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP11 = @V_DTMES,
                      VRREFETMP11 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END
            ELSE IF @V_VRCONTDATA = 12 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP12 = @V_DTMES,
                      VRREFETMP12 = @V_VRSALDCONT
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = @V_CDCONTCTBL;
            END;
            FETCH NEXT FROM C_VALORITCTB INTO @V_CDCONTCTBL, @V_CDITEMSTRU, @V_VRSALDCONT;
         END;
         CLOSE C_VALORITCTB;
         DEALLOCATE C_VALORITCTB;


         SET @V_CDCHAVE = '0';
         SET @V_VRRESULTADO = 0;
         DECLARE C_VALORITVAR CURSOR FOR
            SELECT I.CDITEMSTRU, I.CDORIGDEMO, D.IDOPERACAO, I.CDSEGUAPUR, D.NMITEMSTRU,
                   ISNULL(T.VRREFETMP,0) AS VRREFETMP
              FROM ITEMDEMOF D, ITEMAPUCTB I LEFT JOIN
                   (SELECT T.CDTIPODEMOTMP, T.CDITEMSTRUTMP,
                           SUM(CASE @V_VRCONTDATA
                               WHEN 1 THEN T.VRREFETMP01
                               WHEN 2 THEN T.VRREFETMP02
                               WHEN 3 THEN T.VRREFETMP03
                               WHEN 4 THEN T.VRREFETMP04
                               WHEN 5 THEN T.VRREFETMP05
                               WHEN 6 THEN T.VRREFETMP06
                               WHEN 7 THEN T.VRREFETMP07
                               WHEN 8 THEN T.VRREFETMP08
                               WHEN 9 THEN T.VRREFETMP09
                               WHEN 10 THEN T.VRREFETMP10
                               WHEN 11 THEN T.VRREFETMP11
                               WHEN 12 THEN T.VRREFETMP12
                               ELSE T.VRREFETMP01 END) AS VRREFETMP
                      FROM TMPCONTABGER T
                     WHERE T.CDEMPRESATMP = @P_CDEMPRESA
                       AND T.NRCONTABGER = @V_NRCONTABGER
                       AND T.CDFILIALTMP = @V_CDFILIAL
                       AND (CASE @V_VRCONTDATA
                            WHEN 1 THEN T.DTREFETMP01
                            WHEN 2 THEN T.DTREFETMP02
                            WHEN 3 THEN T.DTREFETMP03
                            WHEN 4 THEN T.DTREFETMP04
                            WHEN 5 THEN T.DTREFETMP05
                            WHEN 6 THEN T.DTREFETMP06
                            WHEN 7 THEN T.DTREFETMP07
                            WHEN 8 THEN T.DTREFETMP08
                            WHEN 9 THEN T.DTREFETMP09
                            WHEN 10 THEN T.DTREFETMP10
                            WHEN 11 THEN T.DTREFETMP11
                            WHEN 12 THEN T.DTREFETMP12
                            ELSE T.DTREFETMP01 END) = @V_DTMES
                     GROUP BY T.CDTIPODEMOTMP, T.CDITEMSTRUTMP) T ON (SUBSTRING(I.CDORIGDEMO,1,2) = T.CDTIPODEMOTMP AND
                                                                      SUBSTRING(I.CDORIGDEMO,3,4) = T.CDITEMSTRUTMP)
             WHERE I.CDTIPODEMO = @P_CDTIPODEMO
               AND I.CDTIPODEMO = D.CDTIPODEMO
               AND I.CDITEMSTRU = D.CDITEMSTRU
               AND I.IDTIPOORIG = 'V'
             ORDER BY I.CDITEMSTRU, I.CDSEGUAPUR
         OPEN C_VALORITVAR;

         FETCH NEXT FROM C_VALORITVAR INTO @V_CDITEMSTRU, @V_CDORIGDEMO, @V_IDOPERACAO,
                                           @V_CDSEGUAPUR, @V_NMITEMSTRU, @V_VRREFETMP;
         WHILE (@@FETCH_STATUS = 0) BEGIN

            IF (@V_CDCHAVE <> @V_CDITEMSTRU) OR ((@V_CDORIGDEMO = '0065') AND (@V_CDORIGDEMO = '0067')) BEGIN
               SET @V_VRRESULTADO = @V_VRREFETMP;

               SELECT @V_SALDITVAR =
                      SUM(CASE @V_VRCONTDATA
                          WHEN 1 THEN T.VRREFETMP01
                          WHEN 2 THEN T.VRREFETMP02
                          WHEN 3 THEN T.VRREFETMP03
                          WHEN 4 THEN T.VRREFETMP04
                          WHEN 5 THEN T.VRREFETMP05
                          WHEN 6 THEN T.VRREFETMP06
                          WHEN 7 THEN T.VRREFETMP07
                          WHEN 8 THEN T.VRREFETMP08
                          WHEN 9 THEN T.VRREFETMP09
                          WHEN 10 THEN T.VRREFETMP10
                          WHEN 11 THEN T.VRREFETMP11
                          WHEN 12 THEN T.VRREFETMP12
                          ELSE T.VRREFETMP01 END)
                 FROM ITEMAPUCTB C, TMPCONTABGER T
                WHERE C.CDTIPODEMO = @P_CDTIPODEMO
                  AND C.CDITEMSTRU = @V_CDITEMSTRU
                  AND T.CDTIPODEMOTMP = SUBSTRING(C.CDORIGDEMO,1,2)
                  AND T.CDITEMSTRUTMP = SUBSTRING(C.CDORIGDEMO,3,4)
                  AND T.CDEMPRESATMP = @P_CDEMPRESA
                  AND T.NRCONTABGER = @V_NRCONTABGER
                  AND T.CDFILIALTMP = @V_CDFILIAL
                  AND C.IDTIPOORIG = 'V'
                  AND (((T.CDITEMSTRUTMP <> '0065') AND (T.CDITEMSTRUTMP <> '0067')) OR (@V_CDITEMSTRU = '0070'))
                  AND EXISTS(SELECT IT.CDTIPODEMO
                             FROM ITEMAPUCTB IT
                             WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                               AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                               AND IT.IDTIPOORIG = 'V')
                  AND (CASE @V_VRCONTDATA
                       WHEN 1 THEN T.DTREFETMP01
                       WHEN 2 THEN T.DTREFETMP02
                       WHEN 3 THEN T.DTREFETMP03
                       WHEN 4 THEN T.DTREFETMP04
                       WHEN 5 THEN T.DTREFETMP05
                       WHEN 6 THEN T.DTREFETMP06
                       WHEN 7 THEN T.DTREFETMP07
                       WHEN 8 THEN T.DTREFETMP08
                       WHEN 9 THEN T.DTREFETMP09
                       WHEN 10 THEN T.DTREFETMP10
                       WHEN 11 THEN T.DTREFETMP11
                       WHEN 12 THEN T.DTREFETMP12
                       ELSE T.DTREFETMP01 END) = @V_DTMES;

               IF @V_SALDITVAR IS NOT NULL BEGIN
                  SET @V_VRRESULTADO = @V_VRRESULTADO + @V_SALDITVAR;
               END;

               IF @V_IDOPERACAO = 'D' BEGIN
                  SET @V_VRDIVISAO1 = @V_VRRESULTADO;
                  SET @V_VRDIVISAO2 = 0;
               END;

               IF @V_IDOPERACAO = 'M' BEGIN
                  SET @V_VRPARCELA1 = @V_VRRESULTADO;
                  SET @V_VRPARCELA2 = 0;
               END;
            END
            ELSE BEGIN
               IF @V_IDOPERACAO = 'S' BEGIN
                  SET @V_VRRESULTADO = @V_VRRESULTADO + @V_VRREFETMP;
               END
               ELSE IF @V_IDOPERACAO = 'U' BEGIN
                  SET @V_VRRESULTADO = @V_VRRESULTADO - @V_VRREFETMP;
               END
               ELSE IF @V_IDOPERACAO = 'M' BEGIN
                  IF @V_NMITEMSTRU = 'TAR' BEGIN
                      SELECT @V_VRPARCELA2 = ISNULL(MAX(VRTXDESPREGE),0)
                        FROM TXDESPREGE
                       WHERE CDAREAGERE = @V_CDAREAGERE
                         AND DTTXDESPREGE = @V_DTMES;

                     IF @V_VRPARCELA2 = 0 BEGIN
                        SELECT @V_VRPARCELA2 = ISNULL(MAX(VRTXDESPREGE),0)
                          FROM TXDESPREGE
                         WHERE CDAREAGERE = @V_CDAREAGERE
                           AND DTTXDESPREGE = DATEADD(MM, -1, @V_DTMES);
                     END;

                     IF (@V_VRPARCELA1 <> 0) AND (@V_VRPARCELA2 <> 0) BEGIN
                        SET @V_VRRESULTADO = (@V_VRPARCELA1 * (@V_VRPARCELA2/100)) * (-1);
                     END
                     ELSE BEGIN
                        SET @V_VRRESULTADO = 0;
                     END;
                  END
                  ELSE IF @V_NMITEMSTRU = 'TAC' BEGIN
                     IF (@V_VRPARCELA1 <> 0) BEGIN
                        SELECT @V_VRTXADMCENT = ISNULL(MAX(VRTXDESPREGE),0)
                          FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                         WHERE T.CDAREAGERE = FL.CDAREAGERE
                           AND FL.CDSUPERVISOR = O.CDOPERADOR
                           AND O.NMOPERADOR = 'TAC'
                           AND DTTXDESPREGE = @V_DTMES;

                        IF @V_VRTXADMCENT = 0 BEGIN
                           SELECT @V_VRTXADMCENT = ISNULL(MAX(VRTXDESPREGE),0)
                             FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                            WHERE T.CDAREAGERE = FL.CDAREAGERE
                              AND FL.CDSUPERVISOR = O.CDOPERADOR
                              AND O.NMOPERADOR = 'TAC'
                              AND DTTXDESPREGE = DATEADD(MM, -1, @V_DTMES);
                        END;

                        SET @V_VRRESULTADO = (@V_VRPARCELA1 * (@V_VRTXADMCENT/100)) * (-1);
                     END
                     ELSE BEGIN
                        SET @V_VRRESULTADO = 0;
                     END;
                  END
                  ELSE BEGIN
                     IF (@V_VRPARCELA1 <> 0) AND (@V_VRPARCELA2 <> 0) BEGIN
                        SET @V_VRRESULTADO = (@V_VRPARCELA1 * @V_VRPARCELA2);
                     END
                     ELSE BEGIN
                        SET @V_VRRESULTADO = 0;
                     END;
                  END;
               END
               ELSE IF @V_IDOPERACAO = 'D' BEGIN
                  IF @V_VRDIVISAO1 = 0 BEGIN
                     SET @V_VRDIVISAO1 = ABS(@V_VRREFETMP);
                  END
                  ELSE BEGIN
                     IF ABS(@V_VRREFETMP) = 0 BEGIN
                        SELECT @V_VRDIVISAO2 =
                               ABS(ISNULL(SUM(CASE @V_VRCONTDATA
                                              WHEN 1 THEN T.VRREFETMP01
                                              WHEN 2 THEN T.VRREFETMP02
                                              WHEN 3 THEN T.VRREFETMP03
                                              WHEN 4 THEN T.VRREFETMP04
                                              WHEN 5 THEN T.VRREFETMP05
                                              WHEN 6 THEN T.VRREFETMP06
                                              WHEN 7 THEN T.VRREFETMP07
                                              WHEN 8 THEN T.VRREFETMP08
                                              WHEN 9 THEN T.VRREFETMP09
                                              WHEN 10 THEN T.VRREFETMP10
                                              WHEN 11 THEN T.VRREFETMP11
                                              WHEN 12 THEN T.VRREFETMP12
                                              ELSE T.VRREFETMP01 END),0))
                          FROM ITEMAPUCTB C, TMPCONTABGER T
                         WHERE C.CDTIPODEMO = @P_CDTIPODEMO
                           AND C.CDITEMSTRU = @V_CDITEMSTRU
                           AND T.CDTIPODEMOTMP = SUBSTRING(@V_CDORIGDEMO,1,2)
                           AND T.CDITEMSTRUTMP = SUBSTRING(@V_CDORIGDEMO,3,4)
                           AND T.CDEMPRESATMP = @P_CDEMPRESA
                           AND T.NRCONTABGER = @V_NRCONTABGER
                           AND T.CDFILIALTMP = @V_CDFILIAL
                           AND C.IDTIPOORIG = 'V'
                           AND C.CDORIGDEMO = @V_CDORIGDEMO
                           AND EXISTS(SELECT IT.CDTIPODEMO
                                      FROM ITEMAPUCTB IT
                                      WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                                        AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                                        AND IT.IDTIPOORIG = 'V')
                           AND (CASE @V_VRCONTDATA
                                WHEN 1 THEN T.DTREFETMP01
                                WHEN 2 THEN T.DTREFETMP02
                                WHEN 3 THEN T.DTREFETMP03
                                WHEN 4 THEN T.DTREFETMP04
                                WHEN 5 THEN T.DTREFETMP05
                                WHEN 6 THEN T.DTREFETMP06
                                WHEN 7 THEN T.DTREFETMP07
                                WHEN 8 THEN T.DTREFETMP08
                                WHEN 9 THEN T.DTREFETMP09
                                WHEN 10 THEN T.DTREFETMP10
                                WHEN 11 THEN T.DTREFETMP11
                                WHEN 12 THEN T.DTREFETMP12
                                ELSE T.DTREFETMP01 END) = @V_DTMES;
                     END
                     ELSE BEGIN
                        SET @V_VRDIVISAO2 = ABS(@V_VRREFETMP);
                     END;
                  END;

                  IF (@V_VRDIVISAO1 <> 0) AND (@V_VRDIVISAO2 <> 0) BEGIN
                     SET @V_VRRESULTADO = (ABS(@V_VRDIVISAO2) / ABS(@V_VRDIVISAO1)) * 100;
                  END
                  ELSE BEGIN
                     SET @V_VRRESULTADO = 0;
                  END;
               END;
            END;

            IF @V_VRCONTDATA = 1 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP01 = @V_DTMES,
                      VRREFETMP01 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 2 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP02 = @V_DTMES,
                      VRREFETMP02 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 3 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP03 = @V_DTMES,
                      VRREFETMP03 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 4 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP04 = @V_DTMES,
                      VRREFETMP04 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 5 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP05 = @V_DTMES,
                      VRREFETMP05 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 6 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP06 = @V_DTMES,
                      VRREFETMP06 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 7 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP07 = @V_DTMES,
                      VRREFETMP07 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 8 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP08 = @V_DTMES,
                      VRREFETMP08 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 9 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP09 = @V_DTMES,
                      VRREFETMP09 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 10 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP10 = @V_DTMES,
                      VRREFETMP10 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 11 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP11 = @V_DTMES,
                      VRREFETMP11 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END
            ELSE IF @V_VRCONTDATA = 12 BEGIN
               UPDATE TMPCONTABGER
                  SET DTREFETMP12 = @V_DTMES,
                      VRREFETMP12 = @V_VRRESULTADO
                WHERE NRCONTABGER = @V_NRCONTABGER
                  AND CDEMPRESATMP = @P_CDEMPRESA
                  AND CDFILIALTMP = @V_CDFILIAL
                  AND CDTIPODEMOTMP = @P_CDTIPODEMO
                  AND CDITEMSTRUTMP = @V_CDITEMSTRU
                  AND CDCONTCTBLTMP = '0000000000';
            END;

            SET @V_CDCHAVE = @V_CDITEMSTRU;
            FETCH NEXT FROM C_VALORITVAR INTO @V_CDITEMSTRU, @V_CDORIGDEMO, @V_IDOPERACAO,
                                              @V_CDSEGUAPUR, @V_NMITEMSTRU, @V_VRREFETMP;
         END;
         CLOSE C_VALORITVAR;
         DEALLOCATE C_VALORITVAR;

         FETCH NEXT FROM C_DATE INTO @V_DTMES;
      END;
      CLOSE C_DATE;
      DEALLOCATE C_DATE;

      COMMIT;
      FETCH NEXT FROM C_FILIAL INTO @V_CDFILIAL, @V_CDAREAGERE
   END;
   CLOSE C_FILIAL;
   DEALLOCATE C_FILIAL;

   SET @P_NRCONTABGER = @V_NRCONTABGER;
END;
GO
