CREATE OR REPLACE PROCEDURE CTB_DFS_CLIENTE(
   P_NRLANCTBTEMP LANCCONT.NRLANCCONT%TYPE,
   P_CDFILIAL FILIAL.CDFILIAL%TYPE,
   P_NRLANCTONF NOTAFISCAL.NRLANCTONF%TYPE,
   P_VRLANCTO LANCCONT.VRLANCAMENT%TYPE)
AS
   V_LIXO VARCHAR2(100); /* VARIAVEL PARA PEGAR OS 3 ULTIMOS PARAMETROS DA FUNCAO QUE PEGA A CONTA CONTABIL DA FILIAL */
   V_IDCOME VARCHAR2(01);
   V_IDCCUS VARCHAR2(01);
   V_CONTCCUS VARCHAR2(01);
   V_VRLANCTO LANCCONT.VRLANCAMENT%TYPE;
   V_COUNT INT;
   V_NRSEQLANC LANCCONT.NRSEQLANC%TYPE;
   V_NUMPARTIDA INT;
   V_CDHISTPADR HISTPADR.CDHISTPADR%TYPE;
   V_DSHISTLANC LANCCONT.DSHISTLANC%TYPE;
   V_CDCENTCUST VARCHAR2(04);
   V_CDCONTCTBL CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCOMERCIAL FORNECEDOR.CDFORNECED%TYPE;
   V_NRLANCTEMP LANCCONT.NRLANCCONT%TYPE;
