create or replace PROCEDURE GERA_RAZAOGER_TEMP_INSCESTAD(
   P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE,
   P_DATAINIC   IN CONTOPECTBL.DTFINALANC%TYPE,
   P_DATAFIM    IN CONTOPECTBL.DTFINALANC%TYPE,
   P_NRCTBTEMP  IN CTBTEMP.NRCTBTEMP%TYPE,
   P_CDINSCESTA IN VARCHAR2,
   P_CDCENTCUST IN VARCHAR2,
   P_CDCONTINI  IN SALDCCUS.CDCONTCTBL%TYPE,
   P_CDCONTFIM  IN SALDCCUS.CDCONTCTBL%TYPE,
   P_CDFILIAL   IN VARCHAR2)
IS
   V_DTSOMA DATE;
   V_NRSEQ INTEGER;
   V_CONT INTEGER;
   V_DARCOMMIT NUMBER;
   V_COUNTCOMMIT NUMBER;
BEGIN
   V_DARCOMMIT := 1000;
   V_COUNTCOMMIT := 0;
   V_NRSEQ  := 1;
   V_DTSOMA := LAST_DAY(P_DATAINIC)+1;
   V_DTSOMA := ADD_MONTHS(V_DTSOMA,-1);
   FOR C_FILIAL IN (
      SELECT F.CDFILIAL, C.CDCENTCUST
        FROM FILIAL F, CENTCUSTFIL CF, CENTCUST C
       WHERE ((INSTR(P_CDINSCESTA, F.CDINSCESTA) <> 0) OR (P_CDINSCESTA = 'T'))
         AND ((INSTR(P_CDFILIAL,   F.CDFILIAL) <> 0) OR (P_CDFILIAL = 'T'))
         AND ((INSTR(P_CDCENTCUST, C.CDCENTCUST) <> 0) OR P_CDCENTCUST = 'T')
         AND F.CDFILIAL = CF.CDFILIAL
         AND CF.CDCENTCUST = C.CDCENTCUST
         AND F.CDEMPRESA = P_CDEMPRESA) LOOP
      FOR SALDANT IN (
         SELECT S.DTSALDCCUS, S.CDFILIAL, S.CDCENTCUST, S.CDCONTCTBL, S.VRSALDCCUS
           FROM SALDCCUS S, CONTCTBL CO
          WHERE (S.CDEMPRESA   = P_CDEMPRESA)
            AND (S.VRSALDCCUS  <> 0)
            AND (S.CDFILIAL    = C_FILIAL.CDFILIAL)
            AND (S.CDCENTCUST = C_FILIAL.CDCENTCUST)
            AND (S.CDCONTCTBL  = CO.CDCONTCTBL)
            AND (CO.CDCONTCTBL  BETWEEN P_CDCONTINI AND P_CDCONTFIM)
            AND (CO.IDTIPOCONT = 'A')
            AND EXISTS (SELECT 1
                         FROM SALDCCUS SA
                        WHERE (SA.DTSALDCCUS < P_DATAINIC)
                          AND (SA.CDEMPRESA  = P_CDEMPRESA)
                          AND (SA.CDFILIAL   = S.CDFILIAL)
                          AND (SA.CDCENTCUST = S.CDCENTCUST)
                          AND (SA.CDCONTCTBL = S.CDCONTCTBL)
                       HAVING S.DTSALDCCUS = MAX(SA.DTSALDCCUS)))LOOP
        V_COUNTCOMMIT := V_COUNTCOMMIT + 1;
        INSERT INTO CTBTEMP
           (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
            CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
            CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
        VALUES
           (P_NRCTBTEMP, SALDANT.CDCONTCTBL, SALDANT.CDFILIAL, SALDANT.CDCENTCUST,
            '', SALDANT.DTSALDCCUS, '', SALDANT.VRSALDCCUS,
            '', '', V_NRSEQ, '0');
        IF V_COUNTCOMMIT = V_DARCOMMIT THEN
           V_COUNTCOMMIT := 0;
           COMMIT;
        END IF;
        V_NRSEQ := V_NRSEQ+1;
      END LOOP;
      IF P_DATAINIC <> V_DTSOMA THEN
         FOR SOMALANC IN (
             SELECT LANCCCUSTO.CDCONTCTBL,   LANCCCUSTO.CDFILIAL,
                    LANCCCUSTO.CDCENTCUST, SUM(LANCCCUSTO.VRLANCCUST) AS VALOR
               FROM  LANCCCUSTO, LANCCONT
              WHERE (LANCCCUSTO.CDEMPRESA  = P_CDEMPRESA)
                AND (C_FILIAL.CDFILIAL     = LANCCCUSTO.CDFILIAL)
                AND (LANCCCUSTO.DTCOMPCUSTO BETWEEN V_DTSOMA AND P_DATAINIC - 1)
                AND (LANCCCUSTO.CDCONTCTBL  BETWEEN P_CDCONTINI AND P_CDCONTFIM)
                AND C_FILIAL.CDCENTCUST = LANCCCUSTO.CDCENTCUST
                AND (LANCCCUSTO.CDEMPRESA = LANCCONT.CDEMPRESA)
                AND (LANCCCUSTO.NRLANCCONT = LANCCONT.NRLANCCONT)
                AND (LANCCCUSTO.NRSEQLANC  = LANCCONT.NRSEQLANC)
              GROUP BY LANCCCUSTO.CDCONTCTBL,   LANCCCUSTO.CDFILIAL,
                    LANCCCUSTO.CDCENTCUST) LOOP
             SELECT COUNT(*)
               INTO V_CONT
               FROM CTBTEMP
              WHERE (CTBTEMP.NRCTBTEMP = P_NRCTBTEMP)
                AND (CTBTEMP.CDCENTCUSTT = SOMALANC.CDCENTCUST)
                AND (CTBTEMP.CDFILIALT   = SOMALANC.CDFILIAL)
                AND (CTBTEMP.CDCONTCTBLT = SOMALANC.CDCONTCTBL);
             V_COUNTCOMMIT := V_COUNTCOMMIT + 1;
             IF V_CONT = 0 THEN
                INSERT INTO CTBTEMP
                   (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
                    CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
                    CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP)
                VALUES
                   (P_NRCTBTEMP, SOMALANC.CDCONTCTBL, SOMALANC.CDFILIAL, SOMALANC.CDCENTCUST,
                    '', (P_DATAINIC - 1), '', SOMALANC.VALOR,
                    '', '', V_NRSEQ, '0');
                V_NRSEQ := V_NRSEQ + 1;
             ELSE
                UPDATE CTBTEMP
                   SET VRLANCAMENTT = VRLANCAMENTT + SOMALANC.VALOR
                 WHERE (CTBTEMP.NRCTBTEMP = P_NRCTBTEMP)
                   AND (CTBTEMP.CDFILIALT   = SOMALANC.CDFILIAL)
                   AND (CTBTEMP.CDCENTCUSTT = SOMALANC.CDCENTCUST)
                   AND (CTBTEMP.CDCONTCTBLT = SOMALANC.CDCONTCTBL);
             END IF;
             IF V_COUNTCOMMIT = V_DARCOMMIT THEN
                V_COUNTCOMMIT := 0;
                COMMIT;
             END IF;
         END LOOP;
      END IF;
      FOR LANC IN (
         SELECT LANCCCUSTO.CDFILIAL,   LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL,
                LANCCCUSTO.VRLANCCUST, LANCCONT.CDDOCUCONT,   LANCCONT.DSHISTLANC,
                LANCCONT.NRLANCCONT,   LANCCCUSTO.DTCOMPCUSTO, LANCCONT.CDCONTCOME
           FROM LANCCONT, LANCCCUSTO
          WHERE (LANCCONT.CDEMPRESA     = P_CDEMPRESA)
            AND (LANCCONT.DTLANCCONT    BETWEEN P_DATAINIC AND P_DATAFIM)
            AND (LANCCCUSTO.NRLANCCONT  = LANCCONT.NRLANCCONT)
            AND (LANCCCUSTO.NRSEQLANC   = LANCCONT.NRSEQLANC)
            AND (LANCCCUSTO.CDEMPRESA   = P_CDEMPRESA)
            AND (LANCCCUSTO.CDCONTCTBL  BETWEEN P_CDCONTINI AND P_CDCONTFIM)
            AND (LANCCCUSTO.DTCOMPCUSTO BETWEEN P_DATAINIC AND P_DATAFIM)
            AND (C_FILIAL.CDFILIAL      = LANCCCUSTO.CDFILIAL)
            AND (C_FILIAL.CDCENTCUST = LANCCCUSTO.CDCENTCUST)) LOOP
         INSERT INTO CTBTEMP
            (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,  CDCENTCUSTT,
             CDCONTCOMET, DTLANCCONTT, NRLANCCONTT,VRLANCAMENTT,
             CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,   IDCTBTEMP)
         VALUES
            (P_NRCTBTEMP, LANC.CDCONTCTBL, LANC.CDFILIAL, LANC.CDCENTCUST,
             LANC.CDCONTCOME, LANC.DTCOMPCUSTO,LANC.NRLANCCONT, LANC.VRLANCCUST,
             LANC.CDDOCUCONT, LANC.DSHISTLANC, V_NRSEQ, '1');
          V_COUNTCOMMIT := V_COUNTCOMMIT + 1;
          IF V_COUNTCOMMIT = V_DARCOMMIT THEN
             V_COUNTCOMMIT := 0;
             COMMIT;
          END IF;
          V_NRSEQ := V_NRSEQ+1;
      END LOOP;
   END LOOP;
COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GERA_RAZAOGER_TEMP_INSCESTAD','5.85.xxx'); END;
/
