CREATE OR REPLACE PROCEDURE ATUALIZA_SALDO_COMERCIAL(
	P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE)
IS
BEGIN 
   DECLARE
   VRSALDO_ANT    SALDCOME.VRSALDCOME%TYPE;
   V_DTFINACONT   DATE;
   V_DATAINIC     DATE;
   DATA_SALDO_ANT DATE;
   DATA_MAX       DATE;
   V_CONTSALDO    CHAR;
   V_IDSALDEMPR   CHAR;
   V_NRSEQ        VARCHAR(10);
   CURSOR C_LANCCOME (P_DATAINIC DATE) IS
      SELECT LANCCONT.CDEMPRESA, LANCCONT.CDCONTCTBL, VRLANCAMENT,
             SALDCOME.CDEMPRESA, SALDCOME.CDCONTCTBL, DTSALDCOME, VRSALDCOME
      FROM LANCCONT, SALDCOME, CONTCTBL
      WHERE (LANCCONT.CDEMPRESA   = P_CDEMPRESA)         AND
            (LANCCONT.IDSALDLANCO = 'N')                  AND
            (SALDCOME.CDEMPRESA   = P_CDEMPRESA)         AND
            (LANCCONT.CDCONTCTBL  = SALDCOME.CDCONTCTBL)  AND
            (LANCCONT.CDCONTCOME  = SALDCOME.CDSALDCOME)  AND
            (SALDCOME.DTSALDCOME >= P_DATAINIC)          AND
            (LANCCONT.CDCONTCTBL  = CONTCTBL.CDCONTCTBL)  AND
            (CONTCTBL.IDCONTCCUS <> 'R')
       FOR UPDATE;
   CURSOR C_LANCDEL (P_DATAINIC DATE) IS
      SELECT LANCOMEEX.NRSEQLANCOM
      FROM LANCOMEEX, CONTCTBL
      WHERE (LANCOMEEX.CDEMPRESA    = P_CDEMPRESA) AND
            (LANCOMEEX.DTLANCCONCOM >= P_DATAINIC) AND
            (LANCOMEEX.CDCONTALACOM = CONTCTBL.CDCONTCTBL) AND
            (CONTCTBL.IDCONTCCUS <> 'R')
      FOR UPDATE;
   BEGIN
      SELECT MIN(DTLANCCONT) INTO V_DATAINIC 
      FROM LANCCONT, CONTCTBL
      WHERE (LANCCONT.CDEMPRESA   = P_CDEMPRESA) AND
            (LANCCONT.IDSALDLANCO = 'N')          AND
            (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
            (CONTCTBL.IDCONTCCUS <> 'R');
      IF V_DATAINIC IS NULL THEN
         SELECT MIN(DTLANCCONCOM) INTO V_DATAINIC
         FROM LANCOMEEX, CONTCTBL
         WHERE (CDEMPRESA = P_CDEMPRESA) AND
               (LANCOMEEX.CDCONTALACOM = CONTCTBL.CDCONTCTBL) AND
               (CONTCTBL.IDCONTCCUS <> 'R');
      END IF;
      OPEN  C_LANCCOME(V_DATAINIC);
      CLOSE C_LANCCOME;
      OPEN C_LANCDEL(V_DATAINIC);
      FOR SOMACONTA IN
         (SELECT LANCCONT.IDLANCCOME, LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL,
                 LANCCONT.DTLANCCONT, SUM(LANCCONT.VRLANCAMENT) AS VALORSALDO
          FROM LANCCONT,CONTCTBL
          WHERE (LANCCONT.CDEMPRESA   = P_CDEMPRESA) AND
                (LANCCONT.IDSALDLANCO = 'N')         AND
                (LANCCONT.CDCONTCOME IS NOT NULL) AND
                (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
                (CONTCTBL.IDCONTCCUS <> 'R')
          GROUP BY LANCCONT.IDLANCCOME, LANCCONT.CDCONTCOME,
                   LANCCONT.CDCONTCTBL, LANCCONT.DTLANCCONT
          UNION ALL
         SELECT LANCOMEEX.IDCONTCOMCOM AS IDLANCCOME, LANCOMEEX.CDCONTCOMCOM AS CDCONTCOME,
                 LANCOMEEX.CDCONTALACOM AS CDCONTCTBL, LANCOMEEX.DTLANCCONCOM AS DTLANCCONT,
                 SUM(LANCOMEEX.VRLANCCONCOM) AS VALORSALDO
          FROM LANCOMEEX, CONTCTBL
          WHERE (LANCOMEEX.CDEMPRESA = P_CDEMPRESA) AND
                (LANCOMEEX.CDCONTALACOM = CONTCTBL.CDCONTCTBL) AND
                (CONTCTBL.IDCONTCCUS <> 'R')
          GROUP BY LANCOMEEX.IDCONTCOMCOM, LANCOMEEX.CDCONTCOMCOM,
                   LANCOMEEX.CDCONTALACOM, LANCOMEEX.DTLANCCONCOM ) LOOP
          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 < SOMACONTA.DTLANCCONT)));
         IF (V_CONTSALDO > 0) THEN
            SELECT DTSALDCOME, VRSALDCOME INTO DATA_SALDO_ANT, 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 < SOMACONTA.DTLANCCONT)));
         ELSE
            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 = LAST_DAY(SOMACONTA.DTLANCCONT));
         IF (V_CONTSALDO = 0) THEN
            INSERT INTO SALDCOME
               (CDEMPRESA, IDSALDCOME, CDSALDCOME, CDCONTCTBL, DTSALDCOME, VRSALDCOME)
            VALUES
               (P_CDEMPRESA, SOMACONTA.IDLANCCOME, SOMACONTA.CDCONTCOME,SOMACONTA.CDCONTCTBL,
                LAST_DAY(SOMACONTA.DTLANCCONT), (SOMACONTA.VALORSALDO + VRSALDO_ANT));
         ELSE
            UPDATE SALDCOME
            SET VRSALDCOME = SALDCOME.VRSALDCOME + SOMACONTA.VALORSALDO
            WHERE (SALDCOME.DTSALDCOME = LAST_DAY(SOMACONTA.DTLANCCONT)) AND
                  (SALDCOME.CDEMPRESA  = P_CDEMPRESA)                    AND
                  (SALDCOME.IDSALDCOME = SOMACONTA.IDLANCCOME)           AND
                  (SALDCOME.CDSALDCOME = SOMACONTA.CDCONTCOME)           AND
                  (SALDCOME.CDCONTCTBL = SOMACONTA.CDCONTCTBL);
         END IF;
         SELECT MAX(DTSALDCOME) INTO DATA_MAX
         FROM SALDCOME
         WHERE (SALDCOME.CDEMPRESA  = P_CDEMPRESA)          AND
               (SALDCOME.IDSALDCOME = SOMACONTA.IDLANCCOME) AND
               (SALDCOME.CDSALDCOME = SOMACONTA.CDCONTCOME) AND
               (SALDCOME.CDCONTCTBL = SOMACONTA.CDCONTCTBL);
         IF (DATA_MAX > SOMACONTA.DTLANCCONT) THEN
            UPDATE SALDCOME
            SET VRSALDCOME = VRSALDCOME + SOMACONTA.VALORSALDO
            WHERE (SALDCOME.DTSALDCOME > LAST_DAY(SOMACONTA.DTLANCCONT)) AND
                  (SALDCOME.CDEMPRESA  = P_CDEMPRESA)                    AND
                  (SALDCOME.IDSALDCOME = SOMACONTA.IDLANCCOME)           AND
                  (SALDCOME.CDSALDCOME = SOMACONTA.CDCONTCOME)           AND
                  (SALDCOME.CDCONTCTBL = SOMACONTA.CDCONTCTBL);
         END IF;
      END LOOP;
      LOOP
      FETCH C_LANCDEL INTO V_NRSEQ;
         EXIT WHEN C_LANCDEL%NOTFOUND;
         DELETE FROM LANCOMEEX
         WHERE (NRSEQLANCOM = V_NRSEQ);
      END LOOP;
      CLOSE C_LANCDEL;
      UPDATE LANCCONT SET IDSALDLANCO = 'S'
      WHERE (CDEMPRESA   = P_CDEMPRESA) AND
            (IDSALDLANCO = 'N') ;
   END;
END;
/