CREATE OR REPLACE PROCEDURE CALC_ESTORNO_RET(
   P_NRSEQUMAPAPU    IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDEMPRESA       IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDIMPOSTO       IN MAPAAPURAC.CDIMPOSTO%TYPE,
   P_INSCRICAO       IN MAPAAPURAC.CDINSCESTA%TYPE)
AS
   V_CDAJUSTEDEVOL     VARCHAR2(20);
   V_DSAJUSTEESTDEVOL  VARCHAR2(250);
   V_CDAJUSTEDEVOLEST  VARCHAR2(20);
   V_DSAJUSTEDEVOLEST  VARCHAR2(250);
   V_NRSEQITMAPR       VARCHAR2(10);
   V_IDSOMAFECP        VARCHAR(01);
   V_NOTAFISCAL        VARCHAR2(17);
   V_VRESTORNO         FLOAT;
   V_VRALIQUOTACP      FLOAT;
   V_VRCREDPRES        NUMBER;
   V_STDESCRICAO       VARCHAR2(500);
   V_NRSEQDETITMAPA    NUMBER;
   V_IDESTSUBAPUR      VARCHAR2(01);
   V_IDESTSUBAPUREST   VARCHAR2(01);
   V_DESCRET           VARCHAR2(500);
   V_CDAJUSTEDEVOLIMPO VARCHAR2(20);
   V_DSAJESTDEVOLIMPO  VARCHAR2(250);
   V_CDAJDEVOLESTIMPO  VARCHAR2(20);
   V_DSAJDEVOLESTIMPO  VARCHAR2(250);
   V_IDESTAPURCREDIMPO VARCHAR2(01);
   V_CDAJUSTE          VARCHAR2(20);
   V_IDTIPOAPUR        VARCHAR2(01);
   V_IDESTSUBAPURIMPO    VARCHAR2(01);
   V_IDESTSUBAPURESTIMPO VARCHAR2(01);
   V_IDESTORNO         VARCHAR2(01);
   V_NOTAFISCAL_IMP    VARCHAR2(17);
   V_IDINDICSUBAPURCP  VARCHAR2(01);
   V_IDINDSUBAPURIMP   VARCHAR2(01);     
   V_DSOUTRAAPURCP     VARCHAR2(250);
   V_DSOUTAPURCPIMPO   VARCHAR2(250);
