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

CREATE PROCEDURE SALDO_COMERCIAL_GERAL
	                 @P_CDEMPRESA VARCHAR(02),
                    @P_DATAINIC  DATETIME
AS

DECLARE @VRSALDO_ANT    DOUBLE PRECISION;
DECLARE @V_DTFINACONT   DATETIME;
DECLARE @DATA_SALDO_ANT DATETIME;
DECLARE @DATA_SALDO     DATETIME;
DECLARE @DATA_MAX       DATETIME;
DECLARE @DATA_INIC      DATETIME;
DECLARE @V_CONTSALDO    INTEGER;
DECLARE @V_IDSALDEMPR   VARCHAR(01);

DECLARE @SOMACONTA_IDLANCCOME VARCHAR(01);
DECLARE @SOMACONTA_CDCONTCOME VARCHAR(21);
DECLARE @SOMACONTA_CDCONTCTBL VARCHAR(20);
DECLARE @SOMACONTA_VALORSALDO DOUBLE PRECISION;

BEGIN

   SELECT @V_DTFINACONT = DTFINACONT
   FROM CONTOPECTBL
   WHERE (CDEMPRESA = @P_CDEMPRESA);

   DELETE FROM SALDCOME
   WHERE (DTSALDCOME >= @P_DATAINIC)   AND
         (CDEMPRESA   = @P_CDEMPRESA);

   SET @DATA_SALDO = DATEADD(MM, 1, @P_DATAINIC) - DAY(DATEADD(MM, 1, @P_DATAINIC));
   SET @DATA_INIC  = @P_DATAINIC;

   WHILE (@DATA_SALDO <= @V_DTFINACONT) BEGIN
      DECLARE SOMACONTA CURSOR FOR
         SELECT LANCCONT.IDLANCCOME, LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL,
                 SUM(LANCCONT.VRLANCAMENT) AS VALORSALDO
	  FROM LANCCONT,CONTCTBL
	 WHERE (LANCCONT.CDEMPRESA  = @P_CDEMPRESA) AND                     
               (LANCCONT.DTLANCCONT BETWEEN @DATA_INIC AND @DATA_SALDO) AND
               (LANCCONT.CDCONTCOME IS NOT NULL) AND
               (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
               (CONTCTBL.IDCONTCCUS <> 'R')
	  GROUP BY LANCCONT.IDLANCCOME,LANCCONT.CDCONTCOME,LANCCONT.CDCONTCTBL;

      OPEN SOMACONTA;
      FETCH NEXT FROM SOMACONTA INTO @SOMACONTA_IDLANCCOME, @SOMACONTA_CDCONTCOME,
                                     @SOMACONTA_CDCONTCTBL, @SOMACONTA_VALORSALDO;

      WHILE(@@FETCH_STATUS = 0) BEGIN
         SELECT @V_CONTSALDO = COUNT(*)
         FROM SALDCOME
         WHERE (SALDCOME.CDEMPRESA  = @P_CDEMPRESA)           AND
               (SALDCOME.IDSALDCOME = @SOMACONTA_IDLANCCOME)  AND
               (SALDCOME.CDSALDCOME = @SOMACONTA_CDCONTCOME)  AND
	       (SALDCOME.CDCONTCTBL = @SOMACONTA_CDCONTCTBL)  AND
               (SALDCOME.DTSALDCOME IN (SELECT MAX(DTSALDCOME)
                                        FROM SALDCOME
                                        WHERE (SALDCOME.CDEMPRESA  = @P_CDEMPRESA)          AND
                                              (SALDCOME.IDSALDCOME = @SOMACONTA_IDLANCCOME) AND
                                              (SALDCOME.CDSALDCOME = @SOMACONTA_CDCONTCOME) AND
                                              (SALDCOME.CDCONTCTBL = @SOMACONTA_CDCONTCTBL) AND
                                              (SALDCOME.DTSALDCOME < @DATA_INIC)));

         IF (@V_CONTSALDO > 0.0) BEGIN
            SELECT @VRSALDO_ANT = VRSALDCOME
            FROM SALDCOME
	         WHERE (SALDCOME.CDEMPRESA  = @P_CDEMPRESA)           AND
                  (SALDCOME.IDSALDCOME = @SOMACONTA_IDLANCCOME)  AND
                  (SALDCOME.CDSALDCOME = @SOMACONTA_CDCONTCOME)  AND
	               (SALDCOME.CDCONTCTBL = @SOMACONTA_CDCONTCTBL)  AND
                  (SALDCOME.DTSALDCOME IN (SELECT MAX(DTSALDCOME)
                                           FROM SALDCOME
                                           WHERE (SALDCOME.CDEMPRESA  = @P_CDEMPRESA)          AND
                                                 (SALDCOME.IDSALDCOME = @SOMACONTA_IDLANCCOME) AND
                                                 (SALDCOME.CDSALDCOME = @SOMACONTA_CDCONTCOME) AND
	                                              (SALDCOME.CDCONTCTBL = @SOMACONTA_CDCONTCTBL) AND
                                                 (SALDCOME.DTSALDCOME < @DATA_INIC)));
         END;
         ELSE BEGIN
            SET @VRSALDO_ANT = 0.0;
         END;

         INSERT INTO SALDCOME
	         (CDEMPRESA, IDSALDCOME, CDSALDCOME, CDCONTCTBL, DTSALDCOME, VRSALDCOME)
	      VALUES
            (@P_CDEMPRESA, @SOMACONTA_IDLANCCOME, @SOMACONTA_CDCONTCOME, @SOMACONTA_CDCONTCTBL,
             @DATA_SALDO, (@SOMACONTA_VALORSALDO + @VRSALDO_ANT) );

         FETCH NEXT FROM SOMACONTA INTO @SOMACONTA_IDLANCCOME, @SOMACONTA_CDCONTCOME,
                                        @SOMACONTA_CDCONTCTBL, @SOMACONTA_VALORSALDO;

      END;

      CLOSE SOMACONTA;
      DEALLOCATE SOMACONTA;

      SET @DATA_SALDO = DATEADD(MM, 1, @DATA_SALDO);
      SET @DATA_INIC  = DATEADD(MM, 1, @DATA_INIC);

   END;

/*   COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
      	ROLLBACK;
	  	RAISE;*/
END;
GO