BEGIN
   V_VRLANCTO := P_VRLANCTO;
   V_NRLANCTEMP := P_NRLANCTBTEMP;
   FOR C_NOTA IN
      (SELECT IDORIGDESTNF, IDENTRSAIDOP, CDTIPOOPER, CDFILIRECE,
             CDFORNECED, CDCLIENTE, CDFILIEMIT, VRNOTAFISC,
             NRNOTAFISC, DTENTRSAID, CDEMPRESA, DTEMISSAO
        FROM NOTAFISCAL NF
       WHERE NF.NRLANCTONF = P_NRLANCTONF
         AND NF.CDFILIAL = P_CDFILIAL)LOOP

      IF ((V_VRLANCTO <> 0) AND (C_NOTA.IDENTRSAIDOP = 'S')) THEN
         CTB_DFE_HISTORICO(P_CDFILIAL, P_NRLANCTONF, V_CDHISTPADR, V_DSHISTLANC);
         BEGIN
            SELECT CDCONTFORCLI
              INTO V_CDCONTCTBL
              FROM TIPOOPERAC T
             WHERE CDTIPOOPER   = C_NOTA.CDTIPOOPER
               AND IDENTRSAIDOP = C_NOTA.IDENTRSAIDOP;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDCONTCTBL := NULL;
         END;

         BEGIN
            SELECT IDCONTGERE
              INTO V_CONTCCUS
              FROM CONTOPECTBL
             WHERE CDEMPRESA = C_NOTA.CDEMPRESA;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CONTCCUS := 'S';
         END;

         IF C_NOTA.IDORIGDESTNF = 'C' THEN
            IF V_CDCONTCTBL IS NULL THEN
               V_CDCONTCTBL := CTB_CCCLIENTE(C_NOTA.CDCLIENTE, C_NOTA.CDFILIEMIT, C_NOTA.CDEMPRESA);
            END IF;
         ELSIF C_NOTA.IDORIGDESTNF = 'F' THEN
            IF V_CDCONTCTBL IS NULL THEN
               V_CDCONTCTBL := CTB_CCFORNECEDOR(C_NOTA.CDFORNECED, C_NOTA.CDFILIEMIT, C_NOTA.CDEMPRESA);
            END IF;
         ELSE
            CONTCTBL_FILIAL(C_NOTA.CDEMPRESA, C_NOTA.CDTIPOOPER, C_NOTA.IDENTRSAIDOP, C_NOTA.CDFILIEMIT, V_CDCONTCTBL, V_LIXO, V_LIXO, V_LIXO);
         END IF;

         BUSCA_TPCOMERCIAL(V_CDCONTCTBL, V_IDCOME, V_IDCCUS);

         IF V_IDCOME = 'F' THEN
            V_CDCOMERCIAL := C_NOTA.CDFORNECED;
         ELSIF V_IDCOME = 'C' THEN
            V_CDCOMERCIAL := C_NOTA.CDCLIENTE;
         END IF;

         V_CDCENTCUST := BUSCA_CCUSTO(C_NOTA.CDFILIEMIT, C_NOTA.CDEMPRESA);
         
         BEGIN
            SELECT COUNT(*), NRSEQCTBTEMP
              INTO V_COUNT, V_NRSEQLANC
              FROM LANCCTBTEMP
             WHERE NRLANCCTBTEMP = V_NRLANCTEMP
               AND CDEMPRESA = C_NOTA.CDEMPRESA
               AND CDCONTCTBL = V_CDCONTCTBL
              GROUP BY NRSEQCTBTEMP;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COUNT := 0;
               V_NRSEQLANC := NULL;
         END;

         IF V_COUNT = 0 THEN
            BEGIN
               SELECT TO_NUMBER(MAX(NRSEQCTBTEMP))
                 INTO V_NUMPARTIDA
                 FROM LANCCTBTEMP
                WHERE NRLANCCTBTEMP = V_NRLANCTEMP
                  AND CDEMPRESA = C_NOTA.CDEMPRESA;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NUMPARTIDA := 0;
            END;
            V_NUMPARTIDA := NVL(V_NUMPARTIDA, 0) + 1;
            INSERT INTO LANCCTBTEMP
               (CDEMPRESA, NRLANCCTBTEMP, CDDOCUTEMP, NRSEQCTBTEMP,
                CDCONTCTBL, CDHISTPADR,    DSHISTCTB, DTLANCCTBTEMP,
                VRLANCCTBTEMP, IDLANCCTBCOME, CDLANCCTBCOME, IDSISTEMA)
            VALUES
               (C_NOTA.CDEMPRESA, V_NRLANCTEMP, C_NOTA.NRNOTAFISC, LPAD(TO_CHAR(V_NUMPARTIDA),5,'0'),
                V_CDCONTCTBL, V_CDHISTPADR, V_DSHISTLANC, C_NOTA.DTEMISSAO,
                V_VRLANCTO, V_IDCOME, V_CDCOMERCIAL, 'DFS');
            IF V_CONTCCUS <> 'N' THEN
               IF V_IDCCUS <> 'O' THEN
                  INSERT INTO RATECTBTEMP
                     (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDFILIAL,
                      CDCENTCUST, VRRATECTBTEMP, DTCOMPCTBTEMP, NRSEQRATTEMP)
                  VALUES
                     (C_NOTA.CDEMPRESA, V_NRLANCTEMP, LPAD(TO_CHAR(V_NUMPARTIDA),5,'0'), C_NOTA.CDFILIEMIT,
                      V_CDCENTCUST, V_VRLANCTO, C_NOTA.DTEMISSAO, '00001');
               END IF;
            END IF;
         ELSE
            UPDATE LANCCTBTEMP
               SET VRLANCCTBTEMP = VRLANCCTBTEMP + V_VRLANCTO
             WHERE NRLANCCTBTEMP = V_NRLANCTEMP
               AND CDEMPRESA = C_NOTA.CDEMPRESA
               AND CDCONTCTBL = V_CDCONTCTBL;

            BEGIN
               SELECT COUNT(*), L.NRSEQCTBTEMP
                 INTO V_COUNT, V_NRSEQLANC
                 FROM LANCCTBTEMP L, RATECTBTEMP R
                WHERE L.NRLANCCTBTEMP = V_NRLANCTEMP
                  AND L.CDEMPRESA = C_NOTA.CDEMPRESA
                  AND L.CDCONTCTBL = V_CDCONTCTBL
                  AND L.NRLANCCTBTEMP = R.NRLANCCTBTEMP
                  AND L.NRSEQCTBTEMP = R.NRSEQCTBTEMP
                  AND L.CDEMPRESA = R.CDEMPRESA
                  AND R.CDFILIAL = C_NOTA.CDFILIEMIT
                  AND R.CDCENTCUST = V_CDCENTCUST;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                   V_COUNT := 0;
                   V_NRSEQLANC := NULL;
            END;

            IF V_COUNT = 0 THEN
               IF V_CONTCCUS <> 'N' THEN
                  IF V_IDCCUS <> 'O' THEN
                     INSERT INTO RATECTBTEMP
                        (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDFILIAL,
                         CDCENTCUST, VRRATECTBTEMP, DTCOMPCTBTEMP, NRSEQRATTEMP)
                     VALUES
                        (C_NOTA.CDEMPRESA, V_NRLANCTEMP, LPAD(TO_CHAR(V_NRSEQLANC),5,'0'), C_NOTA.CDFILIEMIT,
                         V_CDCENTCUST, V_VRLANCTO, C_NOTA.DTEMISSAO, '00001');
                  END IF;
               END IF;
            ELSE
               UPDATE RATECTBTEMP
                  SET VRRATECTBTEMP = VRRATECTBTEMP + V_VRLANCTO
                WHERE CDEMPRESA = C_NOTA.CDEMPRESA
                  AND NRLANCCTBTEMP = V_NRLANCTEMP
                  AND NRSEQCTBTEMP = V_NRSEQLANC
                  AND CDFILIAL = C_NOTA.CDFILIEMIT
                  AND CDCENTCUST = V_CDCENTCUST;
            END IF;
         END IF;
      END IF;
   END LOOP;
END;
/

