CREATE OR REPLACE PROCEDURE ROTINAS_FIN_CTB (
    P_DTFINAL IN DATE DEFAULT SYSDATE
 ) AS

 V_NRMOVBANC  VARCHAR2(10);
 V_CDREDUCONT VARCHAR2(10);
 V_CDFILIAL   VARCHAR2(10);
 V_NRBAIXA    VARCHAR2(10);
 V_COUNT      NUMBER;
 V_CDCONTINI  VARCHAR2(22);
 V_CDCONTFIN  VARCHAR2(22);
 V_DTINICIAL  DATE;
 V_DTFINAL    DATE;
BEGIN
  UPDATE CONTOPECTBL
     SET DTULTEXECJOB = SYSDATE;
     
  /*EXECUCAO DE ROTINAS FINANCEIRAS*/
  EVOLUCAO_FLUXO_CAIXA('T',P_DTFINAL-30);
  FOR C_CONTCORR IN (SELECT CDREDUCONT
                       FROM CONTCORR)LOOP
      ATUALIZA_SALDO_BANCARIO(C_CONTCORR.CDREDUCONT,TRUNC(SYSDATE)-30,0,'0');
  END LOOP;
  FOR C_BX IN (SELECT CDFILIAL, NRBAIXAREC  AS NRBAIXA, CDREDUCONT, NRMOVBANC, 'R' AS ID , ROWID AS CHAVE,
                      CDEMPRESA, NRLCTBBREC AS LANCCONTAB
                 FROM BAIXARECEBIDA
                WHERE DTBAIXAREC BETWEEN  P_DTFINAL-30 AND P_DTFINAL
                UNION
               SELECT CDFILIAL, NRBAIXAPAG  AS NRBAIXA, CDREDUCONT, NRMOVBANC, 'P' AS ID , ROWID AS CHAVE,
                      CDEMPRESA, NRLCTBBPAG
                 FROM BAIXAPAGA
                WHERE DTBAIXAPAG BETWEEN  P_DTFINAL-30 AND P_DTFINAL)LOOP
    BEGIN
       SELECT COUNT(*)
         INTO V_COUNT
         FROM BOLETRECEBER
        WHERE CDFILIBAIREC = C_BX.CDFILIAL
          AND NRBAIXAREC   = C_BX.NRBAIXA
          AND 'R'          = C_BX.ID;
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
        V_COUNT := 0;
    END;
    IF (V_COUNT = 0) AND (C_BX.ID = 'R') THEN
        DELETE FROM BAIXARECEBIDA
         WHERE ROWID = C_BX.CHAVE
           AND 'R'   =  C_BX.ID;
        DELETE
          FROM LANCCONT
         WHERE CDEMPRESA  = C_BX.CDEMPRESA
           AND NRLANCCONT = C_BX.LANCCONTAB;

    END IF;
    BEGIN
       SELECT COUNT(*)
         INTO V_COUNT
         FROM BOLETPAGAR
        WHERE CDFILIBAIPAG   = C_BX.CDFILIAL
          AND NRBAIXAPAG     = C_BX.NRBAIXA
          AND 'P'            = C_BX.ID;
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
        V_COUNT := 0;
    END;
    IF (V_COUNT = 0) AND (C_BX.ID = 'P') THEN
        DELETE FROM BAIXAPAGA
         WHERE ROWID = C_BX.CHAVE
           AND 'P'   =  C_BX.ID;
        DELETE
          FROM LANCCONT
         WHERE CDEMPRESA  = C_BX.CDEMPRESA
           AND NRLANCCONT = C_BX.LANCCONTAB;

    END IF;

  END LOOP;
  DELETE
    FROM EXTRBANCO E
   WHERE NOT EXISTS (SELECT 1
                       FROM MOVREAL M
                      WHERE E.CDREDUCONT = M.CDREDUCONT
                        AND E.CDSEQUEXTR = M.CDSEQUEXTR);
  COMMIT;
  FOR C_MOV_B IN (SELECT M.ROWID  AS CHAVE
                    FROM MOVBANCARIO M
                   WHERE NOT EXISTS(SELECT 1
                                      FROM BAIXARECEBIDA BR
                                     WHERE M.CDREDUCONT = BR.CDREDUCONT
                                       AND M.NRMOVBANC = BR.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM BAIXAPAGA BP
                                     WHERE M.CDREDUCONT = BP.CDREDUCONT
                                       AND M.NRMOVBANC = BP.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM MOVCREDBANC MC
                                     WHERE M.CDREDUCONT = MC.CDREDUCONT
                                       AND M.NRMOVBANC = MC.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM MOVDEBBANC MD
                                     WHERE M.CDREDUCONT = MD.CDREDUCONT
                                       AND M.NRMOVBANC = MD.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM LANCSALDCAIXA LS
                                     WHERE M.CDREDUCONT = LS.CDREDUCONT
                                       AND M.NRMOVBANC = LS.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM ITTRANSFBANC IT
                                     WHERE M.CDREDUCONT = IT.CDREDUCONT
                                       AND M.NRMOVBANC = IT.NRMOVBANC)
                     AND NOT EXISTS(SELECT 1
                                      FROM MOVBANCARIO MO
                                     WHERE M.CDREDUCONT = MO.CDREDUCONT
                                       AND M.NRMOVBANC = MO.NRMOVBANCORIG))LOOP

     DELETE
       FROM MOVBANCARIO
      WHERE ROWID = C_MOV_B.CHAVE;
      COMMIT;
  END LOOP;
  FOR C_MOVREAL IN (SELECT ROWID AS CHAVE
                      FROM MOVREAL MR
                     WHERE NOT EXISTS (SELECT 1
                                         FROM MOVBANCARIO M
                                        WHERE MR.CDREDUCONT = M.CDREDUCONT
                                          AND MR.NRMOVBARE  =  M.NRMOVBANC)
                       AND NOT EXISTS (SELECT 1
                                         FROM MOVBANCARIO M
                                        WHERE MR.NRCONCIMOVB = M.NRCONCIMOVB
                                          AND MR.CDREDUCONT = M.CDREDUCONT)
                       AND NOT EXISTS (SELECT 1
                                         FROM MOVBANCARIO M
                                        WHERE MR.CDSEQUEXTR = M.CDSEQUEXBA
                                          AND MR.NRSEQMOVREAL = M.NRSEQMOVREBA
                                          AND MR.CDREDUCONT = M.CDREDUCONT)                    
                       AND IDSTCONMVBAN <> '0') LOOP
    UPDATE MOVREAL
       SET IDSTCONMVBAN = '0',
           NRCONCIMOVB  = NULL,
           IDCONCREAL   = NULL,
           NRMOVBARE    = NULL
     WHERE ROWID        = C_MOVREAL.CHAVE;
     COMMIT;
  END LOOP;

  FOR C_MOVBANC IN (SELECT ROWID AS CHAVE
                      FROM MOVBANCARIO M
                     WHERE NOT EXISTS (SELECT 1
                                         FROM MOVREAL MR
                                        WHERE MR.CDREDUCONT = M.CDREDUCONT
                                          AND MR.NRMOVBARE  =  M.NRMOVBANC)
                       AND NOT EXISTS (SELECT 1
                                         FROM MOVREAL MR
                                        WHERE MR.NRCONCIMOVB = M.NRCONCIMOVB
                                          AND MR.CDREDUCONT = M.CDREDUCONT)
                       AND NOT EXISTS (SELECT 1
                                         FROM MOVREAL MR
                                        WHERE MR.CDSEQUEXTR = M.CDSEQUEXBA
                                          AND MR.NRSEQMOVREAL = M.NRSEQMOVREBA
                                          AND MR.CDREDUCONT = M.CDREDUCONT)
                       AND IDSTCONEXT <> '0') LOOP

    UPDATE MOVBANCARIO
       SET IDSTCONEXT   = '0',
           CDSEQUEXBA   = NULL,
           NRSEQMOVREBA = NULL,
           NRCONCIMOVB  = NULL,
           IDCONCBANC   = NULL
     WHERE ROWID        = C_MOVBANC.CHAVE;
     COMMIT;
  END LOOP;

  /*EXECUCAO DE ROTINAS CONTABEIS*/
  SELECT MIN(CDCONTCTBL), MAX(CDCONTCTBL)
    INTO V_CDCONTINI, V_CDCONTFIN
    FROM CONTCTBL;
    V_DTINICIAL := P_DTFINAL;
    V_DTFINAL   := P_DTFINAL;
  FOR C_EMPR IN (SELECT DTINICCONT, DTFINACONT, IDCONTCOME, E.CDEMPRESA ,
                        IDUTSALDOSOC, IDCONTGERE, IDRECALCSALDAUTO, IDFECHAPERIODOAUTO
                   FROM EMPRESA  E, CONTOPECTBL C
                  WHERE E.CDEMPRESA = C.CDEMPRESA)LOOP
     IF C_EMPR.IDFECHAPERIODOAUTO = 'S' THEN
        IF C_EMPR.DTINICCONT > ADD_MONTHS(V_DTINICIAL,-3) THEN
           UPDATE CONTOPECTBL
              SET DTINICLANC      = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINICRELA      = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINICCONT      = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINICCTBL      = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINIINTCONTDFE = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINIINTCONTDFS = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINIINTCONTGPA = ADD_MONTHS(V_DTINICIAL,-3),
                  DTINIINTCONTFIS = ADD_MONTHS(V_DTINICIAL,-3)
           WHERE CDEMPRESA = C_EMPR.CDEMPRESA;
        END IF;
     END IF;
	 IF C_EMPR.IDRECALCSALDAUTO = 'S' THEN
        BEGIN
           SELECT DTINICCONT, DTFINACONT
             INTO V_DTINICIAL, V_DTFINAL
             FROM CONTOPECTBL
            WHERE CDEMPRESA = C_EMPR.CDEMPRESA;
        EXCEPTION
             WHEN NO_DATA_FOUND THEN
            V_DTINICIAL := NULL;
            V_DTFINAL   := NULL;
        END;
     END IF;
	 
     IF C_EMPR.IDCONTCOME = 'S' THEN
       SALDO_COMERCIAL_GERAL(C_EMPR.CDEMPRESA, C_EMPR.DTINICCONT);
     END IF;
     IF C_EMPR.IDCONTGERE = 'S' THEN
       SALDO_GERENCIAL_GERAL(C_EMPR.CDEMPRESA, P_DTFINAL ,  P_DTFINAL);
     END IF;
     ATUALIZA_SALDO_GERAL(C_EMPR.CDEMPRESA, V_CDCONTINI, V_CDCONTFIN||'zzzZZZZ', V_DTINICIAL, V_DTFINAL,C_EMPR.IDUTSALDOSOC);

  END LOOP;


END;
/