BEGIN
   V_VRESTORNO  := 0;
   BEGIN
      SELECT IDSOMAFECP
        INTO V_IDSOMAFECP
        FROM AJUSTEFECP
       WHERE CDEMPRESA    = P_CDEMPRESA
         AND CDINSCESTA   = P_INSCRICAO
         AND CDIMPOSTO    = P_CDIMPOSTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDSOMAFECP := 'S';
   END;
   V_IDSOMAFECP := NVL(V_IDSOMAFECP,'S');

   /******** Busca a parametrização para a geração do estorno da inscrição estadual do mapa ********/
   BEGIN
      SELECT CDAJUSTEDEVOL, DSAJUSTEESTDEVOL, CDAJUSTEDEVOLEST , DSAJUSTEDEVOLEST,
             CDAJUSTEDEVOLIMPO, DSAJESTDEVOLIMPO, CDAJDEVOLESTIMPO, DSAJDEVOLESTIMPO,
             IDESTAPURCREDIMPO, IDINDICSUBAPURCP, IDINDSUBAPURIMP, DSOUTRAAPURCP, DSOUTAPURCPIMPO
        INTO V_CDAJUSTEDEVOL, V_DSAJUSTEESTDEVOL, V_CDAJUSTEDEVOLEST, V_DSAJUSTEDEVOLEST,
             V_CDAJUSTEDEVOLIMPO, V_DSAJESTDEVOLIMPO, V_CDAJDEVOLESTIMPO, V_DSAJDEVOLESTIMPO,
             V_IDESTAPURCREDIMPO, V_IDINDICSUBAPURCP, V_IDINDSUBAPURIMP, V_DSOUTRAAPURCP, V_DSOUTAPURCPIMPO
        FROM INSCRESTAD
       WHERE CDEMPRESA    = P_CDEMPRESA
         AND CDINSCESTA   = P_INSCRICAO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDAJUSTEDEVOL    := NULL;
         V_DSAJUSTEESTDEVOL := NULL;
         V_CDAJUSTEDEVOLEST := NULL;
         V_DSAJUSTEDEVOLEST := NULL;
         V_CDAJUSTEDEVOLIMPO:= NULL;
         V_DSAJESTDEVOLIMPO := NULL;
         V_CDAJDEVOLESTIMPO := NULL;
         V_DSAJDEVOLESTIMPO := NULL;
         V_IDESTAPURCREDIMPO:= 'N';
         V_IDINDICSUBAPURCP := NULL;
         V_IDINDSUBAPURIMP  := NULL;
         V_DSOUTRAAPURCP    := NULL;
         V_DSOUTAPURCPIMPO  := NULL;
   END;
   /*MERCADORIA NACIONAL*/
   V_IDESTSUBAPUR    := CASE WHEN SUBSTR(V_CDAJUSTEDEVOL,   4,1) IN ('3','4','5','6','7','8') THEN 'S' ELSE 'N' END;
   V_IDESTSUBAPUREST := CASE WHEN SUBSTR(V_CDAJUSTEDEVOLEST,4,1) IN ('3','4','5','6','7','8') THEN 'S' ELSE 'N' END;
    /*MERCADORIA IMPORTADA*/
   V_IDESTSUBAPURIMPO    := CASE WHEN SUBSTR(V_CDAJUSTEDEVOLIMPO,4,1) IN ('3','4','5','6','7','8') THEN 'S' ELSE 'N' END;
   V_IDESTSUBAPURESTIMPO := CASE WHEN SUBSTR(V_CDAJDEVOLESTIMPO,4,1)  IN ('3','4','5','6','7','8') THEN 'S' ELSE 'N' END;

   BEGIN
      SELECT NVL(MAX(NRSEQITMAPR),0) AS NRSEQITMAPR
        INTO V_NRSEQITMAPR
        FROM ITMAPARECUP
       WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
         AND IDTIPOITMAPR = 'EC';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQITMAPR := NULL;
   END;

   FOR C_DEVOLUCOES IN (SELECT NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT + DECODE(V_IDSOMAFECP,'S',NVL(IT.VRADICFECP,0),0),0),2)),0.0) AS VRCREDDEV,
                               NVL(SUM(NVL(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),2),0.0) +
                               NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '00', 'C','','2'),0.0)),0.0) AS VRTOTALSAIDA,
                               N.CDFILIAL, N.NRLANCTONF, IP.NRSEQITPROD, IP.CDPRODUTO, P.CDARVPROD,
                               N.NRNOTAFISC, DECODE(SUBSTR(C.CDCFOPPADRAO,1,1),'1',CP.VRALIQINTCONT,CP.VRALIQINTECONT) AS VRALIQUOTACP,
                               CP.IDPRODIMPORTADO
                          FROM NOTAFISCAL N, IMPOSTO IM, CFOPPADRAO C, ITEMPROIMPOS IT, PRODUTO P, ITEMPRODUTO IP, CREDPRESPRODUTO CP,
                               (SELECT DISTINCT CDFILIAL, NRLANCTONF, CDIMPOSTO
                                  FROM IMPOSTONF I
                                 WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                                   AND I.IDIMPOSTONF  <> '3') I
                         WHERE I.CDFILIAL      = N.CDFILIAL
                           AND I.NRLANCTONF    = N.NRLANCTONF
                           AND IT.CDIMPOSTO    = I.CDIMPOSTO
                           AND IT.CDFILIAL     = I.CDFILIAL
                           AND IT.NRLANCTONF   = I.NRLANCTONF
                           AND IT.CDFILIAL     = IP.CDFILIAL
                           AND IT.NRLANCTONF   = IP.NRLANCTONF
                           AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                           AND P.CDPRODUTO     = IP.CDPRODUTO
                           AND N.IDENTRSAIDOP  = 'E'
                           AND I.CDIMPOSTO     = IM.CDIMPOSTO
                           AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                           AND IT.CDFISCOPERIT = C.CDCFOPPADRAO
                           AND NVL(C.IDTIPOCFOP,'T')  = 'D'
   			   AND IT.CDFISCALIT   = '1'
                           AND CP.CDARVPROD    = (SELECT MAX(PC.CDARVPROD)
                                                    FROM PRODUTO PC, CREDPRESPRODUTO C
                                                   WHERE PC.CDPRODUTO = C.CDPRODUTO
                                                     AND C.CDEMPRESA  = P_CDEMPRESA
                                                     AND C.CDINSCESTA = P_INSCRICAO
                                                     AND INSTR(P.CDARVPROD, C.CDARVPROD) = 1)
                           AND NVL(CP.VRALIQINTCONT,0) + NVL(CP.VRALIQINTECONT,0) + NVL(CP.VRALIQINTNCONT,0) + NVL(CP.VRALIQINTENCONT,0) > 0
                           AND CP.CDEMPRESA    = P_CDEMPRESA
                           AND CP.CDINSCESTA   = P_INSCRICAO
                         GROUP BY N.CDFILIAL, N.NRLANCTONF, IP.NRSEQITPROD, IP.CDPRODUTO, P.CDARVPROD, N.NRNOTAFISC,
                               SUBSTR(C.CDCFOPPADRAO,1,1),CP.VRALIQINTCONT, CP.VRALIQINTECONT, CP.IDPRODIMPORTADO
                         ORDER BY N.CDFILIAL, N.NRLANCTONF, IP.NRSEQITPROD, CP.IDPRODIMPORTADO ) LOOP

      V_VRESTORNO := ROUND(C_DEVOLUCOES.VRTOTALSAIDA * 0.01 * NVL(C_DEVOLUCOES.VRALIQUOTACP,0),2);
      IF V_VRESTORNO > 0 THEN
         IF NVL(C_DEVOLUCOES.IDPRODIMPORTADO,'N') = 'N' THEN
            V_STDESCRICAO := NVL(V_DSAJUSTEESTDEVOL,'ESTORNO NF DEV: [DOC]');
            V_CDAJUSTE := V_CDAJUSTEDEVOL;
            V_IDTIPOAPUR := 'N'; /*APURACAO MERCADORIA NACIONAL*/
            V_IDESTORNO := V_IDESTSUBAPUR;
         ELSE
            V_STDESCRICAO := NVL(V_DSAJESTDEVOLIMPO,'ESTORNO NF DEV: [DOC]');
            V_CDAJUSTE := V_CDAJUSTEDEVOLIMPO;
            V_IDTIPOAPUR := 'I'; /*APURACAO MERCADORIA IMPORTADA*/
            V_IDESTORNO := V_IDESTSUBAPURIMPO;
         END IF;

         MONTA_DSAJUSTE_MAPA(C_DEVOLUCOES.CDFILIAL, C_DEVOLUCOES.NRLANCTONF, C_DEVOLUCOES.CDPRODUTO, V_STDESCRICAO, V_DESCRET);

         IF NVL(C_DEVOLUCOES.IDPRODIMPORTADO,'N') = 'N' THEN
            IF (V_NOTAFISCAL IS NULL) OR (V_NOTAFISCAL <> C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'RET') THEN
                V_NRSEQITMAPR := LPAD(TO_NUMBER(NVL(V_NRSEQITMAPR,0)) + 1,10,'0');
               INSERT INTO ITMAPARECUP
                  (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDITEMOCOR, DSITEMOCOR,
                   DSFLEGALOCOR, NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, CDAJUSTEITSEF, CDITPROCEDENCIA,
                   IDCREDPRESUMIDO, IDDIFALMANUAL, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTSUBAPUR, IDINDSUBAPURCP, DSOUTRASUBAPUR)
               VALUES
                  (P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, V_DESCRET, V_VRESTORNO, '', '',
                   '', C_DEVOLUCOES.NRLANCTONF,C_DEVOLUCOES.CDFILIAL, V_CDAJUSTE, NULL, NULL,
                   'S', 'S', 'N', 'I', V_IDESTORNO, V_IDINDICSUBAPURCP, V_DSOUTRAAPURCP);
               V_NOTAFISCAL := C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'RET';
            ELSE
               UPDATE ITMAPARECUP
                  SET VRITEMMAPR = NVL(VRITEMMAPR,0) + V_VRESTORNO
                WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
                  AND IDTIPOITMAPR = 'EC'
                  AND NRSEQITMAPR  = V_NRSEQITMAPR;
            END IF;
         ELSE
            IF (V_NOTAFISCAL_IMP IS NULL) OR (V_NOTAFISCAL_IMP <> C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'RET') THEN
                V_NRSEQITMAPR := LPAD(TO_NUMBER(NVL(V_NRSEQITMAPR,0)) + 1,10,'0');
               INSERT INTO ITMAPARECUP
                  (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDITEMOCOR, DSITEMOCOR,
                   DSFLEGALOCOR, NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, CDAJUSTEITSEF, CDITPROCEDENCIA,
                   IDCREDPRESUMIDO, IDDIFALMANUAL, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTSUBAPUR, IDINDSUBAPURCP, DSOUTRASUBAPUR)
               VALUES
                  (P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, V_DESCRET, V_VRESTORNO, '', '',
                   '', C_DEVOLUCOES.NRLANCTONF,C_DEVOLUCOES.CDFILIAL, V_CDAJUSTE, NULL, NULL,
                   'S', 'S', 'N', 'I', V_IDESTORNO, V_IDINDSUBAPURIMP, V_DSOUTAPURCPIMPO);
               V_NOTAFISCAL_IMP := C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'RET';
            ELSE
               UPDATE ITMAPARECUP
                  SET VRITEMMAPR = NVL(VRITEMMAPR,0) + V_VRESTORNO
                WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
                  AND IDTIPOITMAPR = 'EC'
                  AND NRSEQITMAPR  = V_NRSEQITMAPR;
            END IF;
         END IF;
         NOVO_CODIGO_NT('DETITMAPARECUP',V_NRSEQDETITMAPA);
         INSERT INTO DETITMAPARECUP
           (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, CDFILIAL, NRLANCTONF,
            NRSEQITPROD, NRITPROIMPOS, CDIMPOSTO, VRIMPOSTOIT, VROUTALIQ, VROUTBASE, DSITEMMAPR, IDTPSUBAPURCP)
         VALUES
           (V_NRSEQDETITMAPA, P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, C_DEVOLUCOES.CDFILIAL,C_DEVOLUCOES.NRLANCTONF,
            C_DEVOLUCOES.NRSEQITPROD, '1', '00', V_VRESTORNO, NVL(V_VRALIQUOTACP,0), C_DEVOLUCOES.VRTOTALSAIDA, V_DESCRET, V_IDTIPOAPUR);
      END IF;

      V_VRESTORNO  := ROUND(C_DEVOLUCOES.VRCREDDEV - NVL(V_VRESTORNO,0),2);
      IF V_VRESTORNO > 0 THEN
         IF NVL(C_DEVOLUCOES.IDPRODIMPORTADO,'N') = 'N' THEN
            V_STDESCRICAO := NVL(V_DSAJUSTEDEVOLEST,'ESTORNO DO ESTORNO NF DEV: [DOC]');
            V_CDAJUSTE := V_CDAJUSTEDEVOLEST;
            V_IDTIPOAPUR := 'N'; /*APURACAO MERCADORIA NACIONAL*/
            V_IDESTORNO := V_IDESTSUBAPUREST;
         ELSE
            V_STDESCRICAO := NVL(V_DSAJDEVOLESTIMPO,'ESTORNO DO ESTORNO NF DEV: [DOC]');
            V_CDAJUSTE := V_CDAJDEVOLESTIMPO;
            V_IDTIPOAPUR := 'I'; /*APURACAO MERCADORIA IMPORTADA*/
            V_IDESTORNO := V_IDESTSUBAPURESTIMPO;
         END IF;

         MONTA_DSAJUSTE_MAPA(C_DEVOLUCOES.CDFILIAL, C_DEVOLUCOES.NRLANCTONF, C_DEVOLUCOES.CDPRODUTO, V_STDESCRICAO, V_DESCRET);

         IF NVL(C_DEVOLUCOES.IDPRODIMPORTADO,'N') = 'N' THEN
            IF (V_NOTAFISCAL IS NULL) OR (V_NOTAFISCAL <> C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'EST') THEN
                V_NRSEQITMAPR := LPAD(TO_NUMBER(NVL(V_NRSEQITMAPR,0)) + 1,10,'0');
               INSERT INTO ITMAPARECUP
                  (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDITEMOCOR, DSITEMOCOR,
                   DSFLEGALOCOR, NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, CDAJUSTEITSEF, CDITPROCEDENCIA,
                   IDCREDPRESUMIDO, IDDIFALMANUAL, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTSUBAPUR, IDINDSUBAPURCP, DSOUTRASUBAPUR)
               VALUES
                  (P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, V_DESCRET, V_VRESTORNO, '', '',
                   '', C_DEVOLUCOES.NRLANCTONF,C_DEVOLUCOES.CDFILIAL, V_CDAJUSTE, NULL, NULL,
                   'S', 'S', 'N', 'I', V_IDESTORNO, V_IDINDICSUBAPURCP, V_DSOUTRAAPURCP);
               V_NOTAFISCAL := C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'EST';
            ELSE
               UPDATE ITMAPARECUP
                  SET VRITEMMAPR = NVL(VRITEMMAPR,0) + V_VRESTORNO
                WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
                  AND IDTIPOITMAPR = 'EC'
                  AND NRSEQITMAPR  = V_NRSEQITMAPR;
            END IF;
         ELSE
            IF (V_NOTAFISCAL_IMP IS NULL) OR (V_NOTAFISCAL_IMP <> C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'EST') THEN
                V_NRSEQITMAPR := LPAD(TO_NUMBER(NVL(V_NRSEQITMAPR,0)) + 1,10,'0');
               INSERT INTO ITMAPARECUP
                  (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDITEMOCOR, DSITEMOCOR,
                   DSFLEGALOCOR, NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, CDAJUSTEITSEF, CDITPROCEDENCIA,
                   IDCREDPRESUMIDO, IDDIFALMANUAL, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTSUBAPUR, IDINDSUBAPURCP, DSOUTRASUBAPUR)
               VALUES
                  (P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, V_DESCRET, V_VRESTORNO, '', '',
                   '', C_DEVOLUCOES.NRLANCTONF,C_DEVOLUCOES.CDFILIAL, V_CDAJUSTE, NULL, NULL,
                   'S', 'S', 'N', 'I', V_IDESTORNO, V_IDINDSUBAPURIMP, V_DSOUTAPURCPIMPO);
               V_NOTAFISCAL_IMP := C_DEVOLUCOES.CDFILIAL||C_DEVOLUCOES.NRLANCTONF||'EST';
            ELSE
               UPDATE ITMAPARECUP
                  SET VRITEMMAPR = NVL(VRITEMMAPR,0) + V_VRESTORNO
                WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
                  AND IDTIPOITMAPR = 'EC'
                  AND NRSEQITMAPR  = V_NRSEQITMAPR;
            END IF;
         END IF;

         NOVO_CODIGO_NT('DETITMAPARECUP',V_NRSEQDETITMAPA);
         INSERT INTO DETITMAPARECUP
           (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, CDFILIAL, NRLANCTONF,
            NRSEQITPROD, NRITPROIMPOS, CDIMPOSTO, VRIMPOSTOIT, VROUTBASE, DSITEMMAPR, IDTPSUBAPURCP)
         VALUES
           (V_NRSEQDETITMAPA, P_NRSEQUMAPAPU, 'EC', V_NRSEQITMAPR, C_DEVOLUCOES.CDFILIAL,C_DEVOLUCOES.NRLANCTONF,
            C_DEVOLUCOES.NRSEQITPROD, '1', '00', V_VRESTORNO,C_DEVOLUCOES.VRTOTALSAIDA, V_DESCRET, V_IDTIPOAPUR);
      END IF;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CALC_ESTORNO_RET','6.49.020'); END;
/
