CREATE OR REPLACE PROCEDURE VERIFICA_ERRO_CTB(
   P_NRLANCCTBTEMP IN LANCCONT.NRLANCCONT%TYPE,
   P_CDEMPRESA     IN EMPRESA.CDEMPRESA%TYPE)

IS
   V_COUNT FLOAT;
   V_RAT VARCHAR2(01);
   V_CONTRAT  VARCHAR2(01);
   V_CONTCOME VARCHAR2(01);
   V_IDSISTEMA VARCHAR2(03);
BEGIN                                                                 

   V_COUNT := 0;

   BEGIN
      SELECT IDCONTGERE, IDCONTCOME
         INTO V_CONTRAT, V_CONTCOME
         FROM CONTOPECTBL
        WHERE CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CONTRAT := NULL;
         V_CONTCOME := NULL;
   END;

   /*VERIFICACAO DO TOTAL DE DEBITO E CREDITO NO LANCAMENTO CONTABIL*/
   BEGIN
      SELECT NVL(SUM(VRLANCCTBTEMP), 0)
        INTO V_COUNT
        FROM LANCCTBTEMP
       WHERE NRLANCCTBTEMP = P_NRLANCCTBTEMP
         AND CDEMPRESA     = P_CDEMPRESA
      HAVING SUM(VRLANCCTBTEMP) <> 0;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_COUNT := 0;
   END;

   IF V_COUNT <> 0 THEN
      INSERT INTO ERROCTBTEMP
         (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
      VALUES
         (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '001');
      V_COUNT := 0;
   END IF;

   /*VERIFICA SE O LANCAMENTO CONTABIL ESTA DENTRO DO PERIODO CONTABIL*/
   BEGIN
      SELECT COUNT(*)
        INTO V_COUNT
        FROM LANCCTBTEMP L, CONTOPECTBL C
       WHERE L.CDEMPRESA     = P_CDEMPRESA
         AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
         AND NOT TRUNC(L.DTLANCCTBTEMP) BETWEEN C.DTINICCONT AND C.DTFINALANC
         AND L.CDEMPRESA = C.CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_COUNT := 0;
   END;

    IF V_COUNT <> 0 THEN
      INSERT INTO ERROCTBTEMP
         (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
      VALUES
         (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '002');
    END IF;


    /*VERIFICA SE EXISTE ALGUMA CONTA CONTABIL QUE NAO ESTEJA CADASTRADA*/
    FOR C_CONTA IN
       (SELECT NRSEQCTBTEMP
          FROM LANCCTBTEMP L
         WHERE L.CDEMPRESA = P_CDEMPRESA
           AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
           AND NOT EXISTS (SELECT 1
                             FROM CONTCTBL C
                            WHERE C.CDCONTCTBL = L.CDCONTCTBL)) LOOP

       INSERT INTO ERROCTBTEMP
          (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
       VALUES
          (P_CDEMPRESA, P_NRLANCCTBTEMP, C_CONTA.NRSEQCTBTEMP, '003');
    END LOOP;

    /*VERIFICA SE EXISTE ALGUM HISTORICO PADRAO NAO CADASTRADO*/
    SELECT COUNT(*)
      INTO V_COUNT
      FROM LANCCTBTEMP L
     WHERE L.CDEMPRESA = P_CDEMPRESA
       AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
       AND NOT EXISTS (SELECT 1
                         FROM HISTPADR H
                        WHERE H.CDHISTPADR = L.CDHISTPADR);

    IF V_COUNT <> 0 THEN
       INSERT INTO ERROCTBTEMP
          (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
       VALUES
          (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '004');
    END IF;

    IF V_CONTCOME = 'S' THEN
    /*VERIFICACAO DE EXISTE ALGUM CODIGO COMERCIAL QUE NAO ESTEJA CADASTRADO*/
       FOR C_CODCOME IN
          (SELECT IDLANCCTBCOME ID, CDLANCCTBCOME CODIGO, NRSEQCTBTEMP
             FROM LANCCTBTEMP
            WHERE CDEMPRESA = P_CDEMPRESA
              AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
              AND CDLANCCTBCOME IS NOT NULL
              AND IDLANCCTBCOME <> 'N') LOOP

          IF C_CODCOME.ID = 'B' THEN /*BANCO*/
             SELECT COUNT(*)
               INTO V_COUNT
               FROM CONTCORR C
              WHERE C.CDREDUCONT = C_CODCOME.CODIGO;

          ELSIF C_CODCOME.ID = 'F' THEN /*FORNECEDOR*/
             SELECT COUNT(*)
               INTO V_COUNT
               FROM FORNECEDOR
              WHERE CDFORNECED = C_CODCOME.CODIGO;

          ELSE /*CLIENTE*/
              SELECT COUNT(*)
                INTO V_COUNT
                FROM CLIENTE
               WHERE CDCLIENTE = C_CODCOME.CODIGO;
          END IF;

          IF V_COUNT = 0 THEN
             INSERT INTO ERROCTBTEMP
                (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
             VALUES
                (P_CDEMPRESA, P_NRLANCCTBTEMP, C_CODCOME.NRSEQCTBTEMP , '005');
          END IF;
      END LOOP;
   END IF;

   /*VERIFICACAO SE EXISTE ALGUMA PARTIDA ZERADA*/
   FOR C_PART IN
      (SELECT NRSEQCTBTEMP
         FROM LANCCTBTEMP
        WHERE CDEMPRESA = P_CDEMPRESA
          AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
          AND VRLANCCTBTEMP = 0) LOOP
      INSERT INTO ERROCTBTEMP
         (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
      VALUES
         (P_CDEMPRESA, P_NRLANCCTBTEMP, C_PART.NRSEQCTBTEMP , '006');
   END LOOP;
   /*VERIFICA SE EXISTE MESMA CONTA PARA DEBITO E PARA CREDIDO*/
   FOR C_CONTA IN
      (SELECT SUM(VRLANCCTBTEMP), CDCONTCTBL, CDLANCCTBCOME
         FROM LANCCTBTEMP
        WHERE CDEMPRESA = P_CDEMPRESA
          AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
		  AND (CDLANCCTBCOME,CDCONTCTBL)  IN ( SELECT CDLANCCTBCOME , CDCONTCTBL
		                                         FROM LANCCTBTEMP  
							                    WHERE NRLANCCTBTEMP = P_NRLANCCTBTEMP
                                                AND CDEMPRESA = P_CDEMPRESA)
          GROUP BY CDCONTCTBL,CDLANCCTBCOME
          HAVING SUM(VRLANCCTBTEMP) = 0) LOOP
       UPDATE LANCCTBTEMP
          SET VRLANCCTBTEMP = 0
        WHERE CDEMPRESA = P_CDEMPRESA
          AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
          AND CDCONTCTBL = C_CONTA.CDCONTCTBL;

      INSERT INTO ERROCTBTEMP
         (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
      VALUES
         (P_CDEMPRESA, P_NRLANCCTBTEMP, '' , '006');
   END LOOP;

   /*VERIFICACAO SE EXISTE ALGUM RATEIO ZERADO*/
   IF V_CONTRAT = 'S' THEN
      FOR C_RAT IN
         (SELECT VRRATECTBTEMP, NRSEQCTBTEMP
            FROM RATECTBTEMP
           WHERE CDEMPRESA     = P_CDEMPRESA
             AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
             AND VRRATECTBTEMP = 0) LOOP
         INSERT INTO ERROCTBTEMP
            (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
         VALUES
            (P_CDEMPRESA, P_NRLANCCTBTEMP, C_RAT.NRSEQCTBTEMP , '007');
      END LOOP;
   END IF;

   /*VERIFICCAO SE EXISTE ALGUMA FILIAL NAO CADASTRADA*/
   IF V_CONTRAT = 'S' THEN
      FOR C_FIL IN
         (SELECT CDFILIAL, NRSEQCTBTEMP
            FROM RATECTBTEMP
           WHERE CDEMPRESA = P_CDEMPRESA
             AND NRLANCCTBTEMP = P_NRLANCCTBTEMP)LOOP

         SELECT COUNT(*)
           INTO V_COUNT
           FROM FILIAL
          WHERE CDFILIAL = C_FIL.CDFILIAL;

         IF V_COUNT = 0 THEN
            INSERT INTO ERROCTBTEMP
               (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
            VALUES
               (P_CDEMPRESA, P_NRLANCCTBTEMP, C_FIL.NRSEQCTBTEMP , '008');
         END IF;
      END LOOP;
   END IF;

   V_RAT := 'S';
   /*VERIFICACAO SE EXISTE ALGUMA FILIAL NAO INFORMADA*/
   IF V_CONTRAT = 'S' THEN
      FOR FIL_INF IN
         (SELECT NRSEQCTBTEMP
            FROM LANCCTBTEMP L, CONTCTBL C
           WHERE L.CDEMPRESA = P_CDEMPRESA
             AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
             AND L.CDCONTCTBL = C.CDCONTCTBL
             AND C.IDCONTCCUS NOT IN ('L', 'O')
             AND NOT EXISTS (SELECT 1
                              FROM RATECTBTEMP R
                             WHERE R.NRLANCCTBTEMP = L.NRLANCCTBTEMP
                               AND R.NRSEQCTBTEMP = L.NRSEQCTBTEMP))LOOP
            V_RAT := 'N';
            INSERT INTO ERROCTBTEMP
               (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
            VALUES
               (P_CDEMPRESA, P_NRLANCCTBTEMP, FIL_INF.NRSEQCTBTEMP , '012');
      END LOOP;
   END IF;

   IF V_CONTRAT = 'S' THEN
      IF V_RAT = 'S' THEN
         /*VERIFICACAO SE EXISTE ALGUM CENTRO DE CUSTO NAO CADASTRADO PARA FILIAL INFORMADA*/
         FOR C_CCUST IN
            (SELECT CDFILIAL, CDCENTCUST, NRSEQCTBTEMP
               FROM RATECTBTEMP
              WHERE CDEMPRESA = P_CDEMPRESA
                AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
                AND CDCENTCUST IS NOT NULL
                AND CDFILIAL IS NOT NULL)LOOP

            SELECT COUNT(*)
              INTO V_COUNT
              FROM CENTCUSTFIL
             WHERE CDFILIAL   = C_CCUST.CDFILIAL
               AND CDCENTCUST = C_CCUST.CDCENTCUST;

            IF V_COUNT = 0 THEN
               INSERT INTO ERROCTBTEMP
                  (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
               VALUES
                  (P_CDEMPRESA, P_NRLANCCTBTEMP, C_CCUST.NRSEQCTBTEMP , '009');
            END IF;
         END LOOP;

         /* CENTRO DE CUSTO NAO INFORMADO */
         FOR C_CCUST_D IN
            (SELECT CDFILIAL, CDCENTCUST, NRSEQCTBTEMP
               FROM RATECTBTEMP
              WHERE CDEMPRESA = P_CDEMPRESA
                AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
                AND CDCENTCUST IS NULL
                AND CDFILIAL IS NOT NULL)LOOP

               INSERT INTO ERROCTBTEMP
                  (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
               VALUES
                  (P_CDEMPRESA, P_NRLANCCTBTEMP, C_CCUST_D.NRSEQCTBTEMP , '013');
         END LOOP;
      END IF;
   END IF;

   IF V_CONTRAT = 'S' THEN
      /*VERIFICACAO SE EXISTE SOMA DE RATEIOS DIFERENTE DO VALOR DA PARTIDA*/
      FOR C_DIF IN
         (SELECT VRLANCCTBTEMP, NRSEQCTBTEMP, NRLANCCTBTEMP, CDEMPRESA
            FROM LANCCTBTEMP
           WHERE CDEMPRESA = P_CDEMPRESA
             AND NRLANCCTBTEMP = P_NRLANCCTBTEMP)LOOP

         SELECT SUM(VRRATECTBTEMP)
           INTO V_COUNT
           FROM RATECTBTEMP
          WHERE CDEMPRESA = P_CDEMPRESA
            AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
            AND NRSEQCTBTEMP  = C_DIF.NRSEQCTBTEMP;

         IF V_COUNT <> C_DIF.VRLANCCTBTEMP THEN
            INSERT INTO ERROCTBTEMP
               (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
            VALUES
               (P_CDEMPRESA, P_NRLANCCTBTEMP, C_DIF.NRSEQCTBTEMP , '010');
         END IF;
      END LOOP;
   END IF;


   IF V_CONTCOME = 'S' THEN
      /*VERIFICA SE EXISTE ALGUMA PARTIDA DE CONTA COMERCIAL SEM CODIGO COMERCIAL*/
      FOR C_CODCOME_NULL IN
          (SELECT NRSEQCTBTEMP
             FROM LANCCTBTEMP
            WHERE CDEMPRESA = P_CDEMPRESA
              AND NRLANCCTBTEMP = P_NRLANCCTBTEMP
              AND IDLANCCTBCOME <> 'N'
              AND CDLANCCTBCOME IS NULL) LOOP

             INSERT INTO ERROCTBTEMP
                (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
             VALUES
                (P_CDEMPRESA, P_NRLANCCTBTEMP, C_CODCOME_NULL.NRSEQCTBTEMP, '011');
      END LOOP;
   END IF;

   /* VERIFICACAO SE O LANCAMENTO ESTA DENTRO DOS PERIODOS ESPECIFICOS PARA CADA MODULO */
   BEGIN
      SELECT DISTINCT IDSISTEMA
        INTO V_IDSISTEMA
        FROM LANCCTBTEMP
       WHERE NRLANCCTBTEMP = P_NRLANCCTBTEMP
         AND CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDSISTEMA := NULL;
   END;

   IF V_IDSISTEMA IS NOT NULL THEN
      IF V_IDSISTEMA IN ('BCO', 'PAG', 'REC', 'CNC') THEN
         BEGIN
            SELECT COUNT(*)
              INTO V_COUNT
              FROM CONTOPECTBL C, LANCCTBTEMP L
             WHERE C.CDEMPRESA = L.CDEMPRESA
               AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
               AND L.CDEMPRESA = P_CDEMPRESA
               AND L.DTLANCCTBTEMP BETWEEN C.DTINICCTBL AND C.DTFINACCTBL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COUNT := 0;
         END;
         IF V_COUNT = 0 THEN
            INSERT INTO ERROCTBTEMP
                (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
             VALUES
                (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '015');
         END IF;
      ELSIF V_IDSISTEMA = 'DFE' THEN
         BEGIN
            SELECT COUNT(*)
              INTO V_COUNT
              FROM CONTOPECTBL C, LANCCTBTEMP L
             WHERE C.CDEMPRESA = L.CDEMPRESA
               AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
               AND L.CDEMPRESA = P_CDEMPRESA
               AND L.DTLANCCTBTEMP BETWEEN C.DTINIINTCONTDFE AND C.DTFININTCONTDFE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COUNT := 0;
         END;
         IF V_COUNT = 0 THEN
            INSERT INTO ERROCTBTEMP
                (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
             VALUES
                (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '016');
         END IF;
      ELSIF V_IDSISTEMA = 'DFS' THEN
         BEGIN
            SELECT COUNT(*)
              INTO V_COUNT
              FROM CONTOPECTBL C, LANCCTBTEMP L
             WHERE C.CDEMPRESA = L.CDEMPRESA
               AND L.NRLANCCTBTEMP = P_NRLANCCTBTEMP
               AND L.CDEMPRESA = P_CDEMPRESA
               AND L.DTLANCCTBTEMP BETWEEN C.DTINIINTCONTDFS AND C.DTFININTCONTDFS;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COUNT := 0;
         END;
         IF V_COUNT = 0 THEN
            INSERT INTO ERROCTBTEMP
                (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDERROTEMP)
             VALUES
                (P_CDEMPRESA, P_NRLANCCTBTEMP, '', '017');
         END IF;
      END IF;
   END IF;
END;
/

