CREATE OR REPLACE PROCEDURE CONSULTA_CST(
    P_ENTSAICST     IN CODSITUTRIB.IDENTSAICST%TYPE,
    P_EMPRESA       IN CODSITUTRIB.CDEMPRESA%TYPE,
    P_CDARVPROD     IN PRODUTO.CDARVPROD%TYPE,
    P_INSCESTA      IN CODSITUTRIB.CDINSCESTA%TYPE,
    P_TIPOOPER      IN CODSITUTRIB.CDTIPOOPER%TYPE,
    P_FILIAL        IN VARCHAR2,
    P_LANCTONF      IN VARCHAR2,
    P_CDGRUPOFISC   IN CODSITUTRIB.CDGRUPOFISC%TYPE,
    P_IDCSTINTEREST IN CODSITUTRIB.IDCSTINTEREST%TYPE DEFAULT 'N',
    P_IDCSTINTERNA  IN CODSITUTRIB.IDCSTINTERNA%TYPE DEFAULT 'N',
    P_IDSISTEMA     IN VARCHAR2 DEFAULT NULL,
    P_IDORIGEMPROD  IN VARCHAR2 DEFAULT 'N')

IS

    V_SEQUENCIA       VARCHAR2(02);
    V_CST1DIGT        VARCHAR2(1);
    V_CDARVPROD       VARCHAR2(20);
    V_ORIGDESTOP      VARCHAR2(1);
    V_EMPRESA         VARCHAR2(02);
    V_INSCESTA        VARCHAR2(16);
    V_CONTADOR        NUMBER;
    V_CONTINTEREST    NUMBER;
    V_CONTINTERNA     NUMBER;
    w_E               CHAR(1) := 'E';
    V_COUNTMENS       INTEGER;
    V_DSMENSCST       VARCHAR2(120);

    V_INIVALID       DATE;
    V_PAISORIG       VARCHAR2(04); -- Pais de origem (utiliza UF de Origem)
    V_ESTAORIG       VARCHAR2(02); -- Estado de origem (utiliza UF de Origem)
    V_MUNIORIG       VARCHAR2(04); -- Municipio de origem (utiliza municipio)
    V_PAISDEST       VARCHAR2(04);
    V_ESTADEST       VARCHAR2(02);
    V_MUNIDEST       VARCHAR2(04);
    V_IDENDCFOP      VARCHAR2(01);
    V_IDORIGDESTNF   VARCHAR2(01);
    V_CDCLIENTE      CLIENTE.CDCLIENTE%TYPE;
    V_CDORIGDESTNF VARCHAR2(21);
    V_IDENDEMIS      VARCHAR2(01);
    V_CDEMPRESA      VARCHAR2(02);
    V_CDINSCESTA     VARCHAR2(16);
    V_CDFILIRECE     VARCHAR2(04);
    V_CDFORNECED     FORNECEDOR.CDFORNECED%TYPE;
    V_CDFILIEMIT     VARCHAR2(04);
    V_VRITEM         FLOAT;
    V_EXISTE         INTEGER;
    V_IDNFPRESTSERV  VARCHAR2(01);
    V_IDUTLOCPRESTSERV VARCHAR2(01);
    V_ESTAPRESTSERV    VARCHAR2(02);
    V_MUNIPRESTSERV    VARCHAR2(04);
    V_CDPAISFCT      VARCHAR2(04);
    V_SGESTADOFCT    VARCHAR2(02);
    V_CDMUNICIPIOFCT VARCHAR2(04);
    V_QTDREGFCT      FLOAT;
    V_IDPRESENCIAL   VARCHAR2(01);

    V_IDOPPRESENDDIF VARCHAR2(01);
    V_IDENDEMISTPOPFIL VARCHAR2(01);
