CREATE OR REPLACE PROCEDURE SALDO_COMERCIAL_GERAL(
	P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
      P_DATAINIC  IN CONTOPECTBL.DTFINALANC%TYPE) 
IS

VRSALDO_ANT  SALDCOME.VRSALDCOME%TYPE;
V_DTFINACONT DATE;
DATA_SALDO_ANT DATE;
DATA_SALDO DATE;
DATA_MAX DATE;
DATA_INIC DATE; 
V_CONTSALDO CHAR;
V_IDSALDEMPR  CHAR;
V_VERSAO     VARCHAR2(1);
VRSALDO  SALDCOME.VRSALDCOME%TYPE;

BEGIN 

    SELECT MAX(SUBSTR(BANNER,7,1)) INTO V_VERSAO FROM V$VERSION
    WHERE BANNER LIKE 'Orac%';

    IF V_VERSAO <> '9' THEN
       COMMIT;
       SET TRANSACTION USE ROLLBACK SEGMENT RBS_TECNISA;
    END IF;
    
      SELECT DTFINACONT INTO V_DTFINACONT
      FROM CONTOPECTBL 
      WHERE CDEMPRESA = P_CDEMPRESA;

      DELETE SALDCOME
      WHERE DTSALDCOME >= P_DATAINIC   AND
            CDEMPRESA =  P_CDEMPRESA;

      DATA_SALDO := LAST_DAY(P_DATAINIC);
      DATA_INIC  := P_DATAINIC;
 
    	WHILE (DATA_SALDO <= V_DTFINACONT) LOOP

         FOR SOMACONTA IN (SELECT IDLANCCOME, CDCONTCOME, CDCONTCTBL,  DTIMPSALDO, VRIMPSALDO, MENOR_DATA, VALORSALDO
                             FROM
                          (SELECT NVL(I.IDSALDCOME, LANCCONT.IDLANCCOME) AS IDLANCCOME,
                                  NVL(I.CDSALDCOME, LANCCONT.CDCONTCOME) AS CDCONTCOME,
                                  NVL(I.CDCONTCTBL, LANCCONT.CDCONTCTBL) AS CDCONTCTBL,
                                  SUM(LANCCONT.VRLANCAMENT) AS VALORSALDO,
                                  DTIMPSALDO, ROUND(I.VRIMPSALDO, 2) VRIMPSALDO,
                                  MIN(LANCCONT.DTLANCCONT) AS MENOR_DATA
                             FROM CONTCTBL  LEFT JOIN LANCCONT
                                                   ON (LANCCONT.CDEMPRESA  = P_CDEMPRESA) AND
                                                      (LANCCONT.DTLANCCONT BETWEEN DATA_INIC AND DATA_SALDO)AND
                                                      (LANCCONT.CDCONTCOME IS NOT NULL) AND
                                                      (LANCCONT.IDLANCCOME IS NOT NULL) AND
                                                      (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
                                                      (CONTCTBL.IDCONTCCUS <> 'R')
                                            LEFT JOIN IMPLANTSALDCOME I
                                                   ON I.CDEMPRESA   = P_CDEMPRESA AND
                                                    I.DTIMPSALDO BETWEEN DATA_INIC AND DATA_SALDO AND
                                                    I.CDCONTCTBL = CONTCTBL.CDCONTCTBL 

                            GROUP BY NVL(I.IDSALDCOME, LANCCONT.IDLANCCOME),(I.CDSALDCOME, LANCCONT.CDCONTCOME),
                                     NVL(I.CDCONTCTBL, LANCCONT.CDCONTCTBL), DTIMPSALDO, ROUND(I.VRIMPSALDO, 2))
                            WHERE  MENOR_DATA IS NOT NULL
                                OR DTIMPSALDO IS NOT NULL
                             ORDER BY 1) LOOP
          IF SOMACONTA.DTIMPSALDO IS NULL THEN
             DATA_SALDO := SOMACONTA.MENOR_DATA;
             DATA_SALDO := LAST_DAY(DATA_SALDO);
             VRSALDO    := SOMACONTA.VALORSALDO;
          ELSE
             DATA_SALDO := SOMACONTA.DTIMPSALDO;
             VRSALDO    := SOMACONTA.VRIMPSALDO;
			 VRSALDO_ANT := 0 ;
          END IF;
          SELECT COUNT(*) INTO V_CONTSALDO 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) AND (SOMACONTA.DTIMPSALDO IS NULL) THEN
             SELECT VRSALDCOME INTO VRSALDO_ANT 
                 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)));
          ELSE
              VRSALDO_ANT := 0;
          END IF;

          INSERT INTO SALDCOME
	           (CDEMPRESA, IDSALDCOME, CDSALDCOME, CDCONTCTBL, DTSALDCOME, VRSALDCOME)
	            VALUES
                 (P_CDEMPRESA, SOMACONTA.IDLANCCOME, SOMACONTA.CDCONTCOME,SOMACONTA.CDCONTCTBL,
                  DATA_SALDO, (NVL(VRSALDO,0) + VRSALDO_ANT) );

        END LOOP;

        DATA_SALDO := ADD_MONTHS(DATA_SALDO,1);
        DATA_INIC  := ADD_MONTHS(DATA_INIC,1);

      END LOOP;

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

-- VERSAO --
BEGIN ATUVEROBJ('SALDO_COMERCIAL_GERAL','6.49.029'); END;
/

