CREATE OR REPLACE FUNCTION ADRC_ST_NAO_CENTRALIZADOR(
   P_CDEMPRESA        IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA       IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL        IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL          IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG      IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_IDCENTRDISTR     IN VARCHAR2,
   P_CONTREG          IN NUMBER) RETURN NUMBER
AS
   V_CONTREG          NUMBER;
   V_NRSEQUMAPAPU     VARCHAR2(10);
   V_CDICMS           VARCHAR2(02);
   V_QTDEENTR         FLOAT;
   V_CDPRODUTO        VARCHAR2(20);
   V_VRFATCONV        FLOAT;
   V_QTDDEVENTR       FLOAT;
   V_SEQPROD          NUMBER(3);
   V_QTDESAID         FLOAT;
   V_QTDDEVSAID       FLOAT;
   V_ALIQ_ICMS_ITEM   FLOAT;
   V_ALIQ_ICMS        FLOAT;
   V_ALIQ_FECOP       FLOAT;
   V_QTD_LIN_BLC1     NUMBER;

   /*variaveis para geracao do registro 1100*/
   V_QTD_TOT_ENTRADA                FLOAT;
   V_MENOR_VL_UNIT_ITEM             FLOAT;
   V_VL_BC_ICMSST_UNIT_MED          FLOAT;
   V_VL_TOT_ICMS_SUPORT_ENTR        FLOAT;
   V_VL_UNIT_MED_ICMS_SUPORT_ENTR   FLOAT;
   V_REG1100                        VARCHAR2(100);

   /*Variaveis para geracao dos registros de saida, 1200, 1300, 1400, 1500*/
   V_QTD_TOT_SAIDA_REG              FLOAT;
   V_VL_TOT_ICMS_EFETIVO            FLOAT;
   V_VL_CONFRONTO_ICMS_ENTRADA      FLOAT;
   V_RESULT_RECUPERAR_RESSARCIR     FLOAT;
   V_RESULT_COMPLEMENTAR            FLOAT;
   V_APUR_ICMSST_RECUPERAR_RESSAR   FLOAT;
   V_APUR_ICMSST_COMPLEMENTAR       FLOAT;
   V_APUR_FECOP_RESSARCIR           FLOAT;
   V_APUR_FECOP_COMPLEMENTAR        FLOAT;
   V_VL_ICMSST_UNIT_ENTR            FLOAT;
   V_REG1200                        VARCHAR2(150);
   V_REG1300                        VARCHAR2(100);
   V_REG1400                        VARCHAR2(70);
   V_REG1500                        VARCHAR2(50);
   V_QTD_TOT_SAIDA                  FLOAT;

   /*Variaveis do Registro Totalizado, Bloco 9*/
   V_REG9000                        VARCHAR2(100);
   V_APUR_ICMSST_RECUPERAR_1200     FLOAT;
   V_APUR_ICMSST_COMPL_1200         FLOAT;
   V_APUR_FECOP_RESSARCIR_1200      FLOAT;
   V_APUR_FECOP_COMPLEMENTAR_1200   FLOAT;
   V_APUR_ICMSST_RECUPERAR_1300     FLOAT;
   V_APUR_FECOP_RESSARCIR_1300      FLOAT;
   V_APUR_ICMSST_RECUPERAR_1400     FLOAT;
   V_APUR_ICMSST_RECUPERAR_1500     FLOAT;
