--@C:\Web_409\Srv\Src\Cli\Sap\SQL\0006_SAPSTPO_USR_PREENCHE_TABELAS_FISCAIS.sql

CREATE OR REPLACE PROCEDURE USR_PREENCHE_TABELAS_FISCAIS(
   P_CDINSCESTA  IN VARCHAR2)
AS
   V_NRINCIDCFOP  VARCHAR(20);
   V_CFOPTEMP     VARCHAR(14);
   V_FISOPEITEM   VARCHAR(10);

   V_NRINCIDENCIA VARCHAR2(20);
   V_NRINCITEMP   VARCHAR2(14);
   V_CDFISCTEMP   VARCHAR(01);

   V_NRALIQUOTA   VARCHAR2(20);
   V_NRALIQTEMP   VARCHAR2(14);
   V_VRALIQUTMP   NUMBER;
   V_VRPREDBCTMP  NUMBER;
   V_VRPERREDBC   NUMBER;
   V_VRALIQUOTA   NUMBER;

   V_COUNT       INTEGER;
BEGIN
   ---------------------------------------------------------------------------
   ---------------------------------------------------------------------------
   -- Codigo Fiscal de Operacao - CFOP ---------------------------------------
   ---------------------------------------------------------------------------
   ---------------------------------------------------------------------------
   V_CFOPTEMP := '00000000000000';
   V_NRINCIDCFOP := '0000000000';
   FOR C IN (SELECT CDEMPRESA, CDINSCESTA, USR_LUCRPRES, I.SGESTADO
             FROM INSCRESTAD I
             WHERE USR_IDINSCATIV = 'S' AND
                   INSTR(P_CDINSCESTA,I.CDINSCESTA) <> 0) LOOP


      FOR T IN (SELECT IDENTRSAIDOP, CDTIPOOPER
                FROM TIPOOPERAC
                WHERE CDTIPOOPER IN ('01','02','20','21')
                  AND IDENTRSAIDOP = 'E') LOOP

         SELECT COUNT(*)
         INTO V_COUNT
         FROM CODFISCOPER
         WHERE CDEMPRESA = C.CDEMPRESA
           AND CDINSCESTA = C.CDINSCESTA
           AND CDTIPOOPER = T.CDTIPOOPER
           AND IDENTRSAIDOP = T.IDENTRSAIDOP;

         IF V_COUNT = 0 THEN
            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);

            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '1', '102', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '2', '403', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '3', '102', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '4', '556', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '5', '556', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '6', '556', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '7', '556', '.', '01/01/2006');

            NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
            V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);
            INSERT INTO CODFISCOPER
            (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
             CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
             DSCDFISCOPER, DTVALIDCFOP)
            VALUES
            (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
             T.CDTIPOOPER, C.CDINSCESTA, '8', '556', '.', '01/01/2006');
         END IF;
      END LOOP;

      FOR I IN (SELECT DISTINCT P.CDPRODUTO, SUBSTR(II_CFOP,3,3) AS II_CFOP, P.CDARVPROD
                FROM USR_PINDICEICMS C, PRODUTO P
                WHERE II_CFOP IS NOT NULL
                  AND II_CODPRO = P.CDPRODINTE
                  AND P.CDNVPRODUTO = '4'
                  AND II_CODEST = C.SGESTADO) LOOP

         FOR T IN (SELECT IDENTRSAIDOP, CDTIPOOPER
                   FROM TIPOOPERAC
                   WHERE CDTIPOOPER IN ('01','02','20','21')
                     AND IDENTRSAIDOP = 'E') LOOP

            CONSULTA_CFOP('E', C.CDEMPRESA, I.CDARVPROD, C.CDINSCESTA, T.CDTIPOOPER, '',
                          '', '', '', '0000', '0000000000', NULL, SYSDATE);

            SELECT MAX(CDFISOPETMP)
            INTO V_FISOPEITEM
            FROM CFOPTEMP
            WHERE NRCFOPTEMP = V_CFOPTEMP;

            IF V_FISOPEITEM <> I.II_CFOP THEN
               SELECT MAX(CDFISCOPER)
               INTO V_FISOPEITEM
               FROM CODFISCOPER
               WHERE CDEMPRESA = C.CDEMPRESA
                 AND CDINSCESTA = C.CDINSCESTA
                 AND IDENTRSAIDOP = 'E'
                 AND CDTIPOOPER = '01'
                 AND CDPRODUTO = I.CDPRODUTO;

               IF V_FISOPEITEM IS NULL THEN
                  NOVO_CODIGO('FISCOPER', V_NRINCIDCFOP);
                  V_NRINCIDCFOP := SUBSTR(V_NRINCIDCFOP,11,10);

                  INSERT INTO CODFISCOPER
                  (NRINCIDCFOP, CDEMPRESA, IDENTRSAIDOP, IDENTSAICFOP,
                   CDTIPOOPER, CDINSCESTA, CDPRODUTO, CDFISCOPER,
                   DSCDFISCOPER, DTVALIDCFOP)
                  VALUES
                  (V_NRINCIDCFOP, C.CDEMPRESA, 'E', 'E',
                   '01', C.CDINSCESTA, I.CDPRODUTO, I.II_CFOP, '.', '01/01/2006');

               ELSIF V_FISOPEITEM <> I.II_CFOP THEN

                  UPDATE CODFISCOPER
                  SET CDFISCOPER = I.II_CFOP
                  WHERE CDEMPRESA = C.CDEMPRESA
                    AND CDINSCESTA = C.CDINSCESTA
                    AND IDENTRSAIDOP = 'E'
                    AND CDTIPOOPER = '01'
                    AND CDPRODUTO = I.CDPRODUTO;
               END IF;
            END IF;

            DELETE FROM CFOPTEMP
            WHERE NRCFOPTEMP = V_CFOPTEMP;
         END LOOP;
         COMMIT;
      END LOOP;

      ---------------------------------------------------------------------------
      ---------------------------------------------------------------------------
      -- Incidencia -------------------------------------------------------------
      ---------------------------------------------------------------------------
      ---------------------------------------------------------------------------
      V_NRINCITEMP := '00000000000000';
      FOR T IN (SELECT IDENTRSAIDOP, CDTIPOOPER
                FROM TIPOOPERAC
                WHERE CDTIPOOPER IN ('01','02','20','21')
                  AND IDENTRSAIDOP = 'E') LOOP

         SELECT COUNT(*)
         INTO V_COUNT
         FROM INCIDENCIA
         WHERE CDEMPRESA = C.CDEMPRESA
           AND CDINSCESTA = C.CDINSCESTA
           AND IDENTRSAIDOP = T.IDENTRSAIDOP
           AND CDTIPOOPER = T.CDTIPOOPER;

         IF V_COUNT = 0 THEN
            IF NVL(C.USR_LUCRPRES,'N') = 'N' THEN
               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '1', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '1');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '2', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '3', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '1');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '4', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '5', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '6', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '7', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');

               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, '8', C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '2');
            ELSE
               NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
               V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

               INSERT INTO INCIDENCIA
               (NRINCIDENCIA, CDEMPRESA, CDINSCESTA,
                IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                CDFISCAL)
               VALUES
               (V_NRINCIDENCIA, C.CDEMPRESA, C.CDINSCESTA,
                T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP, '3');
            END IF;

            NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
            V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

            INSERT INTO INCIDENCIA
            (NRINCIDENCIA, CDEMPRESA, CDINSCESTA,
             IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
             CDFISCAL)
            VALUES
            (V_NRINCIDENCIA, C.CDEMPRESA, C.CDINSCESTA,
             T.IDENTRSAIDOP, T.CDTIPOOPER, '50', T.IDENTRSAIDOP, '1');

            NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
            V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

            INSERT INTO INCIDENCIA
            (NRINCIDENCIA, CDEMPRESA, CDINSCESTA,
             IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
             CDFISCAL)
            VALUES
            (V_NRINCIDENCIA, C.CDEMPRESA, C.CDINSCESTA,
             T.IDENTRSAIDOP, T.CDTIPOOPER, '60', T.IDENTRSAIDOP, '1');
         END IF;

         FOR I IN (SELECT P.CDPRODUTO, P.CDARVPROD, C.*, DECODE(C.PRO_CREDITOICMS,'S','1','2') AS CDFISCAL
                   FROM USR_PPRODUTO C, PRODUTO P
                   WHERE PRO_CODIGO = P.CDPRODINTE
                     AND P.CDNVPRODUTO = '4') LOOP

            CONSULTA_INCIDENCIA(T.IDENTRSAIDOP, C.CDEMPRESA, I.CDARVPROD, C.CDINSCESTA, T.CDTIPOOPER, '0000', '0000000000', NULL);

            IF NVL(C.USR_LUCRPRES,'N') = 'N' THEN
               SELECT DECODE(MAX(CDFISCTEMP),'1','S','N') AS CDFISCTEMP
               INTO V_CDFISCTEMP
               FROM INCITEMP
               WHERE (NRINCITEMP = V_NRINCITEMP)
                 AND (CDIMPOTEMP = '00');

               IF V_CDFISCTEMP <> I.PRO_CREDITOICMS THEN
                  SELECT MAX(CDFISCAL)
                  INTO V_CDFISCTEMP
                  FROM INCIDENCIA
                  WHERE CDEMPRESA = C.CDEMPRESA
                    AND CDINSCESTA = C.CDINSCESTA
                    AND IDENTRSAIDOP = T.IDENTRSAIDOP
                    AND CDTIPOOPER = T.CDTIPOOPER
                    AND CDPRODUTO = I.CDPRODUTO
                    AND CDIMPOSTO = '00';

                  IF V_CDFISCTEMP IS NULL THEN
                     NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
                     V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

                     INSERT INTO INCIDENCIA
                     (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                      IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                      CDFISCAL)
                     VALUES
                     (V_NRINCIDENCIA, I.CDPRODUTO, C.CDEMPRESA, C.CDINSCESTA,
                      T.IDENTRSAIDOP, T.CDTIPOOPER, '00', T.IDENTRSAIDOP,
                      DECODE(I.PRO_CREDITOICMS,'S','1','2'));
                  ELSIF V_CDFISCTEMP <> I.CDFISCAL THEN

                     UPDATE INCIDENCIA
                     SET CDFISCAL = DECODE(I.PRO_CREDITOICMS,'S','1','2')
                     WHERE CDEMPRESA = C.CDEMPRESA
                       AND CDINSCESTA = C.CDINSCESTA
                       AND IDENTRSAIDOP = T.IDENTRSAIDOP
                       AND CDTIPOOPER = T.CDTIPOOPER
                       AND CDPRODUTO = I.CDPRODUTO
                       AND CDIMPOSTO = '00';
                  END IF;
               END IF;
            END IF;

            FOR IP IN (SELECT CDIMPOSTO
                       FROM IMPOSTO
                       WHERE CDIMPOSTO IN ('50', '60')) LOOP

               SELECT DECODE(MAX(CDFISCTEMP),'1','S','N') AS CDFISCTEMP
               INTO V_CDFISCTEMP
               FROM INCITEMP
               WHERE (NRINCITEMP = V_NRINCITEMP)
                 AND (CDIMPOTEMP = IP.CDIMPOSTO);

               IF V_CDFISCTEMP <> I.PRO_CREDITOPIS THEN
                  SELECT MAX(CDFISCAL)
                  INTO V_CDFISCTEMP
                  FROM INCIDENCIA
                  WHERE CDEMPRESA = C.CDEMPRESA
                    AND CDINSCESTA = C.CDINSCESTA
                    AND IDENTRSAIDOP = T.IDENTRSAIDOP
                    AND CDTIPOOPER = T.CDTIPOOPER
                    AND CDPRODUTO = I.CDPRODUTO
                    AND CDIMPOSTO = IP.CDIMPOSTO;

                  IF V_CDFISCTEMP IS NULL THEN
                     NOVO_CODIGO('INCIDENCIA', V_NRINCIDENCIA);
                     V_NRINCIDENCIA := SUBSTR(V_NRINCIDENCIA,11,10);

                     INSERT INTO INCIDENCIA
                     (NRINCIDENCIA, CDPRODUTO, CDEMPRESA, CDINSCESTA,
                      IDENTRSAIDOP, CDTIPOOPER, CDIMPOSTO, IDENTRSAIDIN,
                      CDFISCAL)
                     VALUES
                     (V_NRINCIDENCIA, I.CDPRODUTO, C.CDEMPRESA, C.CDINSCESTA,
                      T.IDENTRSAIDOP, T.CDTIPOOPER, IP.CDIMPOSTO, T.IDENTRSAIDOP,
                      DECODE(I.PRO_CREDITOPIS,'S','1','2'));
                  ELSIF V_CDFISCTEMP <> I.CDFISCAL THEN

                     UPDATE INCIDENCIA
                     SET CDFISCAL = DECODE(I.PRO_CREDITOPIS,'S','1','2')
                     WHERE CDEMPRESA = C.CDEMPRESA
                       AND CDINSCESTA = C.CDINSCESTA
                       AND IDENTRSAIDOP = T.IDENTRSAIDOP
                       AND CDTIPOOPER = T.CDTIPOOPER
                       AND CDPRODUTO = I.CDPRODUTO
                       AND CDIMPOSTO = IP.CDIMPOSTO;
                  END IF;
               END IF;
            END LOOP;

            DELETE FROM INCITEMP
            WHERE (NRINCITEMP = V_NRINCITEMP);
         END LOOP;

         COMMIT;
      END LOOP;

      ---------------------------------------------------------------------------
      ---------------------------------------------------------------------------
      -- Aliquota ---------------------------------------------------------------
      ---------------------------------------------------------------------------
      ---------------------------------------------------------------------------
      FOR I IN (SELECT T16_ESTAORIG, T16_ESTADEST, T16_ALIQUOTA
                FROM USR_TSAPORE_16) LOOP

         SELECT MAX(VRPERREDBC) AS VRPERREDBC,
                MAX(VRALIQUOTA) AS VRALIQUOTA
         INTO V_VRPERREDBC, V_VRALIQUOTA
         FROM ALIQUOTA
         WHERE CDIMPOSTO = '00'
           AND CDEMPRESA = C.CDEMPRESA
           AND CDINSCESTA = C.CDINSCESTA
           AND CDPAISORIG = '0055'
           AND SGESTAORIG = I.T16_ESTAORIG
           AND CDPAISDEST = '0055'
           AND SGESTADEST = I.T16_ESTADEST;

         IF V_VRALIQUOTA IS NULL THEN
            NOVO_CODIGO('ALIQUOTA', V_NRALIQUOTA);
            V_NRALIQUOTA := SUBSTR(V_NRALIQUOTA,11,10);

            INSERT INTO ALIQUOTA
            (NRALIQUOTA, CDIMPOSTO, CDEMPRESA, IDENTRSAIDAL, CDINSCESTA,
             CDPAISORIG, SGESTAORIG, CDPAISDEST, SGESTADEST,
             DTINIVALID, VRALIQUOTA)
            VALUES
            (V_NRALIQUOTA, '00', C.CDEMPRESA, 'E', C.CDINSCESTA,
             '0055', I.T16_ESTAORIG, '0055', I.T16_ESTADEST,
             '01/01/2000', I.T16_ALIQUOTA);
         ELSIF V_VRALIQUOTA <> I.T16_ALIQUOTA THEN

            UPDATE ALIQUOTA
            SET VRALIQUOTA = I.T16_ALIQUOTA
            WHERE CDIMPOSTO = '00'
              AND CDEMPRESA = C.CDEMPRESA
              AND CDINSCESTA = C.CDINSCESTA
              AND CDPAISORIG = '0055'
              AND SGESTAORIG = I.T16_ESTAORIG
              AND CDPAISDEST = '0055'
              AND SGESTADEST = I.T16_ESTADEST;
         END IF;

         COMMIT;
      END LOOP;

      V_NRALIQTEMP := '00000000000000';

      FOR I IN (SELECT P.CDPRODUTO, P.CDARVPROD, C.*, DECODE(C.II_BASE, 0, C.II_BASE, 100 - C.II_BASE) AS VRBASECALC
                FROM USR_PINDICEICMS C, PRODUTO P
                WHERE II_CODPRO = P.CDPRODINTE
                  AND P.CDNVPRODUTO = '4'
                  AND II_CODEST = C.SGESTADO) LOOP

         CONSULTA_ALIQUOTA('00', 'E', C.CDEMPRESA, I.CDARVPROD, C.CDINSCESTA, '0055',
                           I.II_CODEST, '0055', I.II_CODEST, NULL, 0, SYSDATE, '0000', '0000000000', NULL);

         SELECT NVL(MAX(VRPREDBCTMP),0), NVL(MAX(VRALIQUTMP),0)
         INTO V_VRPREDBCTMP, V_VRALIQUTMP
         FROM ALIQTEMP
         WHERE (NRALIQTEMP = V_NRALIQTEMP);

         IF (V_VRALIQUTMP <> I.II_INDICE) OR (V_VRPREDBCTMP <> I.VRBASECALC) THEN
            SELECT MAX(VRPERREDBC) AS VRPERREDBC,
                   MAX(VRALIQUOTA) AS VRALIQUOTA
            INTO V_VRPERREDBC, V_VRALIQUOTA
            FROM ALIQUOTA
            WHERE CDIMPOSTO = '00'
              AND CDEMPRESA = C.CDEMPRESA
              AND CDINSCESTA = C.CDINSCESTA
              AND CDPAISORIG = '0055'
              AND SGESTAORIG = I.II_CODEST
              AND CDPAISDEST = '0055'
              AND SGESTADEST = I.II_CODEST
              AND CDPRODUTO  = I.CDPRODUTO;

            IF (V_VRALIQUOTA IS NULL) THEN
               NOVO_CODIGO('ALIQUOTA', V_NRALIQUOTA);
               V_NRALIQUOTA := SUBSTR(V_NRALIQUOTA,11,10);

               INSERT INTO ALIQUOTA
               (NRALIQUOTA, CDIMPOSTO, CDEMPRESA, IDENTRSAIDAL, CDINSCESTA,
                CDPAISORIG, SGESTAORIG, CDPAISDEST, SGESTADEST,
                DTINIVALID, VRALIQUOTA, CDPRODUTO, VRPERREDBC)
               VALUES
               (V_NRALIQUOTA, '00', C.CDEMPRESA, 'E', C.CDINSCESTA,
                '0055', I.II_CODEST, '0055', I.II_CODEST,
                '01/01/2000', I.II_INDICE, I.CDPRODUTO, I.VRBASECALC);

            ELSE
               UPDATE ALIQUOTA
               SET VRALIQUOTA = I.II_INDICE,
                   VRPERREDBC = I.VRBASECALC
               WHERE CDIMPOSTO = '00'
                 AND CDEMPRESA = C.CDEMPRESA
                 AND CDPRODUTO = I.CDPRODUTO
                 AND CDINSCESTA = C.CDINSCESTA
                 AND CDPAISORIG = '0055'
                 AND SGESTAORIG = I.II_CODEST
                 AND CDPAISDEST = '0055'
                 AND SGESTADEST = I.II_CODEST;
            END IF;
         END IF;

         DELETE FROM ALIQTEMP
         WHERE (NRALIQTEMP = V_NRALIQTEMP);

         COMMIT;
      END LOOP;

      FOR I IN (SELECT CPC_ALIQPIS, CPC_ALIQCOFINS
                FROM USR_FCREDITOPISCOFINS
                WHERE CPC_CATEGORIA = 'NORMAL') LOOP

         SELECT COUNT(*)
         INTO V_COUNT
         FROM ALIQUOTA
         WHERE CDEMPRESA = C.CDEMPRESA
           AND CDINSCESTA = C.CDINSCESTA
           AND CDIMPOSTO = '50'
           AND CDPRODUTO IS NULL
           AND CDGRUPOFISC IS NULL;

         IF V_COUNT = 0 THEN
            NOVO_CODIGO('ALIQUOTA', V_NRALIQUOTA);
            V_NRALIQUOTA := SUBSTR(V_NRALIQUOTA,11,10);

            INSERT INTO ALIQUOTA
            (NRALIQUOTA, CDIMPOSTO, CDEMPRESA, IDENTRSAIDAL, CDINSCESTA,
             DTINIVALID, VRALIQUOTA)
            VALUES
            (V_NRALIQUOTA, '50', C.CDEMPRESA, 'E', C.CDINSCESTA,
             '01/01/2000', I.CPC_ALIQPIS);
         ELSE
            UPDATE ALIQUOTA
            SET VRALIQUOTA = I.CPC_ALIQPIS
            WHERE CDEMPRESA = C.CDEMPRESA
              AND CDINSCESTA = C.CDINSCESTA
              AND CDIMPOSTO = '50'
              AND CDPRODUTO IS NULL
              AND CDGRUPOFISC IS NULL
              AND VRALIQUOTA <> I.CPC_ALIQPIS;
         END IF;

         SELECT COUNT(*)
         INTO V_COUNT
         FROM ALIQUOTA
         WHERE CDEMPRESA = C.CDEMPRESA
           AND CDINSCESTA = C.CDINSCESTA
           AND CDIMPOSTO = '60'
           AND CDPRODUTO IS NULL
           AND CDGRUPOFISC IS NULL;

         IF V_COUNT = 0 THEN
            NOVO_CODIGO('ALIQUOTA', V_NRALIQUOTA);
            V_NRALIQUOTA := SUBSTR(V_NRALIQUOTA,11,10);

            INSERT INTO ALIQUOTA
            (NRALIQUOTA, CDIMPOSTO, CDEMPRESA, IDENTRSAIDAL, CDINSCESTA,
             DTINIVALID, VRALIQUOTA)
            VALUES
            (V_NRALIQUOTA, '60', C.CDEMPRESA, 'E', C.CDINSCESTA,
             '01/01/2000', I.CPC_ALIQCOFINS);
         ELSE
            UPDATE ALIQUOTA
            SET VRALIQUOTA = I.CPC_ALIQPIS
            WHERE CDEMPRESA = C.CDEMPRESA
              AND CDINSCESTA = C.CDINSCESTA
              AND CDIMPOSTO = '60'
              AND CDPRODUTO IS NULL
              AND CDGRUPOFISC IS NULL
              AND VRALIQUOTA <> I.CPC_ALIQCOFINS;
         END IF;
      END LOOP;

      COMMIT;
   END LOOP;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('USR_PREENCHE_TABELAS_FISCAIS','5.01.003'); END;
/

