CREATE OR REPLACE PROCEDURE IMPOSTOROP_RELGESDIRETA(
   P_CDFILIAL       IN FILIAL.CDFILIAL%TYPE,
   P_DATAINI        IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DATAFIM        IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRAPURACGD     IN TRAB_APURAC_GD.NRAPURACGD%TYPE,
   P_NRROPFIL       IN VARCHAR2,
   P_NRLANCROP      IN VARCHAR2,
   P_NRITLANCROP    IN VARCHAR2,
   P_CDCLIENTEAPGD  IN VARCHAR2,
   P_DTAPURACFATGD  IN DATE,
   P_CDSERVFILIAPGD IN VARCHAR2,
   P_DEDUCAOROP     IN VARCHAR2 DEFAULT 'N')
IS
   V_IDENDCFOP       VARCHAR2(01);
   V_CDGRUPOFISC     VARCHAR2(03);
   V_TIPOOPER        VARCHAR2(02);
   V_CDEMPRESA       VARCHAR2(02);
   V_CDINSCESTA      VARCHAR2(16);
   V_PAISORIG        VARCHAR2(04);
   V_ESTAORIG        VARCHAR2(02);
   V_MUNIORIG        VARCHAR2(04);
   V_PAISDEST        VARCHAR2(04);
   V_ESTADEST        VARCHAR2(02);
   V_MUNIDEST        VARCHAR2(04);
   V_NRALIQTEMP      VARCHAR2(14);
   V_NRLANCTONF      VARCHAR2(20);
   V_IDENDEMIS       VARCHAR2(01);
   V_IDTRIMPSTII     VARCHAR2(01);
   V_PARDEDTMP       NUMBER;
   V_PREDBCTMP       NUMBER;
   V_ALIQUTMP        NUMBER;
   V_TRATIMPST       VARCHAR2(01);
   V_PBCSUBTRIB      NUMBER;
   V_IDPMCBASECAL    VARCHAR2(01);
   V_CDIMPOSTRIB     VARCHAR2(02);
   V_IDVRIMPSTRIB    VARCHAR2(01);
   V_VRMAXVENDA      NUMBER;
   V_VRPEMAXVENDA    NUMBER;
   V_TAXABASECALC    NUMBER;
   V_TAXAALIQUOTA    NUMBER;
   V_TAXABCSUBSTR    NUMBER;
   V_BASECALCIT      NUMBER;
   V_PERDBCALC       NUMBER;
   V_IMPOSTOIT       NUMBER;
   V_BASECALCTO      NUMBER;
   V_BASEVLRMAX      NUMBER;
   V_CALCVLRMAX      NUMBER;
   V_IMPOSTOTAL      NUMBER;     
   V_NRSEQFATIMPROP  VARCHAR2(10);
   V_NRSEQRELACIT    VARCHAR2(10);
   V_IDUTPADRROP     VARCHAR2(1);   /*Filial padrao*/
   V_CDFILCENTPME    VARCHAR2(4);
   V_FILIALPAR       VARCHAR2(4);   /*Filial padrao*/
   V_IDUTPADRROPFIL  VARCHAR2(1);   /*Filial padrao*/
   V_IDFILIALAGRROP  CHAR;
   V_IDINTEREST      VARCHAR2(01);
   V_NRINSCSUFRAMA   VARCHAR2(09);
   V_QUANTREG        NUMBER;  
   V_NRSEQDEDUROP    NUMBER;
