DROP FUNCTION CONFERE_PARTIDA
/

CREATE OR REPLACE PROCEDURE CONFERE_PARTIDA(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDLOTE      IN CAPALOTE.CDLOTE%TYPE,
   P_NRLANCLOTE  IN LANCLOTE.NRLANCLOTE%TYPE,
   P_DTMOVI      IN DATE,
   P_ERROS OUT NOCOPY VARCHAR2,
   P_RETORNO OUT NOCOPY BOOLEAN)
IS
   V_VRDIFERENCA FLOAT;
   V_DTINICCTBL DATE;
   V_DTFINACCTBL DATE;
   V_IDCONTGERE VARCHAR2(1);
   V_CONTACTBL VARCHAR2(20);
   V_CDHISTPADR VARCHAR2(4);
   V_CODCOMERC VARCHAR2(21);
   V_CDFILIAL VARCHAR2(4);
   V_CDCENTCUST VARCHAR2(4);
   V_VRRATECCUS FLOAT;
   V_QTERRO NUMBER(10);
   V_ERROS VARCHAR2(5000);
BEGIN
   /* Verifica Diferença Lançamentos Contabeis */
   BEGIN
      SELECT SUM(ROUND(VRLANCLOTE,2))
        INTO V_VRDIFERENCA
        FROM LANCLOTE
       WHERE CDEMPRESA = P_CDEMPRESA
         AND CDLOTE = P_CDLOTE
         AND NRLANCLOTE = P_NRLANCLOTE;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
        V_VRDIFERENCA := 0;
   END;

   IF V_VRDIFERENCA <> 0 THEN
      /* 01 Total de debitos difere totais de debito */
      BEGIN
         SELECT COUNT(*)
           INTO V_QTERRO
           FROM ERROLANCLOTE
          WHERE CDEMPRESA = P_CDEMPRESA
            AND CDLOTE = P_CDLOTE
            AND NRLANCLOTE = P_NRLANCLOTE
            AND CDERROLANCLOTE = '001';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_QTERRO := 0;
      END;

      IF V_QTERRO = 0 THEN
         V_ERROS := V_ERROS||'_001';
         INSERT INTO ERROLANCLOTE
            (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
         VALUES
            (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE,'00001', '001');
      END IF;
   END IF;

   /* Retorna período contábil da empresa */
   BEGIN
      SELECT DTINICCONT, DTFINACONT, IDCONTGERE
        INTO V_DTINICCTBL, V_DTFINACCTBL, V_IDCONTGERE
        FROM CONTOPECTBL
       WHERE CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_DTINICCTBL  := NULL;
         V_DTFINACCTBL := NULL;
         V_IDCONTGERE  := NULL;
   END;

   /* VERIFICACAO DOS LANCAMENTOS */
   FOR C_LANCLOTE IN (SELECT L.CDEMPRESA, L.CDLOTE, L.NRLANCLOTE, L.NRSEQLANLOTE, L.CDCONTCTBL,
                             L.CDHISTPADR, L.DTLANCLOTE, L.NRDOCULOTE, L.DSHISTLOTE, L.VRLANCLOTE,
                             L.IDLLOTCOME, L.CDLLOTCOME, L.CDREDUZIDO, C.IDTIPOCOME
                        FROM LANCLOTE L, CONTCTBL C
                       WHERE L.CDEMPRESA = P_CDEMPRESA
                         AND L.CDLOTE = P_CDLOTE
                         AND L.NRLANCLOTE = P_NRLANCLOTE
                         AND L.CDCONTCTBL = C.CDCONTCTBL(+)) LOOP

      IF V_DTINICCTBL IS NULL OR NOT(V_DTINICCTBL <= P_DTMOVI AND V_DTFINACCTBL >= P_DTMOVI) THEN
         /* 02 data de movimentacao fora doperiodo contabil */
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '002';
         EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_002';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE,  NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '002');
         END IF;
      END IF;

      BEGIN
         SELECT CDCONTCTBL
           INTO V_CONTACTBL
           FROM CONTCTBL
          WHERE CDCONTCTBL = C_LANCLOTE.CDCONTCTBL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CONTACTBL := NULL;
      END;

      IF V_CONTACTBL IS NULL THEN
         /* 03 Conta Contabil nao cadastrada */
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '003';
         EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_003';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '003');
         END IF;
      END IF;

      BEGIN
         SELECT CDHISTPADR
           INTO V_CDHISTPADR
           FROM HISTPADR
          WHERE CDHISTPADR = C_LANCLOTE.CDHISTPADR;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDHISTPADR := NULL;
      END;

      IF V_CDHISTPADR IS NULL THEN
         /* 04 Histórico Padrao nao cadastrado */
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '004';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_004';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE,  NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '004');
         END IF;
      END IF;

      IF C_LANCLOTE.IDTIPOCOME IS NOT NULL THEN
         IF C_LANCLOTE.IDLLOTCOME = 'F' THEN
            BEGIN
               SELECT CDFORNECED
                 INTO V_CODCOMERC
                 FROM FORNECEDOR
                WHERE CDFORNECED = C_LANCLOTE.CDLLOTCOME;
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_CODCOMERC := NULL;
            END;
         END IF;

         IF C_LANCLOTE.IDLLOTCOME = 'C' THEN
            BEGIN
               SELECT CDCLIENTE
                 INTO V_CODCOMERC
                 FROM CLIENTE
                WHERE CDCLIENTE = C_LANCLOTE.CDLLOTCOME;
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_CODCOMERC := NULL;
            END;
         END IF;

         IF C_LANCLOTE.IDLLOTCOME = 'B' THEN
            BEGIN
               SELECT CDREDUCONT
                 INTO V_CODCOMERC
                 FROM CONTCORR
                WHERE CDREDUCONT = C_LANCLOTE.CDLLOTCOME;
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_CODCOMERC := NULL;
            END;
         END IF;

         IF (C_LANCLOTE.IDLLOTCOME <> C_LANCLOTE.IDTIPOCOME AND C_LANCLOTE.IDTIPOCOME IS NOT NULL)  THEN
            /* 05 - Código Comercial não cadastrado*/
            BEGIN
               SELECT COUNT(*)
                 INTO V_QTERRO
                 FROM ERROLANCLOTE
                WHERE CDEMPRESA = P_CDEMPRESA
                  AND CDLOTE = P_CDLOTE
                  AND NRLANCLOTE = P_NRLANCLOTE
                  AND CDERROLANCLOTE = '005';
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                    V_QTERRO := 0;
            END;

            IF V_QTERRO = 0 THEN
               V_ERROS := V_ERROS||'_005';
               INSERT INTO ERROLANCLOTE
                  (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
               VALUES
                  (P_CDEMPRESA, P_CDLOTE,P_NRLANCLOTE, '00001', '005');
            END IF;
         END IF;
      ELSIF C_LANCLOTE.IDLLOTCOME IS NOT NULL THEN
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '005';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_005';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE,P_NRLANCLOTE, '00001', '005');
         END IF;
      END IF;

      IF C_LANCLOTE.VRLANCLOTE = 0 THEN
         /* 06 Valor partida zerado */
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '006';
         EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_006';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '006');
         END IF;
      END IF;
      /* Verificação dos rateios */
      FOR C_RATECCUS IN (SELECT CDEMPRESA, NRLANCLOTE, NRSEQLANLOTE,
                                CDFILIAL, CDCENTCUST, VRRATECCUS
                           FROM RATECCUS
                          WHERE CDEMPRESA    = C_LANCLOTE.CDEMPRESA
                            AND CDLOTE       = C_LANCLOTE.CDLOTE
                            AND NRLANCLOTE   = C_LANCLOTE.NRLANCLOTE
                            AND NRSEQLANLOTE = C_LANCLOTE.NRSEQLANLOTE) LOOP
         IF C_RATECCUS.VRRATECCUS = 0 THEN
         /* 07   Valor rateio zerado */
            BEGIN
               SELECT COUNT(*)
                 INTO V_QTERRO
                 FROM ERROLANCLOTE
                WHERE CDEMPRESA      = P_CDEMPRESA
                  AND CDLOTE         = P_CDLOTE
                  AND NRLANCLOTE     = P_NRLANCLOTE
                  AND CDERROLANCLOTE = '007';
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_QTERRO := 0;
            END;
            IF V_QTERRO = 0 THEN
               V_ERROS := V_ERROS||'_007';
              INSERT INTO ERROLANCLOTE
                 (CDEMPRESA, CDLOTE, NRLANCLOTE,  NRSEQLANLOTE, CDERROLANCLOTE)
              VALUES
                 (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '007');
            END IF;
         END IF;

         BEGIN
            SELECT CDFILIAL
              INTO V_CDFILIAL
              FROM FILIAL
             WHERE CDFILIAL = C_RATECCUS.CDFILIAL;
         EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_CDFILIAL := NULL;
         END;

         IF V_CDFILIAL IS NULL THEN
            /* 08 Filial nao cadastrada */
            BEGIN
               SELECT COUNT(*)
                 INTO V_QTERRO
                 FROM ERROLANCLOTE
                WHERE CDEMPRESA = P_CDEMPRESA
                  AND CDLOTE = P_CDLOTE
                  AND NRLANCLOTE = P_NRLANCLOTE
                  AND CDERROLANCLOTE = '008';
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_QTERRO := 0;
            END;
            IF V_QTERRO = 0 THEN
               V_ERROS := V_ERROS||'_008';
              INSERT INTO ERROLANCLOTE
                 (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
              VALUES
                 (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '008');
            END IF;
         END IF;

         BEGIN
            SELECT CDCENTCUST
              INTO V_CDCENTCUST
              FROM CENTCUSTFIL
             WHERE CDCENTCUST = C_RATECCUS.CDCENTCUST
               AND CDFILIAL   = C_RATECCUS.CDFILIAL;
         EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_CDCENTCUST := NULL;
         END;
			/* 09 Centro de custo não cadastrado para filial */
         IF V_CDCENTCUST IS NULL THEN
            BEGIN
               SELECT COUNT(*)
                 INTO V_QTERRO
                 FROM ERROLANCLOTE
                WHERE CDEMPRESA = P_CDEMPRESA
                  AND CDLOTE = P_CDLOTE
                  AND NRLANCLOTE = P_NRLANCLOTE
                  AND CDERROLANCLOTE = '009';
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_QTERRO := 0;
            END;
            IF V_QTERRO = 0 THEN
               V_ERROS := V_ERROS||'_009';
               INSERT INTO ERROLANCLOTE
                 (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
               VALUES
                 (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '009');
            END IF;
         END IF;
      END LOOP;
      /* FIM DA VERFICAÇÃO DO RATEIO*/

      /* Verifica o valor total do rateio com a partida*/
      BEGIN
         SELECT SUM(ROUND(VRRATECCUS,2))
           INTO V_VRRATECCUS
           FROM RATECCUS
          WHERE CDEMPRESA    = C_LANCLOTE.CDEMPRESA
            AND CDLOTE       = P_CDLOTE
            AND NRLANCLOTE   = C_LANCLOTE.NRLANCLOTE
            AND NRSEQLANLOTE = C_LANCLOTE.NRLANCLOTE;
      EXCEPTION
           WHEN NO_DATA_FOUND THEN
             V_VRRATECCUS := 0;
      END;
      IF V_VRRATECCUS <> C_LANCLOTE.VRLANCLOTE THEN
         /* 10 VALOR DO RATEIO DIFERE DO LANCAMENTO DE LOTE */
         BEGIN
            SELECT COUNT(*)
              INTO V_QTERRO
              FROM ERROLANCLOTE
             WHERE CDEMPRESA = P_CDEMPRESA
               AND CDLOTE = P_CDLOTE
               AND NRLANCLOTE = P_NRLANCLOTE
               AND CDERROLANCLOTE = '010';
         EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_QTERRO := 0;
         END;
         IF V_QTERRO = 0 THEN
            V_ERROS := V_ERROS||'_010';
            INSERT INTO ERROLANCLOTE
               (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDERROLANCLOTE)
            VALUES
               (P_CDEMPRESA, P_CDLOTE, P_NRLANCLOTE, '00001', '010');
         END IF;
      END IF;
   END LOOP;
   /* FINAL VERIFICAÇÃO DOS LANCAMENTOS */

   BEGIN
      SELECT COUNT(*)
        INTO V_QTERRO
        FROM ERROLANCLOTE
       WHERE CDEMPRESA  = P_CDEMPRESA
         AND CDLOTE     = P_CDLOTE
         AND NRLANCLOTE = P_NRLANCLOTE;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
          V_QTERRO := 0;
   END;

   IF V_QTERRO = 0 THEN
   /*Retorna Verdadeiro pra procedure Contabiliza_Fos caso não encontre nenhum erro de contabilização*/
      P_RETORNO := TRUE;
   ELSE
   /*Retorna Falso pra procedure Contabiliza_Fos caso encontre algum erro de contabilização*/
      P_RETORNO := FALSE;
   END IF;
   /*Retorna os erros na contabilização*/
   P_ERROS := V_ERROS;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CONFERE_PARTIDA','0.00.000'); END;
/
