CREATE OR REPLACE PROCEDURE GERA_RAZAOGER_TEMP_2(
   P_CDEMPRESA  IN VARCHAR2,
   P_DATAINIC   IN CONTOPECTBL.DTFINALANC%TYPE,
   P_DATAFIM    IN CONTOPECTBL.DTFINALANC%TYPE,
   P_NRCTBTEMP  IN CTBTEMP.NRCTBTEMP%TYPE,
   P_CDREGIONAL IN VARCHAR2,
   P_CDFILIAL   IN VARCHAR2,
   P_CDCENTCUST IN VARCHAR2,
   P_CDCONTINI  IN SALDCCUS.CDCONTCTBL%TYPE,
   P_CDCONTFIM  IN SALDCCUS.CDCONTCTBL%TYPE)
IS
   V_DTSOMA DATE;
   V_NRSEQ INTEGER;
   V_CONT INTEGER;
   V_CDCONTCOME VARCHAR2(21);
   V_VRSALDO FLOAT;
   V_SOMALANC FLOAT;
   v_count number;
   V_COMANDO VARCHAR2(200);
BEGIN
   v_count := 0;
   V_NRSEQ  := 1;
   V_DTSOMA := LAST_DAY(P_DATAINIC)+1;
   V_DTSOMA := ADD_MONTHS(V_DTSOMA,-1);
   V_COMANDO := 'truncate table CTBTEMP';
  -- EXECUTE IMMEDIATE V_COMANDO;
   FOR FILIAL IN (SELECT C.CDFILIAL, C.CDCENTCUST
                    FROM CENTCUSTFIL C, FILIAL F
                   WHERE ((INSTR(P_CDCENTCUST, C.CDCENTCUST) <> 0) OR P_CDCENTCUST = 'T')
                     AND ((INSTR(P_CDFILIAL, F.CDFILIAL) <> 0) OR P_CDFILIAL = 'T')
                     AND ((INSTR(P_CDREGIONAL, F.CDAREAGERE) <> 0) OR P_CDREGIONAL = 'T')
                     AND F.CDFILIAL = C.CDFILIAL) LOOP
      FOR SALDANT IN (SELECT CDEMPRESA, MIN(DTSALDCCUS) AS DTSALDCCUS, CDFILIAL, CDCENTCUST, CDCONTCTBL, VRSALDCCUS
                        FROM
                        (
                        SELECT S.CDEMPRESA, S.DTSALDCCUS, S.CDFILIAL, CDCENTCUST, S.CDCONTCTBL, VRSALDCCUS
                                   FROM SALDCCUS S, CONTCTBL, (SELECT MAX(DTSALDCCUS) DATASALDO, CDEMPRESA, CDCONTCTBL
                                                                 FROM SALDCCUS
                                                                WHERE (DTSALDCCUS <= P_DATAINIC) AND
                                                                      ((INSTR(P_CDEMPRESA, SALDCCUS.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA)) AND
                                                                      (CDFILIAL   = FILIAL.CDFILIAL) AND
                                                                      (CDCENTCUST = FILIAL.CDCENTCUST) AND
                                                                      (CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIM||'ZZZZZZZZZZZZZZZZZZZ')
                                                                  GROUP BY CDEMPRESA, CDCONTCTBL) DATA
                                  WHERE (S.CDFILIAL = FILIAL.CDFILIAL) AND
                                        (S.CDCENTCUST = FILIAL.CDCENTCUST) AND
                                        (S.CDCONTCTBL = DATA.CDCONTCTBL) AND
                                        (S.DTSALDCCUS = DATA.DATASALDO) AND
                                        (S.CDEMPRESA = DATA.CDEMPRESA) AND
                                        (CONTCTBL.CDCONTCTBL = DATA.CDCONTCTBL) AND
                                        (CONTCTBL.IDTIPOCONT = 'A')
                                   UNION
                                   SELECT L.CDEMPRESA, L.DTCOMPCUSTO, L.CDFILIAL, L.CDCENTCUST, L.CDCONTCTBL, 0
                                     FROM LANCCCUSTO L
                                    WHERE  ((INSTR(P_CDEMPRESA, l.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA)) AND
                                          (L.DTCOMPCUSTO BETWEEN P_DATAINIC AND P_DATAFIM) AND
                                          (CDFILIAL   = FILIAL.CDFILIAL) AND
                                          (CDCENTCUST = FILIAL.CDCENTCUST) AND
                                          (CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIM||'ZZZZZZZZZZZZZZZZZZZ') AND
                                          NOT EXISTS (SELECT S.CDEMPRESA, S.DTSALDCCUS, S.CDFILIAL, CDCENTCUST, S.CDCONTCTBL, VRSALDCCUS
                                                           FROM SALDCCUS S, CONTCTBL, (SELECT MAX(DTSALDCCUS) DATASALDO, CDEMPRESA, CDCONTCTBL
                                                                                         FROM SALDCCUS
                                                                                        WHERE (DTSALDCCUS <= P_DATAINIC) AND
                                                                                               ((INSTR(P_CDEMPRESA, SALDCCUS.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA)) AND
                                                                                              (CDFILIAL   = FILIAL.CDFILIAL) AND
                                                                                              (CDCENTCUST = FILIAL.CDCENTCUST) AND
                                                                                              (CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIM||'ZZZZZZZZZZZZZZZZZZZ')
                                                                                        GROUP BY CDEMPRESA, CDCONTCTBL) DATA
                                                          WHERE (S.CDFILIAL = FILIAL.CDFILIAL) AND
                                                                (S.CDCENTCUST = FILIAL.CDCENTCUST) AND
                                                                (S.CDCONTCTBL = DATA.CDCONTCTBL) AND
                                                                (S.DTSALDCCUS = DATA.DATASALDO) AND
                                                                (S.CDEMPRESA = DATA.CDEMPRESA) AND
                                                                (CONTCTBL.CDCONTCTBL = DATA.CDCONTCTBL) AND
                                                                (CONTCTBL.IDTIPOCONT = 'A') AND
                                                                (L.CDFILIAL   = S.CDFILIAL) AND
                                                                (L.CDCENTCUST  = S.CDCENTCUST) AND
                                                                (L.CDCONTCTBL = S.CDCONTCTBL)))
                GROUP BY CDEMPRESA, CDFILIAL, CDCENTCUST, CDCONTCTBL, VRSALDCCUS)LOOP
           V_VRSALDO := SALDANT.VRSALDCCUS;
           BEGIN
             SELECT SUM(LANCCCUSTO.VRLANCCUST) AS VALOR
               INTO V_SOMALANC
               FROM LANCCCUSTO
              WHERE (LANCCCUSTO.CDCONTCTBL = SALDANT.CDCONTCTBL) AND
                    (LANCCCUSTO.CDEMPRESA  = SALDANT.CDEMPRESA) AND
                    (LANCCCUSTO.CDFILIAL = FILIAL.CDFILIAL) AND
                    (LANCCCUSTO.CDCENTCUST = FILIAL.CDCENTCUST) AND
                    (LANCCCUSTO.DTCOMPCUSTO BETWEEN V_DTSOMA AND P_DATAINIC - 1) ;
           EXCEPTION
                WHEN NO_DATA_FOUND THEN
                 V_SOMALANC := 0;
           END;
           V_VRSALDO := NVL(V_VRSALDO,0) + NVL(V_SOMALANC,0);
           INSERT INTO CTBTEMP
              (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
               CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
               CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP, CDEMPRESA, VRSALDO)
              VALUES
              (P_NRCTBTEMP, SALDANT.CDCONTCTBL, SALDANT.CDFILIAL, SALDANT.CDCENTCUST,
               '', SALDANT.DTSALDCCUS, '', V_VRSALDO,
               '', '', V_NRSEQ, '0', SALDANT.CDEMPRESA, V_VRSALDO);

           V_NRSEQ := V_NRSEQ+1;

         FOR LANC IN (
            SELECT LC.CDEMPRESA, LC.CDFILIAL,   LC.CDCENTCUST,
                   LC.VRLANCCUST, LC.DTCOMPCUSTO, L.CDCONTCOME,
                   L.DTLANCCONT, L.CDCONTCTBL, L.CDDOCUCONT, L.DSHISTLANC,
                   L.NRLANCCONT||'' AS NRLANCCONT
              FROM LANCCCUSTO LC, LANCCONT L
             WHERE (LC.CDEMPRESA   = SALDANT.CDEMPRESA)
               AND (LC.DTCOMPCUSTO BETWEEN P_DATAINIC AND P_DATAFIM)
               AND (LC.CDFILIAL    = SALDANT.CDFILIAL)
               AND (LC.CDCENTCUST  = SALDANT.CDCENTCUST)
               AND (LC.CDCONTCTBL  = SALDANT.CDCONTCTBL)
               AND (L.CDEMPRESA   = LC.CDEMPRESA)
               AND (LC.NRLANCCONT  = L.NRLANCCONT)
               AND (LC.NRSEQLANC   = L.NRSEQLANC)
               AND (LC.CDCONTCTBL  = L.CDCONTCTBL)
             ORDER BY LC.DTCOMPCUSTO) LOOP


            V_CDCONTCOME := LANC.CDCONTCOME;
            V_VRSALDO := V_VRSALDO + LANC.VRLANCCUST;
            INSERT INTO CTBTEMP
                (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,  CDCENTCUSTT,
                 CDCONTCOMET, DTLANCCONTT, NRLANCCONTT,VRLANCAMENTT,
                 CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,   IDCTBTEMP, CDEMPRESA, VRSALDO)

            VALUES(P_NRCTBTEMP, LANC.CDCONTCTBL, LANC.CDFILIAL, LANC.CDCENTCUST,
                   V_CDCONTCOME, LANC.DTCOMPCUSTO, LANC.NRLANCCONT, LANC.VRLANCCUST,
                   LANC.CDDOCUCONT, LANC.DSHISTLANC, V_NRSEQ, '1', LANC.CDEMPRESA, V_VRSALDO);

            V_NRSEQ := V_NRSEQ + 1;
            v_count := v_count + 1;
            if v_count = 5000 then
               v_count := 0;
               Commit;

            end if;
         END LOOP;
      END LOOP;
      V_VRSALDO := 0;
      COMMIT;
   END LOOP;
END;
/