BEGIN
   V_TIPOOPER        := '01';
   V_NRSEQRELACIT    := '0';             
   V_NRSEQFATIMPROP  := P_NRITLANCROP;
   V_QUANTREG        := 0;
   BEGIN
      SELECT NVL(IDENDCFOPFIL,'I'), CDEMPRESA, CDINSCESTA
      INTO V_IDENDCFOP, V_CDEMPRESA, V_CDINSCESTA
      FROM FILIAL
      WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDENDCFOP  := 'I';
         V_CDEMPRESA  := NULL;
         V_CDINSCESTA := NULL;
   END;

   BEGIN
      SELECT IDUTPADRROP
      INTO V_IDUTPADRROP
      FROM PARAMGERAL G;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDUTPADRROP     := NULL;
      END;
   END;

   BEGIN
      SELECT IDUTPADRROPFIL, CDFILCENTPME
      INTO V_IDUTPADRROPFIL, V_CDFILCENTPME
      FROM PARFILIALROP P, FILIAL F
      WHERE
         F.CDFILIAL    = P_CDFILIAL    AND
         P.CDFILIAL(+) = F.CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDUTPADRROPFIL := NULL;
         V_CDFILCENTPME   := NULL;
      END;
   END;

   IF (V_IDUTPADRROP = 'S' OR V_IDUTPADRROPFIL = 'S') THEN
      V_FILIALPAR := 'P';
   ELSE
      IF V_CDFILCENTPME IS NULL THEN
         V_FILIALPAR := P_CDFILIAL;
      ELSE
         BEGIN
            SELECT IDFILIALAGRROP
            INTO V_IDFILIALAGRROP
            FROM PARFILIALROP
            WHERE CDFILIAL = V_CDFILCENTPME;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDFILIALAGRROP := 'N';
         END;
         IF V_IDFILIALAGRROP = 'S' THEN
            V_FILIALPAR := V_CDFILCENTPME;
         ELSE
            V_FILIALPAR := P_CDFILIAL;
         END IF;
      END IF;
   END IF;

   NOVO_CODIGO_NT('LANCNF/FILIAL'||P_CDFILIAL, V_NRLANCTONF);

   V_NRLANCTONF := SUBSTR(V_NRLANCTONF, 10, 10);

   DELETE INCITEMP                              
    WHERE NRINCITEMP = P_CDFILIAL||V_NRLANCTONF;

   FOR C_FATURA IN (SELECT CDFILIALAPGD, CDCLIENTEAPGD, CDSERVFILIAPGD, P.CDARVPROD, DTAPURACFATGD, NRAPURACGD,
                           CDPRODUTOAPGD, NREFETIVOSAPGD, VRPRECUNITAPGD, ROUND(VRPRECUNITAPGD * NREFETIVOSAPGD,2) AS VRFATUR,
                           CLI.NMFANTCLIE
                      FROM TRAB_APURAC_GD T, PRODUTO P, CLIENTE CLI
                     WHERE CDFILIALAPGD    = P_CDFILIAL
                       AND DTAPURACFATGD BETWEEN P_DATAINI AND P_DATAFIM
                       AND NRAPURACGD = P_NRAPURACGD
                       AND T.CDPRODUTOAPGD = P.CDPRODUTO
                       AND CDCLIENTEAPGD = P_CDCLIENTEAPGD
                       AND CDSERVFILIAPGD = P_CDSERVFILIAPGD
                       AND DTAPURACFATGD = P_DTAPURACFATGD
                       AND T.CDCLIENTEAPGD = CLI.CDCLIENTE) LOOP
      BEGIN
         SELECT NVL(IDENDEMISCLI, 'P')
         INTO V_IDENDEMIS
         FROM CLIENTE
         WHERE (CDCLIENTE = C_FATURA.CDCLIENTEAPGD);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            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  = V_CDEMPRESA
              AND CDINSCESTA = V_CDINSCESTA;
         Exception
            When No_Data_Found Then
               Null;
         END;
      ELSE
         BEGIN
            SELECT CDPAIS, SGESTADO, CDMUNICIPIO
            INTO V_PAISORIG, V_ESTAORIG, V_MUNIORIG
            FROM ENDEFILI
            WHERE (CDFILIAL     = C_FATURA.CDFILIALAPGD)
              AND (IDTPENDEFILI = V_IDENDCFOP);
         Exception
            When No_Data_Found Then
               Null;
         END;
      END IF;

      BEGIN
         SELECT CDPAIS, SGESTADO, CDMUNICIPIO
         INTO V_PAISDEST, V_ESTADEST, V_MUNIDEST
         FROM ENDECLIE
         WHERE (CDCLIENTE    = C_FATURA.CDCLIENTEAPGD) AND
               (IDTPENDECLIE = V_IDENDEMIS);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_PAISDEST := NULL;
            V_ESTADEST := NULL;
            V_MUNIDEST := NULL;
      END;

      V_IDINTEREST := 'N';
      IF (V_PAISDEST = V_PAISORIG) AND (V_ESTADEST <> V_ESTAORIG) THEN
         V_IDINTEREST := 'S';
      END IF;

      CONSULTA_GRUPOFISCAL('C', C_FATURA.CDCLIENTEAPGD, C_FATURA.CDFILIALAPGD, 'S', V_TIPOOPER, V_CDGRUPOFISC);

      CONSULTA_INCIDENCIA('S', V_CDEMPRESA, C_FATURA.CDARVPROD, V_CDINSCESTA, V_TIPOOPER, P_CDFILIAL, V_NRLANCTONF, V_CDGRUPOFISC, NULL, V_IDINTEREST);

      V_IMPOSTOTAL := 0;
      FOR C_INCIDENCIA IN (SELECT DISTINCT (CDIMPOTEMP) AS CDIMPOTEMP, CDFISCTEMP, IDTRATIMPTMP, IDUTMUNIORIG,
                                  IDCONSISTENT, IDIMPORECUP, IR.IDTRATIMPROP, IP.NMIMPOSTO
                           FROM INCITEMP IT, IMPOSTO IP, IMPOSTOROP IR
                           WHERE (IT.NRINCITEMP = P_CDFILIAL||V_NRLANCTONF) AND
                                 (IP.CDIMPOSTO  = IT.CDIMPOTEMP)            AND
                                 (IP.IDPRODNOTA = 'P')                      AND
                                 (IR.CDFILIAL   = V_FILIALPAR)              AND
                                 (CDIMPOTEMP    = IR.CDIMPOSTO)              AND
                                 (IDTPIMPOSTOROP = 'FE')
                           ORDER BY CDIMPOTEMP) LOOP

         IF C_INCIDENCIA.IDUTMUNIORIG = 'D' THEN
            /* CONSIDERA O MUNICIPIO DE DESTINO */
            CONSULTA_ALIQUOTA(C_INCIDENCIA.CDIMPOTEMP, 'S', V_CDEMPRESA, C_FATURA.CDARVPROD, V_CDINSCESTA, V_PAISORIG,
                              V_ESTAORIG, V_PAISDEST, V_ESTADEST, V_MUNIDEST, C_FATURA.VRFATUR, C_FATURA.DTAPURACFATGD, P_CDFILIAL, V_NRLANCTONF, V_CDGRUPOFISC, V_TIPOOPER );

         ELSE
           /* CONSIDERA O MUNICIPIO DE ORIGEM */
            CONSULTA_ALIQUOTA(C_INCIDENCIA.CDIMPOTEMP, 'S', V_CDEMPRESA, C_FATURA.CDARVPROD, V_CDINSCESTA, V_PAISORIG,
                              V_ESTAORIG, V_PAISDEST, V_ESTADEST, V_MUNIORIG, C_FATURA.VRFATUR, C_FATURA.DTAPURACFATGD, P_CDFILIAL, V_NRLANCTONF, V_CDGRUPOFISC, V_TIPOOPER);
         END IF;

         V_NRALIQTEMP := 0;
         V_PARDEDTMP  := 0;
         V_PREDBCTMP  := 0;
         V_ALIQUTMP   := 0;
         V_IMPOSTOIT  := 0;

         BEGIN
            SELECT NVL(MAX(NRALIQTEMP),0), NVL(MAX(VRPARDEDTMP),0), NVL(MAX(VRPREDBCTMP),0), NVL(MAX(VRALIQUTMP),0),
                   NVL(MAX(IDTRIMPSTTMP), '0'), NVL(MAX(VRPBCSTTMP),0), NVL(MAX(IDPMCBSCALTMP), 'S')
            INTO V_NRALIQTEMP, V_PARDEDTMP, V_PREDBCTMP, V_ALIQUTMP, V_TRATIMPST, V_PBCSUBTRIB, V_IDPMCBASECAL
            FROM ALIQTEMP
            WHERE (NRALIQTEMP = P_CDFILIAL||V_NRLANCTONF);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRALIQTEMP   := 0;
               V_PARDEDTMP    := 0;
               V_PREDBCTMP    := 0;
               V_ALIQUTMP     := 0;
               V_TRATIMPST    := 0;
               V_PBCSUBTRIB   := 0;
               V_IDPMCBASECAL := 0;
         END;

         BEGIN
            SELECT CDIMPOSTO, IDVRIMPSTRIB
            INTO V_CDIMPOSTRIB, V_IDVRIMPSTRIB
            FROM IMPOSTO
            WHERE CDIMPOSTRIB = C_INCIDENCIA.CDIMPOTEMP;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDIMPOSTRIB  := NULL;
               V_IDVRIMPSTRIB := NULL;
         END;

         V_TAXABASECALC := (100 - V_PREDBCTMP)/100;
         V_TAXAALIQUOTA := (V_ALIQUTMP/100);
         V_TAXABCSUBSTR := 1 + (V_PBCSUBTRIB/100);
         V_BASECALCIT   := C_FATURA.VRFATUR * V_TAXABASECALC;

         V_PERDBCALC    := 100 - V_PREDBCTMP;

         IF V_PBCSUBTRIB > 0 THEN
            V_BASECALCIT := (V_BASECALCIT * V_TAXABCSUBSTR);
            V_PREDBCTMP  := V_PBCSUBTRIB;
            V_PERDBCALC  := 0;
         END IF;

         V_BASEVLRMAX   := 0;
         V_VRPEMAXVENDA := 0;
         V_VRMAXVENDA   := 0;
         V_CALCVLRMAX   := 0;

         SELECT MAX(NRINSCSUFRAMACL) /*Verifica se ha Inscricao Suframa cadastrado p o cliente*/
              INTO V_NRINSCSUFRAMA
              FROM CLIENTE
             WHERE (CDCLIENTE = C_FATURA.CDCLIENTEAPGD);

         IF V_NRINSCSUFRAMA IS NULL THEN
            BEGIN
               SELECT NVL(MAX(VRMAXVENDA),0), NVL(MAX(VRPEMAXVENDA),0)
               INTO V_VRMAXVENDA, V_VRPEMAXVENDA
               FROM VRMAXVENPROD A
               WHERE (CDPAIS   = V_PAISDEST)          AND
                     (SGESTADO = V_ESTADEST)          AND
                     (A.CDPRODUTO = C_FATURA.CDPRODUTOAPGD);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_VRMAXVENDA   := 0;
                  V_VRPEMAXVENDA := 0;
            END;
         ELSE
            BEGIN
               SELECT NVL(MAX(VRMAXVENDA),0), NVL(MAX(VRPEMAXVENDA),0)
               INTO V_VRMAXVENDA, V_VRPEMAXVENDA
               FROM VRMAXVENPROD A
               WHERE (CDPAIS   = '0055')          AND
                     (SGESTADO = 'SF')          AND
                     (A.CDPRODUTO = C_FATURA.CDPRODUTOAPGD);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_VRMAXVENDA   := 0;
                  V_VRPEMAXVENDA := 0;
            END;
         END IF;

         IF V_VRMAXVENDA IS NOT NULL THEN
            V_CALCVLRMAX := (C_FATURA.NREFETIVOSAPGD * V_VRMAXVENDA) * (V_VRPEMAXVENDA/100);
            V_BASEVLRMAX := V_CALCVLRMAX;
         END IF;

         IF V_CDIMPOSTRIB IS NULL THEN
            V_BASECALCTO  := V_BASECALCIT;
            V_IMPOSTOIT   := (V_BASECALCIT * V_TAXAALIQUOTA) - V_PARDEDTMP;
          ELSE
            /*Se o parametro de "Utiliza PMC" estiver marcado eu comparo o percentual de majoracao com o valor maximo
            de venda e o maior entra na base de calculo. Se o parametro estiver desmarcado sempre sera utilizado o percentual de
            majoracao */
            IF NVL(V_IDPMCBASECAL, 'S') = 'S' THEN
               IF V_BASECALCIT > V_BASEVLRMAX THEN
                  V_BASECALCTO  := V_BASECALCIT;
                  V_IMPOSTOIT   := (V_BASECALCIT * V_TAXAALIQUOTA) - V_PARDEDTMP;
               ELSE
                  V_BASECALCTO  := V_BASEVLRMAX;
                  V_IMPOSTOIT   := (V_BASEVLRMAX * V_TAXAALIQUOTA) - V_PARDEDTMP;
               END IF;
            ELSE
               V_BASECALCTO  := V_BASECALCIT;
               V_IMPOSTOIT   := (V_BASECALCIT * V_TAXAALIQUOTA) - V_PARDEDTMP;
            END IF;
         END IF;

         IF V_CDIMPOSTRIB IS NULL THEN
            V_IDTRIMPSTII := '';
         ELSE
            V_IDTRIMPSTII := V_TRATIMPST;
         END IF;

         IF (V_IMPOSTOIT < 0) THEN
            V_IMPOSTOIT := 0;
         END IF;

         IF (V_ALIQUTMP = 0) AND (V_IMPOSTOIT = 0) THEN
            V_BASECALCTO := 0;
         END IF;

         IF C_INCIDENCIA.IDTRATIMPROP = 'D' THEN
            V_IMPOSTOTAL := - ROUND(V_IMPOSTOIT,2);
         ELSE
            V_IMPOSTOTAL := ROUND(V_IMPOSTOIT,2);
         END IF;

         DELETE ALIQTEMP
         WHERE NRALIQTEMP = P_CDFILIAL||V_NRLANCTONF;

         IF P_DEDUCAOROP = 'N' THEN
            V_NRSEQRELACIT := V_NRSEQRELACIT + 1;

            INSERT INTO ITRELACITROP
               (CDFILIAL,  NRROPFILIAL, NRLANCROP, NRITLANCROP, NRSEQRELACITROP,
                CDITRELITROP, QTITRELITROP, VRITRELITROP)
            VALUES
               (P_CDFILIAL,  P_NRROPFIL, P_NRLANCROP, P_NRITLANCROP, V_NRSEQRELACIT,
                C_INCIDENCIA.CDIMPOTEMP, 0, DECODE(C_INCIDENCIA.IDTRATIMPROP, 'D',-1,1) * V_IMPOSTOTAL);
         ELSE
            NOVO_CODIGO('GESDEDUCAOROP', V_NRSEQDEDUROP); 

            INSERT INTO DEDUCAOROP
                 (NRFILIALROP, NRDEDUCAOROP, CDFILIAL, DTREFERENC, NRNOTAFISC,
                  CDIMPOSTO, CDENCARGO, DSDEDUCAO, CDCLIENTE, CDFILIALBENE,
                  CDFORNECED, DSBENEFICIARIO, CDFILIALLANC, NRLANCTONF, NRRECEXTRAFILI,
                  IDTPLANCROP, CDFILIALCENT, VRDEDUCAO, VRPERFIXCALCIMP,CDSERVFILI)
            VALUES
                 (P_NRROPFIL, V_NRSEQDEDUROP, P_CDFILIAL, P_DATAINI, NULL,
                  C_INCIDENCIA.CDIMPOTEMP, NULL, C_INCIDENCIA.NMIMPOSTO, C_FATURA.CDCLIENTEAPGD, NULL,
                  NULL, C_FATURA.NMFANTCLIE, NULL, NULL, NULL,
                  'FAT_EFETIVO', V_CDFILCENTPME, DECODE(C_INCIDENCIA.IDTRATIMPROP, 'D',-1,1) * V_IMPOSTOTAL , NULL,C_FATURA.CDSERVFILIAPGD);
         END IF;
      END LOOP;

      DELETE INCITEMP
       WHERE NRINCITEMP = P_CDFILIAL||V_NRLANCTONF;
   END LOOP;
END;