CREATE OR REPLACE PROCEDURE GERA_RAZAOGER_TEMP(
   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);
BEGIN
   V_NRSEQ  := 1;
   V_DTSOMA := LAST_DAY(P_DATAINIC)+1;
   V_DTSOMA := ADD_MONTHS(V_DTSOMA,-1);
   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 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)
                                        GROUP BY CDEMPRESA, CDCONTCTBL) DATA
          WHERE (S.VRSALDCCUS <> 0) AND
                (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'))LOOP

           INSERT INTO CTBTEMP
              (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
               CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
               CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP, CDEMPRESA)
              VALUES
              (P_NRCTBTEMP, SALDANT.CDCONTCTBL, SALDANT.CDFILIAL, SALDANT.CDCENTCUST,
               '', SALDANT.DTSALDCCUS, '', SALDANT.VRSALDCCUS,
               '', '', V_NRSEQ, '0', SALDANT.CDEMPRESA);

           V_NRSEQ := V_NRSEQ+1;
      END LOOP;

      IF P_DATAINIC <> V_DTSOMA THEN
         FOR SOMALANC IN (
             SELECT LANCCCUSTO.CDEMPRESA, LANCCCUSTO.CDCONTCTBL,   LANCCCUSTO.CDFILIAL,
                    LANCCCUSTO.CDCENTCUST, SUM(LANCCCUSTO.VRLANCCUST) AS VALOR
               FROM LANCCCUSTO
              WHERE (LANCCCUSTO.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIM) AND
                    ((INSTR(P_CDEMPRESA, LANCCCUSTO.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA)) AND
                    (LANCCCUSTO.CDFILIAL = FILIAL.CDFILIAL) AND
                    (LANCCCUSTO.CDCENTCUST = FILIAL.CDCENTCUST) AND
                    (LANCCCUSTO.DTCOMPCUSTO BETWEEN V_DTSOMA AND P_DATAINIC - 1)
              GROUP BY LANCCCUSTO.CDEMPRESA, LANCCCUSTO.CDCONTCTBL, LANCCCUSTO.CDFILIAL,
                      LANCCCUSTO.CDCENTCUST) LOOP

             BEGIN
                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) AND
                       (CTBTEMP.CDEMPRESA = SOMALANC.CDEMPRESA);
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_CONT := 0;
             END;
             IF V_CONT = 0 THEN
                INSERT INTO CTBTEMP
                   (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,   CDCENTCUSTT,
                    CDCONTCOMET, DTLANCCONTT, NRLANCCONTT, VRLANCAMENTT,
                    CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,    IDCTBTEMP, CDEMPRESA)
                VALUES
                   (P_NRCTBTEMP, SOMALANC.CDCONTCTBL, SOMALANC.CDFILIAL, SOMALANC.CDCENTCUST,
                    '', (P_DATAINIC - 1), '', SOMALANC.VALOR,
                    '', '', V_NRSEQ, '0', SOMALANC.CDEMPRESA);

                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) AND
                      (CTBTEMP.CDEMPRESA = SOMALANC.CDEMPRESA);
             END IF;

         END LOOP;

      END IF;

      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
           FROM LANCCCUSTO LC, LANCCONT L
          WHERE ((INSTR(P_CDEMPRESA, LC.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))
            AND (LC.DTCOMPCUSTO BETWEEN P_DATAINIC AND P_DATAFIM)
            AND (LC.CDFILIAL    = FILIAL.CDFILIAL)
            AND (LC.CDCENTCUST  = FILIAL.CDCENTCUST)
            AND (L.CDEMPRESA   = LC.CDEMPRESA)
            AND (LC.NRLANCCONT  = L.NRLANCCONT)
            AND (LC.NRSEQLANC   = L.NRSEQLANC)
            AND (LC.CDCONTCTBL  = L.CDCONTCTBL)
            AND (LC.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIM)) LOOP


         V_CDCONTCOME := LANC.CDCONTCOME;

         INSERT INTO CTBTEMP
             (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,  CDCENTCUSTT,
              CDCONTCOMET, DTLANCCONTT, NRLANCCONTT,VRLANCAMENTT,
              CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,   IDCTBTEMP, CDEMPRESA)

         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_NRSEQ := V_NRSEQ + 1;
      END LOOP;
      COMMIT;
   END LOOP;
END;
/

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