BEGIN
   V_CONTREG      := NVL(P_CONTREG,0);
   V_QTD_LIN_BLC1 := 0;
   V_CDICMS       := '00';

   BEGIN
      SELECT M.NRSEQUMAPAPU
        INTO V_NRSEQUMAPAPU
        FROM MAPAAPURAC M
       WHERE M.CDIMPOSTO    = V_CDICMS
         AND M.CDEMPRESA    = P_CDEMPRESA
         AND M.CDINSCESTA   = P_CDINSCESTA
         AND M.DTINIPERIODO = P_DTINICIAL
         AND M.DTFIMPERIODO = P_DTFINAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQUMAPAPU := NULL;
   END;

   V_APUR_ICMSST_RECUPERAR_1200   := 0;
   V_APUR_ICMSST_COMPL_1200       := 0;
   V_APUR_FECOP_RESSARCIR_1200    := 0;
   V_APUR_FECOP_COMPLEMENTAR_1200 := 0;
   V_APUR_ICMSST_RECUPERAR_1300   := 0;
   V_APUR_FECOP_RESSARCIR_1300    := 0;
   V_APUR_ICMSST_RECUPERAR_1400   := 0;
   V_APUR_ICMSST_RECUPERAR_1500   := 0;

   /*Verifica os produtos que serao gerados no arquivo*/
   FOR C_PROD IN (SELECT CDPRODUTO, SGUNIDADE, SUM(QTDE) AS QTDE
                    FROM (SELECT NVL(PE.CDPRODUTO,PR.CDPRODUTO) AS CDPRODUTO, ROUND(IT.QTPRODVEND*NVL(PR.VRFATOCONV,1),3) AS QTDE,
                                 NVL(PE.SGUNIDADE,PR.SGUNIDADE) AS SGUNIDADE
                            FROM ITVENDAIMPOS II, ITEMVENDA IT, VENDA VE, PRODUTO PR, PRODUTO PE, (SELECT R.CDFILIALVENDA, R.CDCAIXA, R.NRSEQVENDA
                                                                                                     FROM RELACNFCEAPROV R, IMPOSTONF I
                                                                                                    WHERE R.CDFILIALNOTA = I.CDFILIAL
                                                                                                      AND R.NRLANCTONF   = I.NRLANCTONF
                                                                                                      AND I.NRSEQUMAPAPU = V_NRSEQUMAPAPU) RV
                           WHERE VE.CDFILIAL        = RV.CDFILIALVENDA
                             AND VE.CDCAIXA         = RV.CDCAIXA
                             AND VE.NRSEQVENDA      = RV.NRSEQVENDA
                             AND VE.IDSITUVENDA     = 'O'
                             AND VE.IDTPAMBNFCE     = '1'
                             AND VE.IDSTATUSNFCE    = 'A'
                             AND VE.CDFILIAL        = IT.CDFILIAL
                             AND VE.NRSEQVENDA      = IT.NRSEQVENDA
                             AND VE.CDCAIXA         = IT.CDCAIXA
                             AND IT.CDFILIAL        = II.CDFILIAL
                             AND IT.CDCAIXA         = II.CDCAIXA
                             AND IT.NRSEQUITVEND    = II.NRSEQUITVEND
                             AND IT.NRSEQVENDA      = II.NRSEQVENDA
                             AND II.CDCFOPPROD      = '5405'
                             AND VE.IDAPROVACAONFCE = 'A'
                             AND VE.IDTPEMISVEND    IN ('N','S')
                             AND IT.CDPRODUTO       = PR.CDPRODUTO
                             AND PR.CDPRODESTO      = PE.CDPRODUTO(+)
                           UNION
                          SELECT NVL(PE.CDPRODUTO,PR.CDPRODUTO) AS CDPRODUTO, CASE WHEN NF.IDENTRSAIDOP = 'S'
                                                                                   THEN ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)
                                                                                   WHEN NF.IDENTRSAIDOP = 'E' AND NVL(CP.IDTIPOCFOP,'N') = 'D'
                                                                                   THEN (-1)*ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)
                                                                                   ELSE 0 END AS QTDE,
                                 NVL(PE.SGUNIDADE,PR.SGUNIDADE) AS SGUNIDADE
                            FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, PRODUTO PE, CFOPPADRAO CP,
                                 (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                    FROM IMPOSTONF I
                                   WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                           WHERE IT.CDFILIAL     = II.CDFILIAL
                             AND IT.NRLANCTONF   = II.NRLANCTONF
                             AND IT.CDFILIAL     = IP.CDFILIAL
                             AND IT.NRLANCTONF   = IP.NRLANCTONF
                             AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                             AND IT.CDIMPOSTO    = '00'
                             AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                             AND IT.CDFILIAL     = NF.CDFILIAL
                             AND IT.NRLANCTONF   = NF.NRLANCTONF
                             AND IP.CDPRODUTO    = PR.CDPRODUTO
                             AND PR.CDPRODESTO   = PE.CDPRODUTO(+)
                             AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                             AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),2,1) = '4' /*CFOP de Substituicao Tributaria*/)
                   WHERE QTDE <> 0
                   GROUP BY CDPRODUTO, SGUNIDADE) LOOP

      V_SEQPROD    := NVL(V_SEQPROD,0)+1;
      V_QTDEENTR   := 0;
      V_QTDDEVENTR := 0;

      V_QTD_TOT_ENTRADA              := 0;
      V_MENOR_VL_UNIT_ITEM           := 0;
      V_VL_BC_ICMSST_UNIT_MED        := 0;
      V_VL_TOT_ICMS_SUPORT_ENTR      := 0;
      V_VL_UNIT_MED_ICMS_SUPORT_ENTR := 0;

      V_QTDESAID                     := 0;
      V_QTDDEVSAID                   := 0;

      V_QTD_TOT_SAIDA_REG            := 0;
      V_VL_TOT_ICMS_EFETIVO          := 0;
      V_QTD_TOT_SAIDA                := 0;

      /*Busca a aliquota efetiva e aliquota do FECP*/
      BEGIN
         SELECT A.VRALIQINT, A.VRALIQFECP
           INTO V_ALIQ_ICMS, V_ALIQ_FECOP
           FROM ALIQINTPRODIE A
          WHERE A.CDEMPRESA  = P_CDEMPRESA
            AND A.CDINSCESTA = P_CDINSCESTA
            AND A.CDPRODUTO  = C_PROD.CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_ALIQ_ICMS := 18;
            V_ALIQ_FECOP     := 0;
      END;
      V_ALIQ_ICMS      := NVL(V_ALIQ_ICMS_ITEM,18);
      V_ALIQ_FECOP     := NVL(V_ALIQ_FECOP,0);
      V_ALIQ_ICMS_ITEM := V_ALIQ_ICMS + V_ALIQ_FECOP;

      /*Registro 1120  Identificacao das notas fiscais de devolucao das entradas*/
      FOR C_REG1120 IN (SELECT '1120'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_DEVOLVIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_BC_ICMS_ST, '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_SUPORT_ENTR,'999999990.00')),'.',',')  ||'|'||
                               CHAVE_REF                  ||'|'||
                               N_ITEM_REF AS REG_1120, QTD_DEVOLVIDA, VL_BC_ICMS_ST, VL_ICMS_SUPORT_ENTR
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJ_DEST, EF.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_DEVOLVIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(CASE WHEN VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'80','B') > 0
                                                  THEN VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'80','B')
                                                  ELSE IT.VRCONTITIMP END,2) AS VL_BC_ICMS_ST,
                                       ROUND(NVL(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'80','I'),0) + NVL(VRENCARGOIMPXML,0) +
                                             NVL(VALOR_FECP_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'80','I'),0)    +
                                             NVL(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'00','I'),0) +
                                             NVL(VALOR_FECP_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,'00','I'),0) ,2) AS VL_ICMS_SUPORT_ENTR,
                                       NR.NRACESSONFE AS CHAVE_REF, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM_REF
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, FORNECEDOR FO, ENDEFORN EF, ITEMNOTAFISC IR, NOTAFISCAL NR,
                                         (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                            FROM IMPOSTONF I
                                           WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDFORNECED   = FO.CDFORNECED
                                   AND FO.CDFORNECED   = EF.CDFORNECED
                                   AND EF.IDTPENDEFORN = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),2,1) = '4' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND IR.CDFILIAL     = NF.CDFILIAL
                                   AND IR.NRLANCTONF   = NF.NRLANCTONF
                                   AND IR.CDFILIALIT   = NR.CDFILIAL
                                   AND IR.NRLANCTONFIT = NR.NRLANCTONF
                                   AND NVL(CP.IDTIPOCFOP,'N') = 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1120'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1120.REG_1120, P_NRSEQARQMAG);
         V_CONTREG      :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDDEVENTR              := NVL(V_QTDDEVENTR,0) + C_REG1120.QTD_DEVOLVIDA;
         V_QTD_TOT_ENTRADA         := NVL(V_QTD_TOT_ENTRADA,0) - C_REG1120.QTD_DEVOLVIDA;
         V_VL_BC_ICMSST_UNIT_MED   := NVL(V_VL_BC_ICMSST_UNIT_MED,0) -  C_REG1120.VL_BC_ICMS_ST;
         V_VL_TOT_ICMS_SUPORT_ENTR := NVL(V_VL_TOT_ICMS_SUPORT_ENTR,0) - C_REG1120.VL_ICMS_SUPORT_ENTR;
      END LOOP;

      /*Verifica se o produto utiliza item de composicao*/
      /*Composicao por Filial*/
      BEGIN
         SELECT P.CDPRODESTO, R.QTSUBPRODLOC
           INTO V_CDPRODUTO, V_VRFATCONV
           FROM ROTFABLOCAL R, PRODUTO P,(SELECT MAX(RT.CDFILIAL||RT.CDSUBPRODLOC) AS CHAVE
                                            FROM ROTFABLOCAL RT, FILIAL FI
                                           WHERE FI.CDEMPRESA  = P_CDEMPRESA
                                             AND FI.CDINSCESTA = P_CDINSCESTA
                                             AND RT.CDFILIAL   = FI.CDFILIAL
                                             AND RT.CDPRODUTO  = C_PROD.CDPRODUTO) F
         WHERE R.CDPRODUTO    = C_PROD.CDPRODUTO
           AND R.CDFILIAL     = SUBSTR(F.CHAVE,1,4)
           AND R.CDSUBPRODLOC = SUBSTR(F.CHAVE,5,10)
           AND R.CDSUBPRODLOC = P.CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDPRODUTO := NULL;
            V_VRFATCONV := 0;
      END;
      /*Composicao Geral*/
      IF V_CDPRODUTO IS NULL THEN
         BEGIN
            SELECT P.CDPRODESTO, R.QTSUBPRODROT
              INTO V_CDPRODUTO, V_VRFATCONV
              FROM ROTFABRICA R, PRODUTO P, (SELECT MAX(CDSUBPRODUTO) AS CDPRODUTO
                                               FROM ROTFABRICA
                                              WHERE CDPRODUTO = C_PROD.CDPRODUTO) F
             WHERE R.CDPRODUTO    = C_PROD.CDPRODUTO
               AND R.CDSUBPRODUTO = P.CDPRODUTO
               AND R.CDSUBPRODUTO = F.CDPRODUTO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDPRODUTO := NULL;
               V_VRFATCONV := 0;
         END;
      END IF;

      V_QTDEENTR := C_PROD.QTDE + V_QTDDEVENTR;

      /*Registro 1110  Identificacao das notas fiscais de entrada - Notas de Entrada para o Produto de Estoque*/
      FOR C_REG1110 IN (SELECT '1110'        ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               COD_RESP_RET  ||'|'||
                               CST_CSOSN     ||'|'||
                               CHAVE         ||'|'||
                               N_NF          ||'|'||
                               CNPJ_EMIT     ||'|'||
                               UF_EMIT       ||'|'||
                               CNPJ_DEST     ||'|'||
                               UF_DEST       ||'|'||
                               CFOP          ||'|'||
                               N_ITEM        ||'|'||
                               UNID_ITEM     ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_ENTRADA,'999999990.000')),'.',',')  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,'999999990.00')),'.',',')  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_BC_ICMS_ST,'999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_SUPORT_ENTR,'999999990.00')),'.',',') AS REG_1110,
                               DTENTRSAID, QTD_ENTRADA, VL_UNIT_ITEM, VL_BC_ICMS_ST, VL_ICMS_SUPORT_ENTR
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, DECODE(EM.IDREGIMEICMSST,'1','3','1') AS COD_RESP_RET,
                                       SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE, SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJ_EMIT, EF.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EL.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(PE.NRSEQITPROD,3,'0') AS N_ITEM, PE.SGUNIDADE AS UNID_ITEM,
                                       PE.QTITEMPROD AS QTD_ENTRADA, ROUND(PE.VRCONTITEM/PE.QTITEMPROD,2) AS VL_UNIT_ITEM,
                                       DECODE(PE.VRBASEST,0,PE.VRCONTITEM,PE.VRBASEST) AS VL_BC_ICMS_ST,
                                       NVL(PE.VRST,0)+NVL(PE.VRICMS,0) AS VL_ICMS_SUPORT_ENTR, PE.DTENTRSAID
                                  FROM PROD_ENTR_REF PE, NOTAFISCAL NF, EMPRESA EM, ITEMPROIMPOS IT, IMPOSTO IM,
                                       FORNECEDOR FO, ENDEFORN EF, FILIAL FI, ENDEFILI EL, CFOPPADRAO CP
                                 WHERE PE.CDPRODUTO    = C_PROD.CDPRODUTO
                                   AND PE.DTENTRSAID   BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND PE.CDFILIAL     = NF.CDFILIAL
                                   AND PE.NRLANCTONF   = NF.NRLANCTONF
                                   AND NF.CDEMPRESA    = P_CDEMPRESA
                                   AND NF.CDINSCESTA   = P_CDINSCESTA
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND NF.CDFILIAL     = IT.CDFILIAL
                                   AND NF.NRLANCTONF   = IT.NRLANCTONF
                                   AND PE.NRSEQITPROD  = IT.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = V_CDICMS
                                   AND NF.CDFORNECED   = FO.CDFORNECED
                                   AND FO.CDFORNECED   = EF.CDFORNECED
                                   AND EF.IDTPENDEFORN = 'P'
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                   AND NF.CDEMPRESA    = EM.CDEMPRESA
                                 UNION ALL
                                SELECT NF.DTEMISSAO AS DT_DOC, DECODE(EM.IDREGIMEICMSST,'1','3','1') AS COD_RESP_RET,
                                       SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE, SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJ_EMIT, EF.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EL.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(PE.NRSEQITPROD,3,'0') AS N_ITEM, PE.SGUNIDADE AS UNID_ITEM,
                                       (PE.QTITEMPROD * V_VRFATCONV) AS QTD_ENTRADA, ROUND(PE.VRCONTITEM/(PE.QTITEMPROD * V_VRFATCONV),2) AS VL_UNIT_ITEM,
                                       DECODE(PE.VRBASEST,0,PE.VRCONTITEM,PE.VRBASEST) AS VL_BC_ICMS_ST,
                                       NVL(PE.VRST,0)+NVL(PE.VRICMS,0) AS VL_ICMS_SUPORT_ENTR, PE.DTENTRSAID
                                  FROM PROD_ENTR_REF PE, NOTAFISCAL NF, EMPRESA EM, ITEMPROIMPOS IT, IMPOSTO IM,
                                       FORNECEDOR FO, ENDEFORN EF, FILIAL FI, ENDEFILI EL, CFOPPADRAO CP
                                 WHERE PE.CDPRODUTO    = V_CDPRODUTO
                                   AND PE.DTENTRSAID   BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND PE.CDFILIAL     = NF.CDFILIAL
                                   AND PE.NRLANCTONF   = NF.NRLANCTONF
                                   AND NF.CDEMPRESA    = P_CDEMPRESA
                                   AND NF.CDINSCESTA   = P_CDINSCESTA
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND NF.CDFILIAL     = IT.CDFILIAL
                                   AND NF.NRLANCTONF   = IT.NRLANCTONF
                                   AND PE.NRSEQITPROD  = IT.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = V_CDICMS
                                   AND NF.CDFORNECED   = FO.CDFORNECED
                                   AND FO.CDFORNECED   = EF.CDFORNECED
                                   AND EF.IDTPENDEFORN = 'P'
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                   AND NF.CDEMPRESA    = EM.CDEMPRESA)) LOOP

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1110'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1110.REG_1110, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDEENTR            := NVL(V_QTDEENTR,0) - C_REG1110.QTD_ENTRADA;
         V_QTD_TOT_ENTRADA     := NVL(V_QTD_TOT_ENTRADA,0) + C_REG1110.QTD_ENTRADA;
         IF (C_REG1110.VL_UNIT_ITEM < V_MENOR_VL_UNIT_ITEM) OR (V_MENOR_VL_UNIT_ITEM = 0) THEN
            V_MENOR_VL_UNIT_ITEM := C_REG1110.VL_UNIT_ITEM;
         END IF;
         V_VL_BC_ICMSST_UNIT_MED   := NVL(V_VL_BC_ICMSST_UNIT_MED,0) + C_REG1110.VL_BC_ICMS_ST;
         V_VL_TOT_ICMS_SUPORT_ENTR := NVL(V_VL_TOT_ICMS_SUPORT_ENTR,0) + C_REG1110.VL_ICMS_SUPORT_ENTR;
      END LOOP;


      IF V_QTDEENTR > 0 THEN
         FOR C_REG1110_ANT IN (SELECT '1110'        ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               COD_RESP_RET  ||'|'||
                               CST_CSOSN     ||'|'||
                               CHAVE         ||'|'||
                               N_NF          ||'|'||
                               CNPJ_EMIT     ||'|'||
                               UF_EMIT       ||'|'||
                               CNPJ_DEST     ||'|'||
                               UF_DEST       ||'|'||
                               CFOP          ||'|'||
                               N_ITEM        ||'|'||
                               UNID_ITEM     ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_ENTRADA,'999999990.000')),'.',',')  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,'999999990.00')),'.',',')  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_BC_ICMS_ST,'999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_SUPORT_ENTR,'999999990.00')),'.',',') AS REG_1110,
                               DTENTRSAID, QTD_ENTRADA, VL_UNIT_ITEM, VL_BC_ICMS_ST, VL_ICMS_SUPORT_ENTR
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, DECODE(EM.IDREGIMEICMSST,'1','3','1') AS COD_RESP_RET,
                                       SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE, SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJ_EMIT, EF.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EL.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(PE.NRSEQITPROD,3,'0') AS N_ITEM, PE.SGUNIDADE AS UNID_ITEM,
                                       PE.QTITEMPROD AS QTD_ENTRADA, ROUND(PE.VRCONTITEM/PE.QTITEMPROD,2) AS VL_UNIT_ITEM,
                                       DECODE(PE.VRBASEST,0,PE.VRCONTITEM,PE.VRBASEST) AS VL_BC_ICMS_ST,
                                       NVL(PE.VRST,0)+NVL(PE.VRICMS,0) AS VL_ICMS_SUPORT_ENTR, PE.DTENTRSAID
                                  FROM PROD_ENTR_REF PE, NOTAFISCAL NF, EMPRESA EM, ITEMPROIMPOS IT, IMPOSTO IM,
                                       FORNECEDOR FO, ENDEFORN EF, FILIAL FI, ENDEFILI EL, CFOPPADRAO CP
                                 WHERE PE.CDPRODUTO    = C_PROD.CDPRODUTO
                                   AND PE.DTENTRSAID   < P_DTINICIAL
                                   AND PE.CDFILIAL     = NF.CDFILIAL
                                   AND PE.NRLANCTONF   = NF.NRLANCTONF
                                   AND NF.CDEMPRESA    = P_CDEMPRESA
                                   AND NF.CDINSCESTA   = P_CDINSCESTA
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND NF.CDFILIAL     = IT.CDFILIAL
                                   AND NF.NRLANCTONF   = IT.NRLANCTONF
                                   AND PE.NRSEQITPROD  = IT.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = V_CDICMS
                                   AND NF.CDFORNECED   = FO.CDFORNECED
                                   AND FO.CDFORNECED   = EF.CDFORNECED
                                   AND EF.IDTPENDEFORN = 'P'
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                   AND NF.CDEMPRESA    = EM.CDEMPRESA
                                 UNION ALL
                                SELECT NF.DTEMISSAO AS DT_DOC, DECODE(EM.IDREGIMEICMSST,'1','3','1') AS COD_RESP_RET,
                                       SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE, SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJ_EMIT, EF.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EL.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(PE.NRSEQITPROD,3,'0') AS N_ITEM, PE.SGUNIDADE AS UNID_ITEM,
                                       (PE.QTITEMPROD * V_VRFATCONV) AS QTD_ENTRADA, ROUND(PE.VRCONTITEM/(PE.QTITEMPROD * V_VRFATCONV),2) AS VL_UNIT_ITEM,
                                       DECODE(PE.VRBASEST,0,PE.VRCONTITEM,PE.VRBASEST) AS VL_BC_ICMS_ST,
                                       NVL(PE.VRST,0)+NVL(PE.VRICMS,0) AS VL_ICMS_SUPORT_ENTR, PE.DTENTRSAID
                                  FROM PROD_ENTR_REF PE, NOTAFISCAL NF, EMPRESA EM, ITEMPROIMPOS IT, IMPOSTO IM,
                                       FORNECEDOR FO, ENDEFORN EF, FILIAL FI, ENDEFILI EL, CFOPPADRAO CP
                                 WHERE PE.CDPRODUTO    = V_CDPRODUTO
                                   AND PE.DTENTRSAID   < P_DTINICIAL
                                   AND PE.CDFILIAL     = NF.CDFILIAL
                                   AND PE.NRLANCTONF   = NF.NRLANCTONF
                                   AND NF.CDEMPRESA    = P_CDEMPRESA
                                   AND NF.CDINSCESTA   = P_CDINSCESTA
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND NF.CDFILIAL     = IT.CDFILIAL
                                   AND NF.NRLANCTONF   = IT.NRLANCTONF
                                   AND PE.NRSEQITPROD  = IT.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = V_CDICMS
                                   AND NF.CDFORNECED   = FO.CDFORNECED
                                   AND FO.CDFORNECED   = EF.CDFORNECED
                                   AND EF.IDTPENDEFORN = 'P'
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                   AND NF.CDEMPRESA    = EM.CDEMPRESA)) LOOP

            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               (LPAD(V_SEQPROD,3,'0')||'1110'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1110_ANT.REG_1110, P_NRSEQARQMAG);
            V_CONTREG :=  NVL(V_CONTREG,0) + 1;
            V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

            --GERA OS REGISTROS BUSCANDO AS ENTRADAS ANTERIORES ATE ATINGIR A QUANTIDADE
            V_QTDEENTR := V_QTDEENTR - C_REG1110_ANT.QTD_ENTRADA;
            V_QTD_TOT_ENTRADA     := NVL(V_QTD_TOT_ENTRADA,0) + C_REG1110_ANT.QTD_ENTRADA;
            IF (C_REG1110_ANT.VL_UNIT_ITEM < V_MENOR_VL_UNIT_ITEM) OR (V_MENOR_VL_UNIT_ITEM = 0) THEN
               V_MENOR_VL_UNIT_ITEM := C_REG1110_ANT.VL_UNIT_ITEM;
            END IF;
            V_VL_BC_ICMSST_UNIT_MED   := NVL(V_VL_BC_ICMSST_UNIT_MED,0) + C_REG1110_ANT.VL_BC_ICMS_ST;
            V_VL_TOT_ICMS_SUPORT_ENTR := NVL(V_VL_TOT_ICMS_SUPORT_ENTR,0) + C_REG1110_ANT.VL_ICMS_SUPORT_ENTR;

            IF V_QTDEENTR < 0 THEN
               EXIT;
            END IF;
         END LOOP;
      END IF;

      IF V_QTD_TOT_ENTRADA > 0 THEN
         V_VL_BC_ICMSST_UNIT_MED := ROUND(V_VL_BC_ICMSST_UNIT_MED/V_QTD_TOT_ENTRADA,2);
         V_VL_UNIT_MED_ICMS_SUPORT_ENTR := ROUND(V_VL_TOT_ICMS_SUPORT_ENTR/V_QTD_TOT_ENTRADA,2);
      ELSE
         V_VL_BC_ICMSST_UNIT_MED := 0;
         V_VL_UNIT_MED_ICMS_SUPORT_ENTR := 0;
      END IF;

      /*Registro 1100  Totalizador das entradas do produto*/
      V_REG1100 := '1100'                                                                    ||'|'||
                   REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_ENTRADA,'999999990.000')),'.',',')        ||'|'||
                   REPLACE(LTRIM(TO_CHAR(V_MENOR_VL_UNIT_ITEM,'999999990.00')),'.',',')      ||'|'||
                   REPLACE(LTRIM(TO_CHAR(V_VL_BC_ICMSST_UNIT_MED,'999999990.00')),'.',',')   ||'|'||
                   REPLACE(LTRIM(TO_CHAR(V_VL_TOT_ICMS_SUPORT_ENTR,'999999990.00')),'.',',') ||'|'||
                   REPLACE(LTRIM(TO_CHAR(V_VL_UNIT_MED_ICMS_SUPORT_ENTR,'999999990.00')),'.',',');
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_SEQPROD,3,'0')||'1100'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG1100, P_NRSEQARQMAG);
      V_CONTREG :=  NVL(V_CONTREG,0) + 1;
      V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

      /*Registro 1220  Identificacao das notas fiscais de devolucao das saidas para consumidor final*/
      FOR C_REG1220 IN (SELECT '1220'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_DEVOLVIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  ||'|'||
                               CHAVE_REF                  ||'|'||
                               N_ITEM_REF AS REG_1220, QTD_DEVOLVIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_DEVOLVIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO,
                                       NR.NRACESSONFE AS CHAVE_REF, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM_REF
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC, ITEMNOTAFISC IR, NOTAFISCAL NR,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),2,1) = '4' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND IR.CDFILIAL     = NF.CDFILIAL
                                   AND IR.NRLANCTONF   = NF.NRLANCTONF
                                   AND IR.CDFILIALIT   = NR.CDFILIAL
                                   AND IR.NRLANCTONFIT = NR.NRLANCTONF
                                   AND CL.IDCONSFINAL  = 'S'
                                   AND NVL(CP.IDTIPOCFOP,'N') = 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1220'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1220.REG_1220, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDDEVSAID          := NVL(V_QTDDEVSAID,0) + C_REG1220.QTD_DEVOLVIDA;
         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) - C_REG1220.QTD_DEVOLVIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) - C_REG1220.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1210  Identificacao das notas fiscais de saida para consumidor final*/
      FOR C_REG1210 IN (SELECT '1210'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_SAIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  AS REG_1210,
                               QTD_SAIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),2,1) = '4' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND CL.IDCONSFINAL  = 'S'
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                 UNION ALL
                                SELECT TRUNC(VE.DTEMISSAONFCE) AS DT_DOC, SUBSTR(LPAD(II.CDCSTPRODI,3,'0'),2,2) AS CST_CSOSN, VE.NRACESSONFCE AS CHAVE,
                                       SUBSTR(LPAD(VE.NRNOTAFISCALCE,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(VE.NRINSCRCONS,'.'),'/'),'-') AS CNPJ_DEST, VE.SGESTADO AS UF_DEST,
                                       II.CDCFOPPROD AS CFOP, IT.NRSEQUITVEND AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IT.QTPRODVEND*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND((IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0))*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND( ROUND(IT.QTPRODVEND*NVL(PR.VRFATOCONV,1),3) * ROUND((IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0))*NVL(PR.VRFATOCONV,1),2) *(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITVENDAIMPOS II, ITEMVENDA IT, VENDA VE, FILIAL FI, ENDEFILI EL,
                                       PRODUTO PR, PRODUTO PE, (SELECT R.CDFILIALVENDA, R.CDCAIXA, R.NRSEQVENDA
                                                                  FROM RELACNFCEAPROV R, IMPOSTONF I
                                                                 WHERE R.CDFILIALNOTA = I.CDFILIAL
                                                                   AND R.NRLANCTONF   = I.NRLANCTONF
                                                                   AND I.NRSEQUMAPAPU = V_NRSEQUMAPAPU) RV
                                 WHERE VE.CDFILIAL        = RV.CDFILIALVENDA
                                   AND VE.CDCAIXA         = RV.CDCAIXA
                                   AND VE.NRSEQVENDA      = RV.NRSEQVENDA
                                   AND VE.IDSITUVENDA     = 'O'
                                   AND VE.IDTPAMBNFCE     = '1'
                                   AND VE.IDSTATUSNFCE    = 'A'
                                   AND VE.CDFILIAL        = IT.CDFILIAL
                                   AND VE.NRSEQVENDA      = IT.NRSEQVENDA
                                   AND VE.CDCAIXA         = IT.CDCAIXA
                                   AND IT.CDFILIAL        = II.CDFILIAL
                                   AND IT.CDCAIXA         = II.CDCAIXA
                                   AND IT.NRSEQUITVEND    = II.NRSEQUITVEND
                                   AND IT.NRSEQVENDA      = II.NRSEQVENDA
                                   AND II.CDCFOPPROD      = '5405'
                                   AND VE.IDAPROVACAONFCE = 'A'
                                   AND VE.IDTPEMISVEND    IN ('N','S')
                                   AND VE.CDFILIAL        = FI.CDFILIAL
                                   AND FI.CDFILIAL        = EL.CDFILIAL
                                   AND IT.CDPRODUTO       = PR.CDPRODUTO
                                   AND PR.CDPRODESTO      = PE.CDPRODUTO(+)
                                   AND NVL(PE.CDPRODUTO,PR.CDPRODUTO)= C_PROD.CDPRODUTO)) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1210'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1210.REG_1210, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) + C_REG1210.QTD_SAIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) + C_REG1210.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1200  Totalizador das saidas para consumidor final*/
      IF V_QTD_TOT_SAIDA_REG > 0 THEN
         V_VL_CONFRONTO_ICMS_ENTRADA     := ROUND(V_QTD_TOT_SAIDA_REG*V_VL_UNIT_MED_ICMS_SUPORT_ENTR,2);
         IF V_VL_CONFRONTO_ICMS_ENTRADA >= V_VL_TOT_ICMS_EFETIVO THEN
            V_RESULT_RECUPERAR_RESSARCIR := V_VL_CONFRONTO_ICMS_ENTRADA - V_VL_TOT_ICMS_EFETIVO;
            V_RESULT_COMPLEMENTAR        := 0;
         ELSE
            V_RESULT_RECUPERAR_RESSARCIR := 0;
            V_RESULT_COMPLEMENTAR        := ABS(V_VL_CONFRONTO_ICMS_ENTRADA - V_VL_TOT_ICMS_EFETIVO);
         END IF;
         V_APUR_ICMSST_RECUPERAR_RESSAR  := V_RESULT_RECUPERAR_RESSARCIR * (V_ALIQ_ICMS / V_ALIQ_ICMS_ITEM);
         V_APUR_ICMSST_COMPLEMENTAR      := V_RESULT_COMPLEMENTAR * (V_ALIQ_ICMS / V_ALIQ_ICMS_ITEM);
         V_APUR_FECOP_RESSARCIR          := V_RESULT_RECUPERAR_RESSARCIR * (V_ALIQ_FECOP / V_ALIQ_ICMS_ITEM);
         V_APUR_FECOP_COMPLEMENTAR       := V_RESULT_COMPLEMENTAR * (V_ALIQ_FECOP / V_ALIQ_ICMS_ITEM);

         V_REG1200 := '1200'                                                                         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_SAIDA_REG,'999999990.000')),'.',',')           ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_TOT_ICMS_EFETIVO,'999999990.00')),'.',',')          ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_CONFRONTO_ICMS_ENTRADA,'999999990.00')),'.',',')    ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_RESULT_RECUPERAR_RESSARCIR,'999999990.00')),'.',',')   ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_RESULT_COMPLEMENTAR,'999999990.00')),'.',',')          ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_ICMSST_RECUPERAR_RESSAR,'999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_ICMSST_COMPLEMENTAR,'999999990.00')),'.',',')     ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_FECOP_RESSARCIR,'999999990.00')),'.',',')         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_FECOP_COMPLEMENTAR,'999999990.00')),'.',',');
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1200'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG1200, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA := NVL(V_QTD_TOT_SAIDA,0) + V_QTD_TOT_SAIDA_REG;
         V_APUR_ICMSST_RECUPERAR_1200   := NVL(V_APUR_ICMSST_RECUPERAR_1200,0)   + V_APUR_ICMSST_RECUPERAR_RESSAR;
         V_APUR_ICMSST_COMPL_1200       := NVL(V_APUR_ICMSST_COMPL_1200,0)       + V_APUR_ICMSST_COMPLEMENTAR;
         V_APUR_FECOP_RESSARCIR_1200    := NVL(V_APUR_FECOP_RESSARCIR_1200,0)    + V_APUR_FECOP_RESSARCIR;
         V_APUR_FECOP_COMPLEMENTAR_1200 := NVL(V_APUR_FECOP_COMPLEMENTAR_1200,0) + V_APUR_FECOP_COMPLEMENTAR;
      END IF;

      V_QTD_TOT_SAIDA_REG   := 0;
      V_VL_TOT_ICMS_EFETIVO := 0;
      /*Registro 1320  Identificacao das notas fiscais de devolucao das saidas para outros estados*/
      FOR C_REG1320 IN (SELECT '1320'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_DEVOLVIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  ||'|'||
                               CHAVE_REF                  ||'|'||
                               N_ITEM_REF AS REG_1320, QTD_DEVOLVIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_DEVOLVIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO,
                                       NR.NRACESSONFE AS CHAVE_REF, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM_REF
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC, ITEMNOTAFISC IR, NOTAFISCAL NR,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '24' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND IR.CDFILIAL     = NF.CDFILIAL
                                   AND IR.NRLANCTONF   = NF.NRLANCTONF
                                   AND IR.CDFILIALIT   = NR.CDFILIAL
                                   AND IR.NRLANCTONFIT = NR.NRLANCTONF
                                   AND NVL(CP.IDTIPOCFOP,'N') = 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1320'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1320.REG_1320, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDDEVSAID          := NVL(V_QTDDEVSAID,0) + C_REG1320.QTD_DEVOLVIDA;
         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) - C_REG1320.QTD_DEVOLVIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) - C_REG1320.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1310  Identificacao das notas fiscais de saida para outros estados*/
      FOR C_REG1310 IN (SELECT '1310'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_SAIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  AS REG_1310,
                               QTD_SAIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '64' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                 UNION ALL
                                SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                        SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                        REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                        REPLACE(REPLACE(REPLACE(CL.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                        IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                        ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                        ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                        ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                   FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                        FILIAL FI, ENDEFILI EL, FILIAL CL, ENDEFILI EC,
                                        (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                           FROM IMPOSTONF I
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                  WHERE IT.CDFILIAL     = II.CDFILIAL
                                    AND IT.NRLANCTONF   = II.NRLANCTONF
                                    AND IT.CDFILIAL     = IP.CDFILIAL
                                    AND IT.NRLANCTONF   = IP.NRLANCTONF
                                    AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                    AND IT.CDIMPOSTO    = '00'
                                    AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                    AND IT.CDFILIAL     = NF.CDFILIAL
                                    AND IT.NRLANCTONF   = NF.NRLANCTONF
                                    AND IP.CDPRODUTO    = PR.CDPRODUTO
                                    AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                    AND NF.CDFILIEMIT   = FI.CDFILIAL
                                    AND FI.CDFILIAL     = EL.CDFILIAL
                                    AND EL.IDTPENDEFILI = 'P'
                                    AND NF.CDFILIRECE   = CL.CDFILIAL
                                    AND CL.CDFILIAL     = EC.CDFILIAL
                                    AND EC.IDTPENDEFILI = 'P'
                                    AND NF.IDENTRSAIDOP = 'S'
                                    AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '64' /*CFOP de Substituicao Tributaria*/
                                    AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                    AND NVL(CP.IDTIPOCFOP,'N') <> 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1310'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1310.REG_1310, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) + C_REG1310.QTD_SAIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) + C_REG1310.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1300  Totalizador das saidas para outros estados*/
      IF V_QTD_TOT_SAIDA_REG > 0 THEN
         V_VL_CONFRONTO_ICMS_ENTRADA     := ROUND(V_QTD_TOT_SAIDA_REG*V_VL_UNIT_MED_ICMS_SUPORT_ENTR,2);
         IF V_VL_CONFRONTO_ICMS_ENTRADA >= V_VL_TOT_ICMS_EFETIVO THEN
            V_RESULT_RECUPERAR_RESSARCIR := V_VL_CONFRONTO_ICMS_ENTRADA - V_VL_TOT_ICMS_EFETIVO;
         ELSE
            V_RESULT_RECUPERAR_RESSARCIR := 0;
         END IF;
         V_APUR_ICMSST_RECUPERAR_RESSAR  := V_RESULT_RECUPERAR_RESSARCIR * (V_ALIQ_ICMS / V_ALIQ_ICMS_ITEM);
         V_APUR_FECOP_RESSARCIR          := V_RESULT_RECUPERAR_RESSARCIR * (V_ALIQ_FECOP / V_ALIQ_ICMS_ITEM);

         V_REG1300 := '1300'                                                                         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_SAIDA_REG,'999999990.000')),'.',',')           ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_TOT_ICMS_EFETIVO,'999999990.00')),'.',',')          ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_CONFRONTO_ICMS_ENTRADA,'999999990.00')),'.',',')    ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_RESULT_RECUPERAR_RESSARCIR,'999999990.00')),'.',',')   ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_ICMSST_RECUPERAR_RESSAR,'999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_FECOP_RESSARCIR,'999999990.00')),'.',',');
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1300'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG1300, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA := NVL(V_QTD_TOT_SAIDA,0) + V_QTD_TOT_SAIDA_REG;
         V_APUR_ICMSST_RECUPERAR_1300 := NVL(V_APUR_ICMSST_RECUPERAR_1300,0) + V_APUR_ICMSST_RECUPERAR_RESSAR;
         V_APUR_FECOP_RESSARCIR_1300  := NVL(V_APUR_FECOP_RESSARCIR_1300,0)  + V_APUR_FECOP_RESSARCIR;
      END IF;

      V_QTD_TOT_SAIDA_REG   := 0;
      V_VL_TOT_ICMS_EFETIVO := 0;
      /*Registro 1420  Identificacao das notas fiscais de devolucao das saidas internas de que trata o art. 119 do Anexo IX do RICMS/17*/
      FOR C_REG1420 IN (SELECT '1420'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_DEVOLVIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  ||'|'||
                               CHAVE_REF                  ||'|'||
                               N_ITEM_REF AS REG_1420, QTD_DEVOLVIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_DEVOLVIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO,
                                       NR.NRACESSONFE AS CHAVE_REF, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM_REF
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC, ITEMNOTAFISC IR, NOTAFISCAL NR,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '14' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND IR.CDFILIAL     = NF.CDFILIAL
                                   AND IR.NRLANCTONF   = NF.NRLANCTONF
                                   AND IR.CDFILIALIT   = NR.CDFILIAL
                                   AND IR.NRLANCTONFIT = NR.NRLANCTONF
                                   AND CL.IDCONSFINAL  = 'N'
                                   AND CL.IDOPTANTESNCLIE = 'N'
                                   AND NVL(CP.IDTIPOCFOP,'N') = 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1420'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1420.REG_1420, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDDEVSAID          := NVL(V_QTDDEVSAID,0) + C_REG1420.QTD_DEVOLVIDA;
         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) - C_REG1420.QTD_DEVOLVIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) - C_REG1420.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1410  Identificacao das notas fiscais de saidas de que trata o art. 119 do Anexo IX do RICMS/17*/
      FOR C_REG1410 IN (SELECT '1410'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_SAIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  AS REG_1410,
                               QTD_SAIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '54' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND CL.IDCONSFINAL  = 'N'
                                   AND CL.IDOPTANTESNCLIE = 'N'
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D'
                                 UNION ALL
                                SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, FILIAL CL, ENDEFILI EC,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDFILIRECE   = CL.CDFILIAL
                                   AND CL.CDFILIAL     = EC.CDFILIAL
                                   AND EC.IDTPENDEFILI = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '54' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1410'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1410.REG_1410, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) + C_REG1410.QTD_SAIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) + C_REG1410.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1400  Totalizador das saidas internas que trata o art. 119 do RICMS/17*/
      /* if c_prod.cdproduto = '0000001159' then
         raise_application_error(-20501,V_VL_TOT_ICMS_EFETIVO,true);
      end if;*/
      IF V_QTD_TOT_SAIDA_REG > 0 THEN
         V_VL_CONFRONTO_ICMS_ENTRADA     := ROUND(V_QTD_TOT_SAIDA_REG*V_VL_UNIT_MED_ICMS_SUPORT_ENTR,2);
         IF V_VL_CONFRONTO_ICMS_ENTRADA >= V_VL_TOT_ICMS_EFETIVO THEN
            V_RESULT_RECUPERAR_RESSARCIR := V_VL_CONFRONTO_ICMS_ENTRADA - V_VL_TOT_ICMS_EFETIVO;
         ELSE
            V_RESULT_RECUPERAR_RESSARCIR := 0;
         END IF;
         V_APUR_ICMSST_RECUPERAR_RESSAR  := V_RESULT_RECUPERAR_RESSARCIR * (V_ALIQ_ICMS / V_ALIQ_ICMS_ITEM);

         V_REG1400 := '1400'                                                                         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_SAIDA_REG,'999999990.000')),'.',',')           ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_TOT_ICMS_EFETIVO,'999999990.00')),'.',',')          ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_CONFRONTO_ICMS_ENTRADA,'999999990.00')),'.',',')    ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_ICMSST_RECUPERAR_RESSAR,'999999990.00')),'.',',');
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1400'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG1400, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA := NVL(V_QTD_TOT_SAIDA,0) + V_QTD_TOT_SAIDA_REG;
         V_APUR_ICMSST_RECUPERAR_1400 := NVL(V_APUR_ICMSST_RECUPERAR_1400,0) + V_APUR_ICMSST_RECUPERAR_RESSAR;
      END IF;

      V_QTD_TOT_SAIDA_REG   := 0;
      V_VL_TOT_ICMS_EFETIVO := 0;
      /*Registro 1520  Identificacao das notas fiscais de devolucao das saidas internas destinadas a contribuinte do Simples Nacional*/
      FOR C_REG1520 IN (SELECT '1520'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_DEVOLVIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  ||'|'||
                               CHAVE_REF                  ||'|'||
                               N_ITEM_REF AS REG_1520, QTD_DEVOLVIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_DEVOLVIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO,
                                       NR.NRACESSONFE AS CHAVE_REF, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM_REF
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC, ITEMNOTAFISC IR, NOTAFISCAL NR,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIRECE   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'E'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '14' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND IR.CDFILIAL     = NF.CDFILIAL
                                   AND IR.NRLANCTONF   = NF.NRLANCTONF
                                   AND IR.CDFILIALIT   = NR.CDFILIAL
                                   AND IR.NRLANCTONFIT = NR.NRLANCTONF
                                   AND CL.IDOPTANTESNCLIE = 'S'
                                   AND NVL(CP.IDTIPOCFOP,'N') = 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1520'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1520.REG_1520, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTDDEVSAID          := NVL(V_QTDDEVSAID,0) + C_REG1520.QTD_DEVOLVIDA;
         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) - C_REG1520.QTD_DEVOLVIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) - C_REG1520.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1510  Identificacao das notas fiscais destinadas a contribuinte do Simples Nacional*/
      FOR C_REG1510 IN (SELECT '1510'                     ||'|'||
                               TO_CHAR(DT_DOC,'DDMMYYYY') ||'|'||
                               CST_CSOSN                  ||'|'||
                               CHAVE                      ||'|'||
                               N_NF                       ||'|'||
                               CNPJ_EMIT                  ||'|'||
                               UF_EMIT                    ||'|'||
                               CNPJ_DEST                  ||'|'||
                               UF_DEST                    ||'|'||
                               CFOP                       ||'|'||
                               N_ITEM                     ||'|'||
                               UNID_ITEM                  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(QTD_SAIDA,'999999990.000')),'.',',')       ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_UNIT_ITEM,  '999999990.00')),'.',',')   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(VL_ICMS_EFETIVO,'999999990.00')),'.',',')  AS REG_1510,
                               QTD_SAIDA, VL_ICMS_EFETIVO
                          FROM (SELECT NF.DTEMISSAO AS DT_DOC, SUBSTR(LPAD(IT.CDSITUTRIBIT,3,'0'),2,2) AS CST_CSOSN, NF.NRACESSONFE AS CHAVE,
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9) AS N_NF,
                                       REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS CNPJ_EMIT, EL.SGESTADO AS UF_EMIT,
                                       REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.'),'/'),'-') AS CNPJ_DEST, EC.SGESTADO AS UF_DEST,
                                       IT.CDFISCOPERIT AS CFOP, LPAD(IT.NRSEQITPROD,3,'0') AS N_ITEM, C_PROD.SGUNIDADE AS UNID_ITEM,
                                       ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3) AS QTD_SAIDA,
                                       ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2) AS VL_UNIT_ITEM,
                                       ROUND(ROUND(IP.QTITEMPROD*NVL(PR.VRFATOCONV,1),3)*ROUND(IP.VRUNITITEM*NVL(PR.VRFATOCONV,1),2)*(V_ALIQ_ICMS_ITEM/100),2) AS VL_ICMS_EFETIVO
                                  FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, NOTAFISCAL NF, PRODUTO PR, CFOPPADRAO CP,
                                       FILIAL FI, ENDEFILI EL, CLIENTE CL, ENDECLIE EC,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                          FROM IMPOSTONF I
                                         WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) II
                                 WHERE IT.CDFILIAL     = II.CDFILIAL
                                   AND IT.NRLANCTONF   = II.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.NRLANCTONF   = IP.NRLANCTONF
                                   AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                   AND IT.CDIMPOSTO    = '00'
                                   AND NVL(IT.NRITPROIMPOS,'1') <> '3'
                                   AND IT.CDFILIAL     = NF.CDFILIAL
                                   AND IT.NRLANCTONF   = NF.NRLANCTONF
                                   AND IP.CDPRODUTO    = PR.CDPRODUTO
                                   AND NVL(PR.CDPRODESTO,PR.CDPRODUTO) = C_PROD.CDPRODUTO
                                   AND NF.CDFILIEMIT   = FI.CDFILIAL
                                   AND FI.CDFILIAL     = EL.CDFILIAL
                                   AND EL.IDTPENDEFILI = 'P'
                                   AND NF.CDCLIENTE    = CL.CDCLIENTE
                                   AND CL.CDCLIENTE    = EC.CDCLIENTE
                                   AND EC.IDTPENDECLIE = 'P'
                                   AND NF.IDENTRSAIDOP = 'S'
                                   AND SUBSTR(LPAD(IT.CDFISCOPERIT,4,'0'),1,2) = '54' /*CFOP de Substituicao Tributaria*/
                                   AND IT.CDFISCOPERIT = CP.CDCFOPPADRAO
                                   AND CL.IDOPTANTESNCLIE = 'S'
                                   AND NVL(CP.IDTIPOCFOP,'N') <> 'D')) LOOP
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1510'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1510.REG_1510, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA_REG   := NVL(V_QTD_TOT_SAIDA_REG,0) + C_REG1510.QTD_SAIDA;
         V_VL_TOT_ICMS_EFETIVO := NVL(V_VL_TOT_ICMS_EFETIVO,0) + C_REG1510.VL_ICMS_EFETIVO;
      END LOOP;

      /*Registro 1500  Totalizador das saidas internas destinadas a contribuinte do Simples Nacional*/
      IF V_QTD_TOT_SAIDA_REG > 0 THEN
         V_VL_ICMSST_UNIT_ENTR           := (V_VL_BC_ICMSST_UNIT_MED /(1+0.4)) * (0.4/CASE WHEN V_ALIQ_ICMS = 18 THEN  0.7 ELSE 0.5 END) * (V_ALIQ_ICMS/100);
         V_APUR_ICMSST_RECUPERAR_RESSAR  := V_QTD_TOT_SAIDA_REG * V_VL_ICMSST_UNIT_ENTR;

         V_REG1500 := '1500'                                                                   ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_SAIDA_REG,'999999990.000')),'.',',')     ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_VL_ICMSST_UNIT_ENTR,'999999990.00')),'.',',')    ||'|'||
                      REPLACE(LTRIM(TO_CHAR(V_APUR_ICMSST_RECUPERAR_RESSAR,'999999990.00')),'.',',');
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1500'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG1500, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;

         V_QTD_TOT_SAIDA := NVL(V_QTD_TOT_SAIDA,0) + V_QTD_TOT_SAIDA_REG;
         V_APUR_ICMSST_RECUPERAR_1500 := NVL(V_APUR_ICMSST_RECUPERAR_1500,0) + V_APUR_ICMSST_RECUPERAR_RESSAR;
      END IF;

      /*Registro 1000  Identificacao analitica do produto*/
      FOR C_REG1000 IN (SELECT '1000'          ||'|'||
                              CASE WHEN V_ALIQ_FECOP > 0 THEN 1 ELSE 0 END ||'|'||
                              PR.CDARVPROD    ||'|'||
                              PR.CDBARPRODUTO ||'|'||
                              ''              ||'|'||
                              PR.CDCLASFISC   ||'|'||
                              REPLACE(REPLACE(PR.CDCEST,'.'),'-') ||'|'||
                              REPLACE(REPLACE(PR.NMPRODUTO,'C','C'),'c','c') ||'|'||
                              PR.SGUNIDADE    ||'|'||
                              REPLACE(LTRIM(TO_CHAR(V_ALIQ_ICMS_ITEM,'90.00')),'.',',')      ||'|'||
                              CASE WHEN V_ALIQ_FECOP > 0 THEN REPLACE(LTRIM(TO_CHAR(V_ALIQ_FECOP,'0.00')),'.',',') ELSE '' END ||'|'||
                              REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_ENTRADA,'999999990.000')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(V_QTD_TOT_SAIDA,'999999990.000')),'.',',')    AS REG_1000
                         FROM PRODUTO PR
                        WHERE PR.CDPRODUTO = C_PROD.CDPRODUTO) LOOP

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_SEQPROD,3,'0')||'1000'||LPAD(RTRIM(V_CONTREG),3,'0'), C_REG1000.REG_1000, P_NRSEQARQMAG);
         V_CONTREG :=  NVL(V_CONTREG,0) + 1;
         V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1;
      END LOOP;
   END LOOP;

   /*Registro 1999  Encerramento do Bloco 1*/

   V_QTD_LIN_BLC1 := NVL(V_QTD_LIN_BLC1,0) + 1; /*Acrecenta o registro 1999*/

   INSERT INTO ARQMAGNETICO

      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_SEQPROD,3,'0')||'1999'||LPAD(RTRIM(V_CONTREG),3,'0'), '1999'||'|'||LPAD(V_QTD_LIN_BLC1,9,'0'), P_NRSEQARQMAG);
   V_CONTREG :=  NVL(V_CONTREG,0) + 1;


   /*Registro 9000  Apuracao total do arquivo*/

   V_REG9000 := '9000'                       ||'|'||

                REPLACE(LTRIM(TO_CHAR(CASE WHEN NVL(V_APUR_ICMSST_RECUPERAR_1200,0) - NVL(V_APUR_ICMSST_COMPL_1200,0) > 0

                                           THEN NVL(V_APUR_ICMSST_RECUPERAR_1200,0) - NVL(V_APUR_ICMSST_COMPL_1200,0)

                                           ELSE 0 END,'999999990.000')),'.',',')              ||'|'||

                REPLACE(LTRIM(TO_CHAR(CASE WHEN NVL(V_APUR_ICMSST_RECUPERAR_1200,0) - NVL(V_APUR_ICMSST_COMPL_1200,0) < 0

                                           THEN ABS(NVL(V_APUR_ICMSST_RECUPERAR_1200,0) - NVL(V_APUR_ICMSST_COMPL_1200,0))

                                           ELSE 0 END,'999999990.000')),'.',',')              ||'|'||

                REPLACE(LTRIM(TO_CHAR(NVL(V_APUR_ICMSST_RECUPERAR_1300,0),'999999990.000')),'.',',') ||'|'||

                REPLACE(LTRIM(TO_CHAR(NVL(V_APUR_ICMSST_RECUPERAR_1400,0),'999999990.000')),'.',',') ||'|'||

                REPLACE(LTRIM(TO_CHAR(NVL(V_APUR_ICMSST_RECUPERAR_1500,0),'999999990.000')),'.',',') ||'|'||

                REPLACE(LTRIM(TO_CHAR(CASE WHEN NVL(V_APUR_FECOP_RESSARCIR_1200,0) + NVL(V_APUR_FECOP_RESSARCIR_1300,0) - NVL(V_APUR_FECOP_COMPLEMENTAR_1200,0) > 0

                                           THEN NVL(V_APUR_FECOP_RESSARCIR_1200,0) + NVL(V_APUR_FECOP_RESSARCIR_1300,0) - NVL(V_APUR_FECOP_COMPLEMENTAR_1200,0)

                                           ELSE 0 END,'999999990.000')),'.',',')              ||'|'||

                REPLACE(LTRIM(TO_CHAR(CASE WHEN NVL(V_APUR_FECOP_RESSARCIR_1200,0) + NVL(V_APUR_FECOP_RESSARCIR_1300,0) - NVL(V_APUR_FECOP_COMPLEMENTAR_1200,0) < 0

                                           THEN ABS(NVL(V_APUR_FECOP_RESSARCIR_1200,0) + NVL(V_APUR_FECOP_RESSARCIR_1300,0) - NVL(V_APUR_FECOP_COMPLEMENTAR_1200,0))

                                           ELSE 0 END,'999999990.000')),'.',',');

   INSERT INTO ARQMAGNETICO

      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_SEQPROD,3,'0')||'9000'||LPAD(RTRIM(V_CONTREG),3,'0'), V_REG9000, P_NRSEQARQMAG);
   V_CONTREG :=  NVL(V_CONTREG,0) + 1;


   RETURN V_CONTREG;

END;
/