CREATE OR REPLACE PROCEDURE SALDO_GERENCIAL_GERAL(
  P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
  P_DATAINIC  IN CONTOPECTBL.DTFINALANC%TYPE)
IS
  V_DTFINACONT  DATE;
  DATA_SALDO    DATE;
  DATA_MAX      DATE;
  DATA_INIC     DATE;
  V_CONTSALDO   CHAR;
  V_IDSALDEMPR  CHAR;
  V_COUNT       NUMBER;
  V_VERSAO      VARCHAR2(1);
  V_DARCOMMIT   NUMBER;
  V_CONTCOMMIT NUMBER;
BEGIN
   V_DARCOMMIT := 250;
   V_CONTCOMMIT := 1;

   SELECT DTFINACONT
     INTO V_DTFINACONT
     FROM CONTOPECTBL
    WHERE CDEMPRESA = P_CDEMPRESA;

   FOR DEL_SALD IN
      (SELECT CDEMPRESA, DTSALDCCUS, CDCONTCTBL, CDFILIAL, CDCENTCUST
         FROM SALDCCUS
        WHERE CDEMPRESA   = P_CDEMPRESA
          AND DTSALDCCUS >= P_DATAINIC) LOOP
      DELETE
        FROM SALDCCUS
       WHERE CDEMPRESA = DEL_SALD.CDEMPRESA
         AND DTSALDCCUS = DEL_SALD.DTSALDCCUS
         AND CDCONTCTBL = DEL_SALD.CDCONTCTBL
         AND CDFILIAL = DEL_SALD.CDFILIAL
         AND CDCENTCUST = DEL_SALD.CDCENTCUST;
      V_CONTCOMMIT := V_CONTCOMMIT +1 ;
       IF V_CONTCOMMIT = V_DARCOMMIT THEN
          V_CONTCOMMIT := 1;
          COMMIT;
       END IF;
   END LOOP;

   FOR MES IN
       (SELECT DISTINCT DTMES PRIMDIA, LAST_DAY(DTDATA) ULTIMODIA
          FROM DATA
         WHERE DTDATA BETWEEN P_DATAINIC AND V_DTFINACONT
         ORDER BY 1) LOOP

      FOR SALDANT IN
         (SELECT SALDCCUS.CDFILIAL,   SALDCCUS.CDCENTCUST, SALDCCUS.CDCONTCTBL,
                 SALDCCUS.DTSALDCCUS, SALDCCUS.VRSALDCCUS
            FROM SALDCCUS
           WHERE (SALDCCUS.CDEMPRESA  = P_CDEMPRESA)
             AND (SALDCCUS.DTSALDCCUS IN (SELECT MAX(S.DTSALDCCUS)
                                            FROM SALDCCUS S
                                           WHERE (S.CDEMPRESA  = P_CDEMPRESA)
                                             AND (S.CDFILIAL   = SALDCCUS.CDFILIAL)
                                             AND (S.CDCENTCUST = SALDCCUS.CDCENTCUST)
                                             AND (S.CDCONTCTBL = SALDCCUS.CDCONTCTBL)
                                             AND (S.DTSALDCCUS < MES.PRIMDIA)))) LOOP
         DATA_SALDO := LAST_DAY(SALDANT.DTSALDCCUS);
         WHILE (DATA_SALDO <= MES.ULTIMODIA) LOOP
            INSERT INTO SALDCCUS
               (CDEMPRESA, CDFILIAL, CDCENTCUST, CDCONTCTBL, DTSALDCCUS, VRSALDCCUS)
               (SELECT P_CDEMPRESA, SALDANT.CDFILIAL, SALDANT.CDCENTCUST, CDCONTCTBL, DATA_SALDO, 0
                  FROM CONTCTBL
                 WHERE NOT EXISTS (SELECT 1
                                     FROM SALDCCUS
                                    WHERE CDEMPRESA = P_CDEMPRESA
                                      AND DTSALDCCUS = DATA_SALDO
                                      AND CDFILIAL   = SALDANT.CDFILIAL
                                      AND CDCENTCUST = SALDANT.CDCENTCUST
                                      AND CDCONTCTBL = CONTCTBL.CDCONTCTBL)
                   AND (SALDANT.CDCONTCTBL LIKE (RTRIM(CONTCTBL.CDCONTCTBL))||'%'));
             V_CONTCOMMIT := V_CONTCOMMIT +1 ;
             IF V_CONTCOMMIT = V_DARCOMMIT THEN
                V_CONTCOMMIT := 1;
                COMMIT;
             END IF;

            DATA_SALDO := ADD_MONTHS(DATA_SALDO,1);
         END LOOP;

         UPDATE SALDCCUS
            SET VRSALDCCUS = SALDANT.VRSALDCCUS
          WHERE (SALDCCUS.DTSALDCCUS >= MES.PRIMDIA)
            AND (SALDCCUS.CDEMPRESA  =  P_CDEMPRESA)
            AND (SALDCCUS.CDFILIAL   =  SALDANT.CDFILIAL)
            AND (SALDCCUS.CDCENTCUST =  SALDANT.CDCENTCUST)
            AND (SALDCCUS.CDCONTCTBL =  SALDANT.CDCONTCTBL);
         V_CONTCOMMIT := V_CONTCOMMIT +1 ;
         IF V_CONTCOMMIT = V_DARCOMMIT THEN
            V_CONTCOMMIT := 1;
            COMMIT;
         END IF;
      END LOOP;

      /* SOMA OS LANCAMENTOS DO MES, SOMA COM O ANTEIOR E GRAVA NO MES COMO SALDO */
      FOR ATUALIZA IN
         (SELECT LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL,
                 MIN(DTCOMPCUSTO) AS MENOR_DATA
            FROM LANCCCUSTO
           WHERE (LANCCCUSTO.CDEMPRESA   =  P_CDEMPRESA)
             AND (LANCCCUSTO.DTCOMPCUSTO BETWEEN MES.PRIMDIA AND MES.ULTIMODIA)
           GROUP BY LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL) LOOP

         DATA_SALDO := LAST_DAY(ATUALIZA.MENOR_DATA);
         V_CONTSALDO := 0;
         SELECT COUNT(*)
           INTO V_CONTSALDO
           FROM SALDCCUS
          WHERE (CDEMPRESA  = P_CDEMPRESA)
            AND (CDCONTCTBL = ATUALIZA.CDCONTCTBL)
            AND (CDFILIAL   = ATUALIZA.CDFILIAL)
            AND (CDCENTCUST = ATUALIZA.CDCENTCUST)
            AND (DTSALDCCUS = DATA_SALDO);

         IF (V_CONTSALDO = 0) THEN
            WHILE (DATA_SALDO <= MES.ULTIMODIA) LOOP
               INSERT INTO SALDCCUS
                  (CDEMPRESA, CDFILIAL, CDCENTCUST, CDCONTCTBL, DTSALDCCUS, VRSALDCCUS)
                  (SELECT P_CDEMPRESA, ATUALIZA.CDFILIAL, ATUALIZA.CDCENTCUST, CDCONTCTBL, DATA_SALDO, 0
                     FROM CONTCTBL
                    WHERE NOT EXISTS (SELECT DISTINCT 1
                                        FROM SALDCCUS
                                       WHERE CDEMPRESA  = P_CDEMPRESA
                                         AND CDFILIAL = ATUALIZA.CDFILIAL
                                         AND CDCENTCUST = ATUALIZA.CDCENTCUST
                                         AND DTSALDCCUS = DATA_SALDO
                                         AND CDCONTCTBL = CONTCTBL.CDCONTCTBL)
                      AND ATUALIZA.CDCONTCTBL LIKE (RTRIM(CONTCTBL.CDCONTCTBL))||'%');
               V_CONTCOMMIT := V_CONTCOMMIT +1;
               IF V_CONTCOMMIT = V_DARCOMMIT THEN
                  V_CONTCOMMIT := 1;
                  COMMIT;
               END IF;
               DATA_SALDO := ADD_MONTHS(DATA_SALDO,1);
            END LOOP;
         END IF;

         DATA_SALDO := LAST_DAY(ATUALIZA.MENOR_DATA);
         WHILE (DATA_SALDO <= MES.ULTIMODIA) LOOP
            FOR SOMACONTA IN
               (SELECT LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL,
                       SUM(LANCCCUSTO.VRLANCCUST) AS VALORSALDO
                  FROM LANCCCUSTO
                 WHERE (LANCCCUSTO.DTCOMPCUSTO BETWEEN ATUALIZA.MENOR_DATA AND DATA_SALDO)
                   AND (LANCCCUSTO.CDFILIAL    = ATUALIZA.CDFILIAL)
                   AND (LANCCCUSTO.CDCENTCUST  = ATUALIZA.CDCENTCUST)
                   AND (LANCCCUSTO.CDCONTCTBL  = ATUALIZA.CDCONTCTBL)
                   AND (LANCCCUSTO.CDEMPRESA   = P_CDEMPRESA)
                 GROUP BY LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL) LOOP

               UPDATE SALDCCUS
                  SET VRSALDCCUS = SALDCCUS.VRSALDCCUS + SOMACONTA.VALORSALDO
                WHERE (SALDCCUS.DTSALDCCUS = DATA_SALDO)
                  AND (SALDCCUS.CDEMPRESA  = P_CDEMPRESA)
                  AND (SALDCCUS.CDFILIAL   = SOMACONTA.CDFILIAL)
                  AND (SALDCCUS.CDCENTCUST = SOMACONTA.CDCENTCUST)
                  AND (SOMACONTA.CDCONTCTBL LIKE (RTRIM(SALDCCUS.CDCONTCTBL)||'%'));
               V_CONTCOMMIT := V_CONTCOMMIT +1 ;
               IF V_CONTCOMMIT = V_DARCOMMIT THEN
                  V_CONTCOMMIT := 1;
                  COMMIT;
               END IF;
            END LOOP;
            DATA_SALDO := ADD_MONTHS(DATA_SALDO,1);
         END LOOP;
      END LOOP;
      COMMIT;
   END LOOP;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SALDO_GERENCIAL_GERAL','5.30.000'); END;
/