BEGIN

   DELETE CSTTEMP
   WHERE NRCSTTEMP = P_FILIAL||P_LANCTONF;

   IF P_ENTSAICST = 'S' THEN
      Begin
         SELECT MAX(CDCSTDIG1PROD)
           INTO V_CST1DIGT
           FROM PRODUTO
          WHERE (CDARVPROD = P_CDARVPROD);

         IF V_CST1DIGT IS NULL THEN
            SELECT MAX(CDCSTDIG1SAI)
              INTO   V_CST1DIGT
              FROM INSCRESTAD
             WHERE (CDEMPRESA  = P_EMPRESA) AND
                   (CDINSCESTA = P_INSCESTA);
         END IF;
      Exception
         When No_Data_Found Then
            V_CST1DIGT := Null;
      End;
   ELSE
      Begin
         SELECT MAX(IDORIGDESTOP),NVL(MAX(IDNFPRESTSERV), 'N')
           INTO   V_ORIGDESTOP, V_IDNFPRESTSERV
           FROM TIPOOPERAC
          WHERE CDTIPOOPER   = P_TIPOOPER
            AND IDENTRSAIDOP = w_E;
      Exception
         When No_Data_Found Then
            V_ORIGDESTOP := Null; V_IDNFPRESTSERV := 'N';
      End;

      IF V_ORIGDESTOP = 'L' THEN
         Begin
            SELECT MAX(CDEMPRESA), MAX(CDINSCESTA)
            INTO   V_EMPRESA, V_INSCESTA
            FROM FILIAL
            WHERE CDFILIAL IN (SELECT CDFILIRECE
                               FROM NOTAFISCAL
                               WHERE CDFILIAL   = P_FILIAL   AND
                                     NRLANCTONF = P_LANCTONF AND
                                     P_IDSISTEMA IS NULL
                               UNION ALL
                               SELECT CDFILIALPED
                               FROM PEDIDO
                               WHERE CDFILIAL = SUBSTR(P_FILIAL,1,4) AND
                                     NRPEDIDO = SUBSTR(P_FILIAL,5,10) AND
                                     P_IDSISTEMA = 'PED');
         Exception
            When No_Data_Found Then
               V_EMPRESA := Null; V_INSCESTA := Null;
         End;

         Begin
            SELECT MAX(CDCSTDIG1PROD)
              INTO V_CST1DIGT
              FROM PRODUTO
             WHERE (CDARVPROD = P_CDARVPROD);

            IF V_CST1DIGT IS NULL THEN
               SELECT MAX(CDCSTDIG1SAI)
               INTO   V_CST1DIGT
               FROM INSCRESTAD
               WHERE CDEMPRESA  = V_EMPRESA AND
                     CDINSCESTA = V_INSCESTA;
            END IF;
         Exception
            When No_Data_Found Then
               V_CST1DIGT := Null;
         End;
      ELSIF V_ORIGDESTOP = 'C' THEN
         Begin
            SELECT MAX(CDCSTDIG1PROD)
              INTO V_CST1DIGT
              FROM PRODUTO
             WHERE (CDARVPROD = P_CDARVPROD);

            IF V_CST1DIGT IS NULL THEN
               SELECT MAX(CDCSTDIG1CLI)
                 INTO V_CST1DIGT
                 FROM NOTAFISCAL N, CLIENTE C
                WHERE CDFILIAL     = P_FILIAL    AND
                      NRLANCTONF   = P_LANCTONF  AND
                      N.CDCLIENTE  = C.CDCLIENTE;
            END IF;
         Exception
            When No_Data_Found Then
               V_CST1DIGT := Null;
         End;
      ELSE
         Begin
            SELECT MAX(CDCSTDIG1PROD)
              INTO V_CST1DIGT
              FROM PRODUTO
             WHERE (CDARVPROD = P_CDARVPROD);

            IF V_CST1DIGT IS NULL THEN
               SELECT MAX(CDCSTDIG1ENT)
                 INTO   V_CST1DIGT
                 FROM NOTAFISCAL N, FORNECEDOR F
                WHERE CDFILIAL     = P_FILIAL   AND
                     NRLANCTONF    = P_LANCTONF AND
                     N.CDFORNECED  = F.CDFORNECED;
            END IF;
         Exception
            When No_Data_Found Then
               V_CST1DIGT := Null;
         End;
      END IF;
   END IF;

  	V_SEQUENCIA := '01';

   Begin
      SELECT N.DTENTRSAID,   N.CDFILIEMIT,   N.IDORIGDESTNF,   N.CDCLIENTE,
             N.CDFILIRECE,   N.CDFORNECED, (IT.VRTOTAITEM - IT.VRDESCITEM), NVL(N.IDPRESENCIAL, '9'),
             N.SGESTADO,     N.CDMUNICIPIO, NVL(R.IDOPPRESENDDIF, 'N')
        INTO V_INIVALID,   V_CDFILIEMIT, V_IDORIGDESTNF, V_CDCLIENTE,
             V_CDFILIRECE, V_CDFORNECED, V_VRITEM, V_IDPRESENCIAL,
             V_ESTAPRESTSERV, V_MUNIPRESTSERV, V_IDOPPRESENDDIF
      FROM NOTAFISCAL N, ITEMPRODUTO IT, PRODUTO P, PARAMGERAL R
      WHERE N.CDFILIAL = P_FILIAL
        AND N.NRLANCTONF = P_LANCTONF
        AND N.CDFILIAL = IT.CDFILIAL
        AND N.NRLANCTONF = IT.NRLANCTONF
        AND IT.CDPRODUTO = P.CDPRODUTO
        AND P.CDARVPROD  = P_CDARVPROD
        AND ROWNUM = '1'
        AND R.CDPARGERAL = '0001';
   Exception
      When No_Data_Found Then
         V_INIVALID  := Null; V_CDFILIEMIT  := Null; V_IDORIGDESTNF:= Null; V_CDCLIENTE:= Null;
         V_CDFILIRECE := Null; V_CDFORNECED:= Null; V_VRITEM := 0; V_IDPRESENCIAL := '9';
         V_ESTAPRESTSERV := NULL; V_MUNIPRESTSERV := NULL; V_IDOPPRESENDDIF := 'N';
   End;

   IF (P_ENTSAICST = 'S') THEN
      Begin
         SELECT NVL(IDENDCFOPFIL,'I'), CDEMPRESA,   CDINSCESTA
         INTO   V_IDENDCFOP,           V_CDEMPRESA, V_CDINSCESTA
         FROM FILIAL
         WHERE (CDFILIAL = V_CDFILIEMIT);
      Exception
         When No_Data_Found Then
            V_IDENDCFOP:= 'I'; V_CDEMPRESA:= Null; V_CDINSCESTA:= Null;
      End;

      BEGIN
         SELECT NVL(IDENDEMISTPOPFIL, 'P')
           INTO V_IDENDEMISTPOPFIL
           FROM TIPOOPERACFIL
          WHERE IDENTRSAIDOP = 'S'
            AND CDTIPOOPER = P_TIPOOPER
            AND CDFILIAL = V_CDFILIEMIT;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDENDEMISTPOPFIL := 'P';
      END;

      IF V_IDENDEMISTPOPFIL <> 'P' THEN
         IF V_IDENDEMISTPOPFIL = 'I' THEN
            BEGIN
               SELECT CDPAIS,
                      SGESTADO,
                      CDMUNICIPIO
                 INTO V_PAISORIG, 
                      V_ESTAORIG, 
                      V_MUNIORIG
                 FROM INSCRESTAD
                WHERE CDEMPRESA  = P_EMPRESA 
                  AND CDINSCESTA = P_INSCESTA;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_PAISORIG := NULL;
                  V_ESTAORIG := NULL; 
                  V_MUNIORIG := NULL;
            END;
         ELSE
            BEGIN
               SELECT CDPAIS,
                      SGESTADO,
                      CDMUNICIPIO
                 INTO V_PAISORIG,
                      V_ESTAORIG,
                      V_MUNIORIG
                 FROM ENDEFILI
                WHERE CDFILIAL     = V_CDFILIEMIT
                  AND IDTPENDEFILI = V_IDENDEMISTPOPFIL;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_PAISORIG := NULL;
                  V_ESTAORIG := NULL;
                  V_MUNIORIG := NULL;
            END;
         END IF;
      ELSE
         IF V_IDENDCFOP = 'I' THEN
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM INSCRESTAD
               WHERE CDEMPRESA  = P_EMPRESA AND
                     CDINSCESTA = P_INSCESTA;
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         ELSE
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM ENDEFILI
               WHERE (CDFILIAL     = V_CDFILIEMIT) AND
                     (IDTPENDEFILI = V_IDENDCFOP);
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         END IF;
      END IF;

      /*DESTINATARIO-REMETENTE DA NOTA*/
      IF V_IDORIGDESTNF IS NULL THEN
         Begin
            SELECT IDORIGDESTOP
            INTO   V_ORIGDESTOP
            FROM TIPOOPERAC
            WHERE IDENTRSAIDOP = 'S' AND
                  CDTIPOOPER   = P_TIPOOPER;
         Exception
            When No_Data_Found Then
               V_ORIGDESTOP := Null;
         End;
      ELSE
         V_ORIGDESTOP := V_IDORIGDESTNF;
      END IF;

      IF V_ORIGDESTOP = 'L' THEN /*DESTINATARIO -> FILIAL */
         V_CDORIGDESTNF := V_CDFILIRECE;

         Begin
            SELECT NVL(IDENDCFOPFIL,'I'), NVL(IDENDEMISFIL,'I'), CDEMPRESA  , CDINSCESTA
            INTO   V_IDENDCFOP          , V_IDENDEMIS          , V_CDEMPRESA, V_CDINSCESTA
            FROM FILIAL
            WHERE (CDFILIAL = V_CDFILIRECE);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'I'; V_IDENDEMIS := 'I'; V_CDEMPRESA := Null; V_CDINSCESTA := Null;
         End;

         IF V_IDENDEMIS = 'I' THEN
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
               FROM INSCRESTAD
               WHERE CDEMPRESA  = V_CDEMPRESA AND
                     CDINSCESTA = V_CDINSCESTA;
            Exception
               When No_Data_Found Then
                  V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
            End;
         ELSE
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
               FROM ENDEFILI
               WHERE CDFILIAL     = V_CDFILIRECE AND
                     IDTPENDEFILI = V_IDENDEMIS;
            Exception
               When No_Data_Found Then
                  V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
            End;
         END IF;
      ELSIF V_ORIGDESTOP = 'F' THEN /*DESTINATARIO -> FORNECEDOR */
         V_CDORIGDESTNF := V_CDFORNECED;
         Begin
            SELECT NVL(IDENDCFOPFOR,'P'), NVL(IDENDEMISFOR,'P')
            INTO   V_IDENDCFOP          , V_IDENDEMIS
            FROM FORNECEDOR
            WHERE (CDFORNECED = V_CDORIGDESTNF);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'P'; V_IDENDEMIS := 'P';
         End;

         Begin
            SELECT CDPAIS,     SGESTADO,   CDMUNICIPIO
            INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
            FROM ENDEFORN
            WHERE (CDFORNECED   = V_CDORIGDESTNF) AND
                  (IDTPENDEFORN = V_IDENDEMIS);
         Exception
            When No_Data_Found Then
               V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
         End;
      ELSE /*DESTINATARIO -> CLIENTE(DEFAULT) */
         V_CDORIGDESTNF := V_CDCLIENTE;
         Begin
            SELECT NVL(IDENDCFOPCLI,'P'), NVL(IDENDEMISCLI,'P')
            INTO   V_IDENDCFOP          , V_IDENDEMIS
            FROM CLIENTE
            WHERE (CDCLIENTE = V_CDORIGDESTNF);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'P'; V_IDENDEMIS := 'P';
         End;

         IF V_IDENDCFOP = 'I' THEN
            Begin
               SELECT CDPAIS    , SGESTADO,   CDMUNICIPIO
               INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
               FROM INSCRESTAD
               WHERE CDEMPRESA  = P_EMPRESA AND
                     CDINSCESTA = P_INSCESTA;
            Exception
               When No_Data_Found Then
                  V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
            End;
         ELSE

            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
               FROM ENDECLIE
               WHERE (CDCLIENTE    = V_CDORIGDESTNF) AND
                     (IDTPENDECLIE = V_IDENDEMIS);
            Exception
               When No_Data_Found Then
                  V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
            End;
         END IF;
      END IF;
      /* 
      ROTINA PARA VERIFICAR AS NOTAS DO SISTEMA FACILITIES. NESTAS NOTAS O SISTEMA 
      NAO VERIFICARA O PAIS, O ESTADO E NEM O MUNICIPIO DO DESTINATARIO DA NOTA FISCAL 
      E SIM DO LOCAL DE ATENDIMENTO DAQUELA PRESTACAO DE SERVICO
      */
      V_QTDREGFCT := 0;
      BEGIN
         SELECT CDPAIS, 
                SGESTADO, 
                CDMUNICIPIO,
                COUNT(*)
           INTO V_CDPAISFCT,
                V_SGESTADOFCT,
                V_CDMUNICIPIOFCT,
                V_QTDREGFCT
           FROM FCT_DADOSNF
          WHERE CDFILIAL = P_FILIAL
            AND NRLANCTONF = P_LANCTONF
          GROUP BY CDPAIS, 
                SGESTADO, 
                CDMUNICIPIO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDPAISFCT := NULL;
            V_SGESTADOFCT := NULL;
            V_CDMUNICIPIOFCT := NULL;
            V_QTDREGFCT := 0;
      END;
      IF (V_QTDREGFCT > 0) THEN
         V_PAISDEST := V_CDPAISFCT;
         V_ESTADEST := V_SGESTADOFCT;
         V_MUNIDEST := V_CDMUNICIPIOFCT;
      END IF;
      /*
      QUANDO A OPERACAO DA NOTA FISCAL FOR PRESENCIAL O SISTEMA USARA COMO ENDERECO
      DE DESTINO O MESMO ENDERECO DA ORIGEM. ESTA CONDICAO FOI NECESSARIA PARA ATENDER 
      A NOTA TECNICA 2015/003 e Se considera endereco de destino igual ao endereco de origem
      */
      IF (V_IDPRESENCIAL = '1') AND (V_IDOPPRESENDDIF = 'N') THEN
         V_PAISDEST := V_PAISORIG;
         V_ESTADEST := V_ESTAORIG;
         V_MUNIDEST := V_MUNIORIG;
      END IF;
   END IF;

   IF (P_ENTSAICST = 'E') THEN
      Begin
         SELECT NVL(IDENDCFOPFIL,'I'), CDEMPRESA,   CDINSCESTA
         INTO   V_IDENDCFOP,           V_CDEMPRESA, V_CDINSCESTA
         FROM FILIAL
         WHERE (CDFILIAL = V_CDFILIRECE);
      Exception
         When No_Data_Found Then
            V_IDENDCFOP:= 'I'; V_CDEMPRESA:= Null; V_CDINSCESTA:= Null;
      End;

      Begin
         SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
         INTO   V_PAISDEST, V_ESTADEST, V_MUNIDEST
         FROM INSCRESTAD
         WHERE CDEMPRESA  = P_EMPRESA AND
               CDINSCESTA = P_INSCESTA;
      Exception
         When No_Data_Found Then
            V_PAISDEST := Null; V_ESTADEST := Null; V_MUNIDEST := Null;
      End;

      BEGIN
         SELECT NVL(MAX(IDNFPRESTSERV), 'N'), NVL(MAX(IDUTLOCPRESTSERV), 'N')
           INTO V_IDNFPRESTSERV, V_IDUTLOCPRESTSERV
           FROM TIPOOPERAC
          WHERE IDENTRSAIDOP = 'E'
            AND CDTIPOOPER   = P_TIPOOPER;
          EXCEPTION
             WHEN NO_DATA_FOUND Then
                V_IDNFPRESTSERV := 'N'; V_IDUTLOCPRESTSERV := 'N';
      END;
      
      IF V_IDNFPRESTSERV = 'S' THEN
         BEGIN
            SELECT MAX(CDPAIS), MAX(SGESTADO), MAX(CDMUNICIPIO)
              INTO V_PAISDEST, V_ESTADEST, V_MUNIDEST
              FROM ENDEFILI
             WHERE (CDFILIAL     = V_CDFILIRECE) AND
                   (IDTPENDEFILI = 'P');
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_PAISDEST := NULL; V_ESTADEST := NULL; V_MUNIDEST := NULL;
         END;
      END IF;

      IF (V_IDNFPRESTSERV = 'S') AND (V_IDUTLOCPRESTSERV = 'S') THEN
         V_ESTADEST := NVL(V_ESTAPRESTSERV, V_ESTADEST);
         V_MUNIDEST := NVL(V_MUNIPRESTSERV, V_MUNIDEST);
      END IF;

      /*DESTINATARIO-REMETENTE DA NOTA*/
      IF V_IDORIGDESTNF IS NULL THEN
         Begin
            SELECT NVL(IDORIGDESTOP, 'F')
            INTO   V_ORIGDESTOP
            FROM TIPOOPERAC
            WHERE IDENTRSAIDOP = 'E' AND
                 CDTIPOOPER   = P_TIPOOPER;
         Exception
            When No_Data_Found Then
               V_ORIGDESTOP := Null;
         End;
      ELSE
        V_ORIGDESTOP := V_IDORIGDESTNF;
      END IF;

      IF V_ORIGDESTOP = 'L' THEN /*Remetente -> FILIAL */
         V_CDORIGDESTNF := V_CDFILIEMIT;

         Begin
            SELECT NVL(IDENDCFOPFIL,'I'), NVL(IDENDEMISFIL,'I'), CDEMPRESA  , CDINSCESTA
            INTO   V_IDENDCFOP          , V_IDENDEMIS          , V_CDEMPRESA, V_CDINSCESTA
            FROM FILIAL
            WHERE (CDFILIAL = V_CDFILIEMIT);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'I'; V_IDENDEMIS := 'I'; V_CDEMPRESA := Null; V_CDINSCESTA := Null;
         End;

         IF V_IDENDEMIS = 'I' THEN
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM INSCRESTAD
               WHERE CDEMPRESA  = V_CDEMPRESA AND
                     CDINSCESTA = V_CDINSCESTA;
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         ELSE
            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM ENDEFILI
               WHERE CDFILIAL     = V_CDFILIEMIT AND
                     IDTPENDEFILI = V_IDENDEMIS;
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         END IF;
      ELSIF V_ORIGDESTOP = 'F' THEN /*DESTINATARIO -> FORNECEDOR */
         V_CDORIGDESTNF := V_CDFORNECED;
         Begin
            SELECT NVL(IDENDCFOPFOR,'P'), NVL(IDENDEMISFOR,'P')
            INTO   V_IDENDCFOP          , V_IDENDEMIS
            FROM FORNECEDOR
            WHERE (CDFORNECED = V_CDORIGDESTNF);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'P'; V_IDENDEMIS := 'P';
         End;

         Begin
            SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
            INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
            FROM ENDEFORN
            WHERE (CDFORNECED   = V_CDORIGDESTNF) AND
                  (IDTPENDEFORN = V_IDENDCFOP);
         Exception
            When No_Data_Found Then
               V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
         End;
      ELSE /*DESTINATARIO -> CLIENTE */
         V_CDORIGDESTNF := V_CDCLIENTE;
         Begin
            SELECT NVL(IDENDCFOPCLI,'P'), NVL(IDENDEMISCLI,'P')
            INTO   V_IDENDCFOP          , V_IDENDEMIS
            FROM CLIENTE
            WHERE (CDCLIENTE = V_CDORIGDESTNF);
         Exception
            When No_Data_Found Then
               V_IDENDCFOP := 'P'; V_IDENDEMIS := 'P';
         End;

         IF V_IDENDCFOP = 'I' THEN
            Begin
               SELECT CDPAIS    , SGESTADO, CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM INSCRESTAD
               WHERE CDEMPRESA  = P_EMPRESA AND
                     CDINSCESTA = P_INSCESTA;
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         ELSE

            Begin
               SELECT CDPAIS    , SGESTADO  , CDMUNICIPIO
               INTO   V_PAISORIG, V_ESTAORIG, V_MUNIORIG
               FROM ENDECLIE
               WHERE (CDCLIENTE    = V_CDORIGDESTNF) AND
                     (IDTPENDECLIE = V_IDENDEMIS);
            Exception
               When No_Data_Found Then
                  V_PAISORIG := Null; V_ESTAORIG := Null; V_MUNIORIG := Null;
            End;
         END IF;
      END IF;
   END IF;

   FOR C_REGRA IN (SELECT CDSITUTRIB, IDCSTINTEREST, CDREGRAFISC
                     FROM TABLE(CONSULTA_REGRA_FISCAL('00', P_EMPRESA, P_CDGRUPOFISC, P_ENTSAICST, P_TIPOOPER, P_INSCESTA, P_CDARVPROD, V_INIVALID,
                                                      V_PAISORIG, V_ESTAORIG, V_MUNIORIG, V_PAISDEST, V_ESTADEST, V_MUNIDEST, V_VRITEM, 'N', P_IDORIGEMPROD))
                    WHERE VRALIQUOTA IS NOT NULL) LOOP


       INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP, CDREGRATMP)
                  VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_REGRA.CDSITUTRIB, NULL, NVL(C_REGRA.IDCSTINTEREST, 'T'), C_REGRA.CDREGRAFISC);


       V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
   END LOOP;

   /* Verificacao do cst referente ao imposto 00 se ha filiais parametrizadas na tela Parametros de Grupo Fiscal (Imposto/Filial) */
   FOR C_IMPFILI IN (SELECT G.CDIMPOSTO, G.CDGRUPOFISC
                       FROM GRUPOFISCIMPFIL G,
                            (SELECT DECODE(P_ENTSAICST, 'E', CDFILIRECE, CDFILIEMIT) AS CDFILIAL
                               FROM NOTAFISCAL
                              WHERE CDFILIAL = P_FILIAL
                                AND NRLANCTONF = P_LANCTONF
                                AND P_IDSISTEMA IS NULL
                             UNION ALL
                             SELECT CDFILIAL
                               FROM PEDIDO
                              WHERE CDFILIAL = SUBSTR(P_FILIAL,1,4)
                                AND NRPEDIDO = SUBSTR(P_FILIAL,5,10)
                                AND P_IDSISTEMA = 'PED') F
                      WHERE G.CDFILIAL = F.CDFILIAL
                        AND NOT G.CDGRUPOFISC IS NULL)
   LOOP
      FOR C_REGRA IN (SELECT *
                        FROM TABLE(CONSULTA_REGRA_FISCAL('00', P_EMPRESA, C_IMPFILI.CDGRUPOFISC, P_ENTSAICST, P_TIPOOPER, P_INSCESTA, P_CDARVPROD, V_INIVALID,
                                                         V_PAISORIG, V_ESTAORIG, V_MUNIORIG, V_PAISDEST, V_ESTADEST,V_MUNIDEST, V_VRITEM, 'N', P_IDORIGEMPROD))
                       WHERE VRALIQUOTA IS NOT NULL) LOOP

         SELECT COUNT(*)
           INTO V_EXISTE
           FROM CSTTEMP
          WHERE NRCSTTEMP  = P_FILIAL||P_LANCTONF
            AND CDREGRATMP IS NOT NULL;

         IF V_EXISTE > 0 THEN
            DELETE FROM CSTTEMP
            WHERE NRCSTTEMP  = P_FILIAL||P_LANCTONF;
         END IF;

         INSERT INTO CSTTEMP(NRCSTTEMP,            NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP, CDREGRATMP)
                      VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_REGRA.CDSITUTRIB, NULL, NVL(C_REGRA.IDCSTINTEREST, 'T'), C_REGRA.CDREGRAFISC);


         V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
      END LOOP;
   END LOOP;

   /* Verificacao das incidencias referente as filiais parametrizadas na tela Parametros de Grupo Fiscal (Imposto/Inscricao Estadual) */
   /* Por Fornecedor */
   FOR C_IMPINSCESTAFO IN (SELECT G.CDIMPOSTO, G.CDGRUPOFISC
                             FROM GRFISIMPIEFORN G,
                                  (SELECT CDFORNECED
                                     FROM NOTAFISCAL
                                    WHERE CDFILIAL = P_FILIAL
                                      AND NRLANCTONF = P_LANCTONF) N
                            WHERE G.CDFORNECED = N.CDFORNECED
                              AND G.CDEMPRESA = P_EMPRESA
                              AND G.CDINSCESTA = P_INSCESTA
                              AND NOT G.CDGRUPOFISC IS NULL)
   LOOP
      FOR C_REGRA IN (SELECT *
                        FROM TABLE(CONSULTA_REGRA_FISCAL('00', P_EMPRESA, C_IMPINSCESTAFO.CDGRUPOFISC, P_ENTSAICST, P_TIPOOPER, P_INSCESTA, P_CDARVPROD, V_INIVALID,
                                                          V_PAISORIG, V_ESTAORIG, V_MUNIORIG, V_PAISDEST, V_ESTADEST,V_MUNIDEST, V_VRITEM, 'N', P_IDORIGEMPROD))
                       WHERE VRALIQUOTA IS NOT NULL) LOOP

         IF V_EXISTE > 0 THEN
            DELETE FROM CSTTEMP
            WHERE NRCSTTEMP  = P_FILIAL||P_LANCTONF
              AND CDREGRATMP IS NOT NULL;
         END IF;

         INSERT INTO CSTTEMP(NRCSTTEMP,            NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP, CDREGRATMP)
                      VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_REGRA.CDSITUTRIB, NULL, NVL(C_REGRA.IDCSTINTEREST, 'T'), C_REGRA.CDREGRAFISC);


         V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
      END LOOP;
   END LOOP;

   /* Verificacao das incidencias referente as filiais parametrizadas na tela Parametros de Grupo Fiscal (Imposto/Inscricao Estadual) */
   /* Por Cliente */
   FOR C_IMPINSCESTACL IN (SELECT G.CDIMPOSTO, G.CDGRUPOFISC
                             FROM GRFISIMPIECLIE G,
                                 (SELECT CDCLIENTE
                                    FROM NOTAFISCAL
                                   WHERE CDFILIAL = P_FILIAL
                                     AND NRLANCTONF = P_LANCTONF) N
                           WHERE G.CDCLIENTE = N.CDCLIENTE
                             AND G.CDEMPRESA = P_EMPRESA
                             AND G.CDINSCESTA = P_INSCESTA
                             AND NOT G.CDGRUPOFISC IS NULL)
   LOOP

      FOR C_REGRA IN (SELECT *
                        FROM TABLE(CONSULTA_REGRA_FISCAL('00', P_EMPRESA, C_IMPINSCESTACL.CDGRUPOFISC, P_ENTSAICST, P_TIPOOPER, P_INSCESTA, P_CDARVPROD, V_INIVALID,
                                                         V_PAISORIG, V_ESTAORIG, V_MUNIORIG, V_PAISDEST, V_ESTADEST, V_MUNIDEST, V_VRITEM, 'N', P_IDORIGEMPROD))
                       WHERE VRALIQUOTA IS NOT NULL) LOOP

         IF V_EXISTE > 0 THEN
            DELETE FROM CSTTEMP
            WHERE NRCSTTEMP  = P_FILIAL||P_LANCTONF
              AND CDREGRATMP IS NOT NULL;
         END IF;

         INSERT INTO CSTTEMP(NRCSTTEMP,            NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                      VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_REGRA.CDSITUTRIB, NULL, NVL(C_REGRA.IDCSTINTEREST, 'T'));


         V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
      END LOOP;
   END LOOP;

   V_EXISTE := 0;
   SELECT COUNT(*)
     INTO V_EXISTE
     FROM CSTTEMP
    WHERE NRCSTTEMP  = P_FILIAL||P_LANCTONF
      AND CDREGRATMP IS NOT NULL;

   IF NOT(V_EXISTE > 0) THEN


      IF P_IDCSTINTERNA = 'S' THEN
         IF P_CDARVPROD IS NULL THEN
            SELECT COUNT(*)
              INTO V_CONTINTERNA
              FROM CODSITUTRIB C
             WHERE (IDENTSAICST = P_ENTSAICST)
               AND (CDEMPRESA   = P_EMPRESA)
               AND (CDTIPOOPER  = P_TIPOOPER)
               AND (CDINSCESTA  = RTRIM(P_INSCESTA))
               AND (C.IDCSTINTEREST = 'N')
               AND (C.IDCSTINTERNA  = 'S')
               AND (C.CDPRODUTO IS NULL);
         ELSE
            SELECT COUNT(*)
              INTO V_CONTINTERNA
              FROM CODSITUTRIB C, PRODUTO P
             WHERE (IDENTSAICST = P_ENTSAICST)
               AND (CDEMPRESA   = P_EMPRESA)
               AND (CDTIPOOPER  = P_TIPOOPER)
               AND (CDINSCESTA  = RTRIM(P_INSCESTA))
               AND (C.CDPRODUTO = P.CDPRODUTO(+))
               AND (C.IDCSTINTEREST = 'N')
               AND (C.IDCSTINTERNA  = 'S')
               AND (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);
         END IF;

         IF V_CONTINTERNA > 0 THEN
            IF (P_CDGRUPOFISC IS NOT NULL) THEN
               SELECT MAX(P.CDARVPROD), COUNT(*)
               INTO   V_CDARVPROD,      V_CONTADOR
               FROM CODSITUTRIB C, PRODUTO P
               WHERE (IDENTSAICST = P_ENTSAICST)       AND
                     (CDEMPRESA   = P_EMPRESA)         AND
                     (CDTIPOOPER  = P_TIPOOPER)        AND
                     (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                     (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                     (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                     (C.IDCSTINTEREST = 'N')           AND
                     (C.IDCSTINTERNA  = 'S')           AND
                     (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


               IF V_CONTADOR > 0 THEN
                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'N')             AND
                                          (C.IDCSTINTERNA  = 'S')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'I');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                     FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                        FROM CODSITUTRIB C, MENSCST M
                                        WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                              (C.CDEMPRESA   = P_EMPRESA)         AND
                                              (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                              (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                              (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                              (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                              (C.IDCSTINTEREST = 'N')             AND
                                              (C.IDCSTINTERNA  = 'S')             AND
                                              (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'I');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                     END LOOP;
                  END IF;
               END IF;
            ELSE
               SELECT MAX(P.CDARVPROD), COUNT(*)
               INTO   V_CDARVPROD,      V_CONTADOR
               FROM CODSITUTRIB C, PRODUTO P
               WHERE (IDENTSAICST = P_ENTSAICST)       AND
                     (CDEMPRESA   = P_EMPRESA)         AND
                     (CDTIPOOPER  = P_TIPOOPER)        AND
                     (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                     (CDGRUPOFISC IS NULL)             AND
                     (C.CDPRODUTO = P.CDPRODUTO)       AND
                     (C.IDCSTINTEREST = 'N')           AND
                     (C.IDCSTINTERNA  = 'S')           AND
                     (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

               IF NOT(V_CDARVPROD IS NULL) THEN
                  FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                 FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                 WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                       (C.CDEMPRESA   = P_EMPRESA)         AND
                                       (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                       (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                       (C.CDGRUPOFISC IS NULL)             AND
                                       (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                       (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                       (C.IDCSTINTEREST = 'N')             AND
                                       (C.IDCSTINTERNA  = 'S')             AND
                                       (P.CDARVPROD   = V_CDARVPROD)) LOOP

                     SELECT COUNT(*)
                       INTO V_COUNTMENS
                       FROM MENSAGEM
                      WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                        AND IDSITUACAOMENS = 'I';

                     IF V_COUNTMENS > 0 THEN
                        V_DSMENSCST := NULL;
                     ELSE
                        V_DSMENSCST := C_CST.DSMENSCST;
                     END IF;

                     INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                     VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'I');

                     V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                  END LOOP;
               ELSIF (V_CDARVPROD IS NULL) THEN
                  FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                     FROM CODSITUTRIB C, MENSCST M
                                     WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                           (C.CDEMPRESA = P_EMPRESA)          AND
                                           (C.CDTIPOOPER = P_TIPOOPER)        AND
                                           (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                           (C.CDGRUPOFISC IS NULL)            AND
                                           (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                           (C.IDCSTINTEREST = 'N')            AND
                                           (C.IDCSTINTERNA  = 'S')            AND
                                           (C.CDPRODUTO IS NULL)) LOOP

                     SELECT COUNT(*)
                       INTO V_COUNTMENS
                       FROM MENSAGEM
                      WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                        AND IDSITUACAOMENS = 'I';

                     IF V_COUNTMENS > 0 THEN
                        V_DSMENSCST := NULL;
                     ELSE
                        V_DSMENSCST := C_CSTNULL.DSMENSCST;
                     END IF;

                     INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                     VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'I');

                     V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                  END LOOP;
               END IF;
            END IF;
         ELSE
            IF P_CDARVPROD IS NULL THEN
               SELECT COUNT(*)
                 INTO V_CONTINTEREST
                 FROM CODSITUTRIB C
                WHERE (IDENTSAICST = P_ENTSAICST)
                  AND (CDEMPRESA   = P_EMPRESA)
                  AND (CDTIPOOPER  = P_TIPOOPER)
                  AND (CDINSCESTA  = RTRIM(P_INSCESTA))
                  AND (C.IDCSTINTEREST = 'S')
                  AND (C.IDCSTINTERNA  = 'N')
                  AND (C.CDPRODUTO IS NULL);
            ELSE
               SELECT COUNT(*)
                 INTO V_CONTINTEREST
                 FROM CODSITUTRIB C, PRODUTO P
                WHERE (IDENTSAICST = P_ENTSAICST)
                  AND (CDEMPRESA   = P_EMPRESA)
                  AND (CDTIPOOPER  = P_TIPOOPER)
                  AND (CDINSCESTA  = RTRIM(P_INSCESTA))
                  AND (C.CDPRODUTO = P.CDPRODUTO(+))
                  AND (C.IDCSTINTEREST = 'S')
                  AND (C.IDCSTINTERNA  = 'N')
                  AND (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);
            END IF;

            IF V_CONTINTEREST > 0 THEN
               IF (P_CDGRUPOFISC IS NOT NULL) THEN
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                        (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                        (C.IDCSTINTEREST = 'S')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


                  IF V_CONTADOR > 0 THEN
                     IF NOT(V_CDARVPROD IS NULL) THEN
                        FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                       FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                       WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                             (C.CDEMPRESA   = P_EMPRESA)         AND
                                             (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                             (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                             (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                             (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                             (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                             (C.IDCSTINTEREST = 'S')             AND
                                             (C.IDCSTINTERNA  = 'N')             AND
                                             (P.CDARVPROD   = V_CDARVPROD)) LOOP
                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CST.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'T');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     ELSIF (V_CDARVPROD IS NULL) THEN
                        FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                           FROM CODSITUTRIB C, MENSCST M
                                           WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                                 (C.CDEMPRESA   = P_EMPRESA)         AND
                                                 (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                                 (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                                 (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                                 (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                                 (C.IDCSTINTEREST = 'S')             AND
                                                 (C.IDCSTINTERNA  = 'N')             AND
                                                 (C.CDPRODUTO IS NULL)) LOOP
                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CSTNULL.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'T');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                        END LOOP;
                     END IF;
                  END IF;
               ELSE
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC IS NULL)             AND
                        (C.CDPRODUTO = P.CDPRODUTO)       AND
                        (C.IDCSTINTEREST = 'S')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.CDGRUPOFISC IS NULL)             AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'S')             AND
                                          (C.IDCSTINTERNA  = 'N')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'T');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                     FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                        FROM CODSITUTRIB C, MENSCST M
                                        WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                              (C.CDEMPRESA = P_EMPRESA)          AND
                                              (C.CDTIPOOPER = P_TIPOOPER)        AND
                                              (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                              (C.CDGRUPOFISC IS NULL)            AND
                                              (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                              (C.IDCSTINTEREST = 'S')            AND
                                              (C.IDCSTINTERNA  = 'N')            AND
                                              (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'T');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  END IF;
               END IF;
            ELSE
               IF (P_CDGRUPOFISC IS NOT NULL) THEN
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                        (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                        (C.IDCSTINTEREST = 'N')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


                  IF V_CONTADOR > 0 THEN
                     IF NOT(V_CDARVPROD IS NULL) THEN
                        FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                       FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                       WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                             (C.CDEMPRESA   = P_EMPRESA)         AND
                                             (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                             (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                             (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                             (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                             (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                             (C.IDCSTINTEREST = 'N')             AND
                                             (C.IDCSTINTERNA  = 'N')             AND
                                             (P.CDARVPROD   = V_CDARVPROD)) LOOP

                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CST.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     ELSIF (V_CDARVPROD IS NULL) THEN
                        FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                           FROM CODSITUTRIB C, MENSCST M
                                           WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                                 (C.CDEMPRESA   = P_EMPRESA)         AND
                                                 (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                                 (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                                 (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                                 (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                                 (C.IDCSTINTEREST = 'N')             AND
                                                 (C.IDCSTINTERNA  = 'N')             AND
                                                 (C.CDPRODUTO IS NULL)) LOOP

                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CSTNULL.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     END IF;
                  END IF;
               ELSE
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC IS NULL)             AND
                        (C.CDPRODUTO = P.CDPRODUTO)       AND
                        (C.IDCSTINTEREST = 'N')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.CDGRUPOFISC IS NULL)             AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'N')             AND
                                          (C.IDCSTINTERNA  = 'N')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                      FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                         FROM CODSITUTRIB C, MENSCST M
                                         WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                               (C.CDEMPRESA = P_EMPRESA)          AND
                                               (C.CDTIPOOPER = P_TIPOOPER)        AND
                                               (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                               (C.CDGRUPOFISC IS NULL)            AND
                                               (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                               (C.IDCSTINTEREST = 'N')            AND
                                               (C.IDCSTINTERNA  = 'N')            AND
                                               (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  END IF;
               END IF;
            END IF;
         END IF;
      ELSE
         IF P_IDCSTINTEREST = 'S' THEN
            IF P_CDARVPROD IS NULL THEN
               SELECT COUNT(*)
                 INTO   V_CONTINTEREST
                 FROM CODSITUTRIB C
                WHERE (IDENTSAICST = P_ENTSAICST)
                  AND (CDEMPRESA   = P_EMPRESA)
                  AND (CDTIPOOPER  = P_TIPOOPER)
                  AND (CDINSCESTA  = RTRIM(P_INSCESTA))
                  AND (C.IDCSTINTEREST = 'S')
                  AND (C.IDCSTINTERNA  = 'N')
                  AND (C.CDPRODUTO IS NULL);
            ELSE
               SELECT COUNT(*)
                 INTO   V_CONTINTEREST
                 FROM CODSITUTRIB C, PRODUTO P
                WHERE (IDENTSAICST = P_ENTSAICST)
                  AND (CDEMPRESA   = P_EMPRESA)
                  AND (CDTIPOOPER  = P_TIPOOPER)
                  AND (CDINSCESTA  = RTRIM(P_INSCESTA))
                  AND (C.CDPRODUTO = P.CDPRODUTO(+))
                  AND (C.IDCSTINTEREST = 'S')
                  AND (C.IDCSTINTERNA  = 'N')
                  AND (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);
            END IF;

            IF V_CONTINTEREST > 0 THEN
               IF (P_CDGRUPOFISC IS NOT NULL) THEN
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                        (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                        (C.IDCSTINTEREST = 'S')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


                  IF V_CONTADOR > 0 THEN
                     IF NOT(V_CDARVPROD IS NULL) THEN
                        FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                       FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                       WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                             (C.CDEMPRESA   = P_EMPRESA)         AND
                                             (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                             (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                             (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                             (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                             (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                             (C.IDCSTINTEREST = 'S')             AND
                                             (C.IDCSTINTERNA  = 'N')             AND
                                             (P.CDARVPROD   = V_CDARVPROD)) LOOP
                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CST.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'T');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     ELSIF (V_CDARVPROD IS NULL) THEN
                        FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                           FROM CODSITUTRIB C, MENSCST M
                                           WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                                 (C.CDEMPRESA   = P_EMPRESA)         AND
                                                 (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                                 (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                                 (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                                 (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                                 (C.IDCSTINTEREST = 'S')             AND
                                                 (C.IDCSTINTERNA  = 'N')             AND
                                                 (C.CDPRODUTO IS NULL)) LOOP
                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CSTNULL.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'T');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                        END LOOP;
                     END IF;
                  END IF;
               ELSE
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC IS NULL)             AND
                        (C.CDPRODUTO = P.CDPRODUTO)       AND
                        (C.IDCSTINTEREST = 'S')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.CDGRUPOFISC IS NULL)             AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'S')             AND
                                          (C.IDCSTINTERNA  = 'N')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'T');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                     FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                        FROM CODSITUTRIB C, MENSCST M
                                        WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                              (C.CDEMPRESA = P_EMPRESA)          AND
                                              (C.CDTIPOOPER = P_TIPOOPER)        AND
                                              (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                              (C.CDGRUPOFISC IS NULL)            AND
                                              (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                              (C.IDCSTINTEREST = 'S')            AND
                                              (C.IDCSTINTERNA  = 'N')            AND
                                              (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'T');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  END IF;
               END IF;
            ELSE
               IF (P_CDGRUPOFISC IS NOT NULL) THEN
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                        (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                        (C.IDCSTINTEREST = 'N')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


                  IF V_CONTADOR > 0 THEN
                     IF NOT(V_CDARVPROD IS NULL) THEN
                        FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                       FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                       WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                             (C.CDEMPRESA   = P_EMPRESA)         AND
                                             (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                             (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                             (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                             (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                             (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                             (C.IDCSTINTEREST = 'N')             AND
                                             (C.IDCSTINTERNA  = 'N')             AND
                                             (P.CDARVPROD   = V_CDARVPROD)) LOOP

                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CST.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     ELSIF (V_CDARVPROD IS NULL) THEN
                        FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                           FROM CODSITUTRIB C, MENSCST M
                                           WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                                 (C.CDEMPRESA   = P_EMPRESA)         AND
                                                 (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                                 (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                                 (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                                 (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                                 (C.IDCSTINTEREST = 'N')             AND
                                                 (C.IDCSTINTERNA  = 'N')             AND
                                                 (C.CDPRODUTO IS NULL)) LOOP

                           SELECT COUNT(*)
                             INTO V_COUNTMENS
                             FROM MENSAGEM
                            WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                              AND IDSITUACAOMENS = 'I';

                           IF V_COUNTMENS > 0 THEN
                              V_DSMENSCST := NULL;
                           ELSE
                              V_DSMENSCST := C_CSTNULL.DSMENSCST;
                           END IF;

                           INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                           VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                           V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                        END LOOP;
                     END IF;
                  END IF;
               ELSE
                  SELECT MAX(P.CDARVPROD), COUNT(*)
                  INTO   V_CDARVPROD,      V_CONTADOR
                  FROM CODSITUTRIB C, PRODUTO P
                  WHERE (IDENTSAICST = P_ENTSAICST)       AND
                        (CDEMPRESA   = P_EMPRESA)         AND
                        (CDTIPOOPER  = P_TIPOOPER)        AND
                        (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                        (CDGRUPOFISC IS NULL)             AND
                        (C.CDPRODUTO = P.CDPRODUTO)       AND
                        (C.IDCSTINTEREST = 'N')           AND
                        (C.IDCSTINTERNA  = 'N')           AND
                        (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.CDGRUPOFISC IS NULL)             AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'N')             AND
                                          (C.IDCSTINTERNA  = 'N')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                      FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                         FROM CODSITUTRIB C, MENSCST M
                                         WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                               (C.CDEMPRESA = P_EMPRESA)          AND
                                               (C.CDTIPOOPER = P_TIPOOPER)        AND
                                               (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                               (C.CDGRUPOFISC IS NULL)            AND
                                               (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                               (C.IDCSTINTEREST = 'N')            AND
                                               (C.IDCSTINTERNA  = 'N')            AND
                                               (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                     END LOOP;
                  END IF;
               END IF;
            END IF;
         ELSE
            IF (P_CDGRUPOFISC IS NOT NULL) THEN
               SELECT MAX(P.CDARVPROD), COUNT(*)
               INTO   V_CDARVPROD,      V_CONTADOR
               FROM CODSITUTRIB C, PRODUTO P
               WHERE (IDENTSAICST = P_ENTSAICST)       AND
                     (CDEMPRESA   = P_EMPRESA)         AND
                     (CDTIPOOPER  = P_TIPOOPER)        AND
                     (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                     (CDGRUPOFISC = P_CDGRUPOFISC)     AND
                     (C.CDPRODUTO = P.CDPRODUTO(+))    AND
                     (C.IDCSTINTEREST = 'N')           AND
                     (C.IDCSTINTERNA  = 'N')           AND
                     (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);


               IF V_CONTADOR > 0 THEN
                  IF NOT(V_CDARVPROD IS NULL) THEN
                     FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                    FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                    WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                          (C.CDEMPRESA   = P_EMPRESA)         AND
                                          (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                          (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                          (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                          (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                          (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                          (C.IDCSTINTEREST = 'N')             AND
                                          (C.IDCSTINTERNA  = 'N')             AND
                                          (P.CDARVPROD   = V_CDARVPROD)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CST.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                     END LOOP;
                  ELSIF (V_CDARVPROD IS NULL) THEN
                     FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                        FROM CODSITUTRIB C, MENSCST M
                                        WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                              (C.CDEMPRESA   = P_EMPRESA)         AND
                                              (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                              (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                              (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                              (C.CDGRUPOFISC = P_CDGRUPOFISC)     AND
                                              (C.IDCSTINTEREST = 'N')             AND
                                              (C.IDCSTINTERNA  = 'N')             AND
                                              (C.CDPRODUTO IS NULL)) LOOP

                        SELECT COUNT(*)
                          INTO V_COUNTMENS
                          FROM MENSAGEM
                         WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                           AND IDSITUACAOMENS = 'I';

                        IF V_COUNTMENS > 0 THEN
                           V_DSMENSCST := NULL;
                        ELSE
                           V_DSMENSCST := C_CSTNULL.DSMENSCST;
                        END IF;

                        INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                        VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                        V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);
                     END LOOP;
                  END IF;
               END IF;
            ELSE
               SELECT MAX(P.CDARVPROD), COUNT(*)
               INTO   V_CDARVPROD,      V_CONTADOR
               FROM CODSITUTRIB C, PRODUTO P
               WHERE (IDENTSAICST = P_ENTSAICST)       AND
                     (CDEMPRESA   = P_EMPRESA)         AND
                     (CDTIPOOPER  = P_TIPOOPER)        AND
                     (CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                     (CDGRUPOFISC IS NULL)             AND
                     (C.CDPRODUTO = P.CDPRODUTO)       AND
                     (C.IDCSTINTEREST = 'N')           AND
                     (C.IDCSTINTERNA  = 'N')           AND
                     (INSTR(P_CDARVPROD,NVL(RTRIM(P.CDARVPROD),P_CDARVPROD)) = 1);

               IF NOT(V_CDARVPROD IS NULL) THEN
                  FOR C_CST IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                 FROM CODSITUTRIB C, MENSCST M, PRODUTO P
                                 WHERE (C.IDENTSAICST = P_ENTSAICST)       AND
                                       (C.CDEMPRESA   = P_EMPRESA)         AND
                                       (C.CDTIPOOPER  = P_TIPOOPER)        AND
                                       (C.CDINSCESTA  = RTRIM(P_INSCESTA)) AND
                                       (C.CDGRUPOFISC IS NULL)             AND
                                       (C.NRINCIDCST  = M.NRINCIDCST(+))   AND
                                       (C.CDPRODUTO   = P.CDPRODUTO(+))    AND
                                       (C.IDCSTINTEREST = 'N')             AND
                                       (C.IDCSTINTERNA  = 'N')             AND
                                       (P.CDARVPROD   = V_CDARVPROD)) LOOP

                     SELECT COUNT(*)
                       INTO V_COUNTMENS
                       FROM MENSAGEM
                      WHERE CDMENSAGEM = C_CST.CDMENSAGEM
                        AND IDSITUACAOMENS = 'I';

                     IF V_COUNTMENS > 0 THEN
                        V_DSMENSCST := NULL;
                     ELSE
                        V_DSMENSCST := C_CST.DSMENSCST;
                     END IF;

                     INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                     VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CST.CDSITUTRIB, V_DSMENSCST, 'E');

                     V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                  END LOOP;
               ELSIF (V_CDARVPROD IS NULL) THEN
                   FOR C_CSTNULL IN ( SELECT CDSITUTRIB, DSMENSCST, CDMENSAGEM
                                      FROM CODSITUTRIB C, MENSCST M
                                      WHERE (C.IDENTSAICST = P_ENTSAICST)      AND
                                            (C.CDEMPRESA = P_EMPRESA)          AND
                                            (C.CDTIPOOPER = P_TIPOOPER)        AND
                                            (C.CDINSCESTA = RTRIM(P_INSCESTA)) AND
                                            (C.CDGRUPOFISC IS NULL)            AND
                                            (C.NRINCIDCST = M.NRINCIDCST(+))   AND
                                            (C.IDCSTINTEREST = 'N')            AND
                                            (C.IDCSTINTERNA  = 'N')            AND
                                            (C.CDPRODUTO IS NULL)) LOOP

                     SELECT COUNT(*)
                       INTO V_COUNTMENS
                       FROM MENSAGEM
                      WHERE CDMENSAGEM = C_CSTNULL.CDMENSAGEM
                        AND IDSITUACAOMENS = 'I';

                     IF V_COUNTMENS > 0 THEN
                        V_DSMENSCST := NULL;
                     ELSE
                        V_DSMENSCST := C_CSTNULL.DSMENSCST;
                     END IF;

                     INSERT INTO CSTTEMP(NRCSTTEMP, NRSEQUCST, CDSITTRIBTMP, DSMENSCSTTMP, IDOPERACSTTMP)
                     VALUES(P_FILIAL||P_LANCTONF, V_SEQUENCIA, V_CST1DIGT||C_CSTNULL.CDSITUTRIB, V_DSMENSCST, 'E');

                     V_SEQUENCIA := LPAD(RTRIM(V_SEQUENCIA + '01'),2,0);

                  END LOOP;
               END IF;
            END IF;
         END IF;
      END IF;
   END IF;
END;
/