CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R17(
   P_PISPASEP               IN VARCHAR2,
   P_DTINICIO               IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFIM                  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDEMPRESA              IN EMPRESA.CDEMPRESA%TYPE,
   P_LIVRO                  IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_NRSEQARQMAG            IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER

AS
   V_CONTREG17        NUMBER;

BEGIN

 /*----------------------------------TIPO R17A-------------------------------------*/


   IF (P_PISPASEP = '1') THEN
      FOR C_REG_R17A IN (SELECT (RPAD('R17A',4,' ')                                                                 ||
                                 TO_CHAR(P_DTINICIO,'MM')                                                           ||
                                 '01'                                                                               ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR07,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',346,'0')                                                                  ||
                                 LPAD('0',10,'0'))REG_R17A
                           FROM
                              (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR01,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR02,
                                      (CASE WHEN A.CDSITUTRIBITNFE IN ('06') THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR04,
                                       DECODE(A.CDSITUTRIBITNFE,'04',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR05,
                                      (CASE WHEN A.CDFISCOPERIT IN ('5551','6551','7551')THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR06,
                                      (CASE WHEN ((SUBSTR(A.CDFISCOPERIT,1,1)  = '7') AND (CDSITUTRIBITNFE = '08')) THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR07,
                                      (CASE WHEN (((SUBSTR(A.CDFISCOPERIT,1,1) <>'7') AND (CDSITUTRIBITNFE = '08')) OR (CDSITUTRIBITNFE = '07')) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR08,
                                       DECODE(A.CDSITUTRIBITNFE,'09',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR09,
                                      (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.00))THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR10,
                                      LPAD('0',14,'0') AS VR11
                                 FROM
                                    (SELECT VRCONTITIMP, CDSITUTRIBITNFE, CDFISCOPERIT,VRALIQITEM,IP.CDPRODUTO,CDNATUBCALPCIT
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO     =  '60'
                                              AND M.CDEMPRESA     =  P_CDEMPRESA
                                              AND M.DTINIPERIODO  >= P_DTINICIO
                                              AND M.DTFIMPERIODO  <= P_DTFIM
                                              AND M.NRSEQUMAPAPU  =  I.NRSEQUMAPAPU
                                              AND SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7') ) NOTA
                                      WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                        AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                        AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = '60'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND N.CDFILIAL      = IT.CDFILIAL
                                        AND N.NRLANCTONF    = IT.NRLANCTONF
                                        AND N.CDFILIAL      = NOTA.CDFILIAL
                                        AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                        AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                        AND F.IDCUMUPISCOFIL   = 'S'
                                    )A
                              )B
                         GROUP BY 'R17A', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R17A',4,' ')                                                                 ||
                                 TO_CHAR(P_DTINICIO,'MM')                                                           ||
                                 '02'                                                                               ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR07,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',346,'0')                                                                  ||
                                 LPAD('0',10,'0'))AS REG_R17A
                           FROM
                              (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRBASECALCIT) ELSE LPAD('0',14,'0') END) AS VR01,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRBASECALCIT) ELSE LPAD('0',14,'0') END) AS VR02,
                                      LPAD('0',14,'0') AS VR04,
                                      LPAD('0',14,'0') AS VR05,
                                      LPAD('0',14,'0') AS VR06,
                                      LPAD('0',14,'0') AS VR07,
                                      LPAD('0',14,'0') AS VR08,
                                      LPAD('0',14,'0') AS VR09,
                                      LPAD('0',14,'0') AS VR10,
                                      LPAD('0',14,'0') AS VR11
                                 FROM
                                    (SELECT  VRBASECALCIT, VRALIQITEM,IP.CDPRODUTO,CDNATUBCALPCIT,VRCONTITIMP,CDSITUTRIBITNFE
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO     =  '60'
                                              AND M.CDEMPRESA     =  P_CDEMPRESA
                                              AND M.DTINIPERIODO  >= P_DTINICIO
                                              AND M.DTFIMPERIODO  <= P_DTFIM
                                              AND M.NRSEQUMAPAPU  =  I.NRSEQUMAPAPU
                                              AND SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7') ) NOTA
                                      WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                        AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                        AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = '60'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND N.CDFILIAL      = IT.CDFILIAL
                                        AND N.NRLANCTONF    = IT.NRLANCTONF
                                        AND N.CDFILIAL      = NOTA.CDFILIAL
                                        AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                        AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                        AND F.IDCUMUPISCOFIL   = 'S'
                                    )A
                              )B
                         GROUP BY 'R17A', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R17A',4,' ')                                                                     ||
                                 TO_CHAR(P_DTINICIO,'MM')                                                               ||
                                '03'                                                                                    ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01+B.VR02,0)),'000000000000.00'),'.')),'14','0')||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR07,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD('0',346,'0')                                                                      ||
                                 LPAD('0',10,'0'))AS REG_R17A
                           FROM
                              (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRIMPOSTOIT) ELSE LPAD('0',14,'0') END) AS VR01,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRIMPOSTOIT) ELSE LPAD('0',14,'0') END) AS VR02,
                                      LPAD('0',14,'0') AS VR04,
                                      LPAD('0',14,'0') AS VR05,
                                      LPAD('0',14,'0') AS VR06,
                                      LPAD('0',14,'0') AS VR07,
                                      LPAD('0',14,'0') AS VR08,
                                      LPAD('0',14,'0') AS VR09,
                                      LPAD('0',14,'0') AS VR10,
                                      LPAD('0',14,'0') AS VR11
                                 FROM
                                    (SELECT VRIMPOSTOIT,CDFISCOPERIT,VRALIQITEM,IP.CDPRODUTO,CDNATUBCALPCIT,VRCONTITIMP,CDSITUTRIBITNFE
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM, NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO      =  '60'
                                              AND M.CDEMPRESA      =  P_CDEMPRESA
                                              AND M.DTINIPERIODO  >=  P_DTINICIO
                                              AND M.DTFIMPERIODO  <=  P_DTFIM
                                              AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
                                              AND SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7')) NOTA
                                      WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                        AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                        AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = '60'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND N.CDFILIAL      = IT.CDFILIAL
                                        AND N.NRLANCTONF    = IT.NRLANCTONF
                                        AND N.CDFILIAL      = NOTA.CDFILIAL
                                        AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                        AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                        AND F.IDCUMUPISCOFIL   = 'S'
                                    )A
                              )B
                         GROUP BY 'R17A', TO_CHAR(P_DTINICIO,'MM'),'01'



       ) LOOP

       V_CONTREG17 := NVL(V_CONTREG17,0) + 1;

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('16'||LPAD(RTRIM(V_CONTREG17), 8, '0'), C_REG_R17A.REG_R17A, P_NRSEQARQMAG);

      END LOOP;

   ELSIF(P_PISPASEP = '2') THEN

 /*----------------------------------TIPO R17B-------------------------------------*/


      FOR C_REG_R17B IN (SELECT (RPAD('R17B',4,' ')                                                                 ||
                                TO_CHAR(P_DTINICIO,'MM')                                                            ||
                                '01'                                                                                ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR12,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR13,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR14,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR15,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',290,'0')                                                                  ||
                                 LPAD('0',10,'0'))REG_R17B
                          FROM
                             (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR01,
                                     (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR02,
                                     (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 3.0) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR04,
                                     (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 3.0) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR05,
                                     (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 3.0))THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR06,
                                      LPAD('0',14,'0') AS VR07,
                                     (CASE WHEN A.CDSITUTRIBITNFE IN ('06')THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR08,
                                      DECODE(A.CDSITUTRIBITNFE,'04',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR09,
                                     (CASE WHEN A.CDFISCOPERIT IN ('5551','6551','7551')THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR10,
                                     (CASE WHEN ((SUBSTR(A.CDFISCOPERIT,1,1)  =  '7') AND (CDSITUTRIBITNFE = '08')) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR11,
                                     (CASE WHEN (((SUBSTR(A.CDFISCOPERIT,1,1) <> '7') AND (CDSITUTRIBITNFE = '08')) OR (CDSITUTRIBITNFE = '07')) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR12,
                                      DECODE(A.CDSITUTRIBITNFE,'09',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR13,
                                     (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.00))THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR14,
                                      LPAD('0',14,'0') AS VR15
                               FROM
                                  (SELECT VRCONTITIMP, CDSITUTRIBITNFE, CDFISCOPERIT,VRALIQITEM,IP.CDPRODUTO,CDNATUBCALPCIT
                                     FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                        (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                           FROM IMPOSTONF I, MAPAAPURAC M
                                          WHERE M.CDIMPOSTO     =  '60'
                                            AND M.CDEMPRESA     =  P_CDEMPRESA
                                            AND M.DTINIPERIODO  >= P_DTINICIO
                                            AND M.DTFIMPERIODO  <= P_DTFIM
                                            AND M.NRSEQUMAPAPU  =  I.NRSEQUMAPAPU
                                            AND SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7')) NOTA
                                    WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                      AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                      AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE = '60'
                                      AND IT.CDFILIAL        = IP.CDFILIAL
                                      AND IT.NRLANCTONF      = IP.NRLANCTONF
                                      AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                      AND N.CDFILIAL      = IT.CDFILIAL
                                      AND N.NRLANCTONF    = IT.NRLANCTONF
                                      AND N.CDFILIAL      = NOTA.CDFILIAL
                                      AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                      AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                  )A
                             )B
                         GROUP BY 'R17B', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R17B',4,' ')                                                                 ||
                                 TO_CHAR(P_DTINICIO,'MM')                                                           ||
                                 '02'                                                                               ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',14,'0')                                                                   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR12,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR13,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR14,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR15,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',290,'0')                                                                                     ||
                                 LPAD('0',10,'0'))REG_R17B
                           FROM
                              (SELECT  (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,0)) ELSE LPAD('0',14,'0') END) AS VR01,
                                       (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,0)) ELSE LPAD('0',14,'0') END) AS VR02,
                                       (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 3.0) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,(0-A.VRBASECALCIT))) ELSE LPAD('0',14,'0') END) AS VR04,
                                       (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 3.0) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,(0-A.VRBASECALCIT))) ELSE LPAD('0',14,'0') END) AS VR05,
                                       (CASE WHEN ((A.CDSITUTRIBITNFE = '05') AND (VRALIQITEM = 3.00) AND (CDFISCALIT = '1'))THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,(0-A.VRBASECALCIT))) ELSE LPAD('0',14,'0') END) AS VR06,
                                       LPAD('0',14,'0') AS VR07,
                                       LPAD('0',14,'0') AS VR08,
                                       LPAD('0',14,'0') AS VR09,
                                       LPAD('0',14,'0') AS VR10,
                                       LPAD('0',14,'0') AS VR11,
                                       LPAD('0',14,'0') AS VR12,
                                       LPAD('0',14,'0') AS VR13,
                                       LPAD('0',14,'0') AS VR14,
                                       LPAD('0',14,'0') AS VR15
                                 FROM
                                    (SELECT VRBASECALCIT, VRALIQITEM, CDSITUTRIBITNFE, VRCONTITIMP, CDFISCALIT, IP.CDPRODUTO, CDNATUBCALPCIT, N.IDENTRSAIDOP
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO     =  '60'
                                              AND M.CDEMPRESA     =  P_CDEMPRESA
                                              AND M.DTINIPERIODO  >= P_DTINICIO
                                              AND M.DTFIMPERIODO  <= P_DTFIM
                                              AND M.NRSEQUMAPAPU  =  I.NRSEQUMAPAPU) NOTA
                                      WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                        AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                        AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = '60'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND N.CDFILIAL      = IT.CDFILIAL
                                        AND N.NRLANCTONF    = IT.NRLANCTONF
                                        AND N.CDFILIAL      = NOTA.CDFILIAL
                                        AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                        AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                        AND ((N.IDENTRSAIDOP   = 'S') OR
                                             ((N.IDENTRSAIDOP  = 'E') AND (IT.CDFISCOPERIT IN (SELECT CDCFOPPADRAO
                                                                                                 FROM CFOPPADRAO
                                                                                                WHERE IDTIPOCFOP = 'D'))))
                                    )A
                              )B
                         GROUP BY 'R17B', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R17B',4,' ')                                                                            ||
                                  TO_CHAR(P_DTINICIO,'MM')                                                                     ||
                                 '03'                                                                                          ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR02,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01+B.VR02,0)),'000000000000.00'),'.')),'14','0')       ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR05,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR06,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR04+B.VR05+B.VR06,0)),'000000000000.00'),'.')),'14','0')||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR08,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR09,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR10,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR11,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR12,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR13,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR14,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR15,0)),'000000000000.00'),'.')),'14','0')              ||
                                 LPAD('0',290,'0')                                                                             ||
                                 LPAD('0',10,'0'))REG_R17B
                           FROM
                              (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 7.6) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,0)) ELSE LPAD('0',14,'0') END) AS VR01,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 7.6) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,0)) ELSE LPAD('0',14,'0') END) AS VR02,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 3.0) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,(0-A.VRIMPOSTOIT))) ELSE LPAD('0',14,'0') END) AS VR04,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 3.0) AND (CDFISCALIT = '1') AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,(0-A.VRIMPOSTOIT))) ELSE LPAD('0',14,'0') END) AS VR05,
                                      (CASE WHEN ((A.CDSITUTRIBITNFE = '05') AND (VRALIQITEM = 3.00) AND (CDFISCALIT = '1'))THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,(0-A.VRIMPOSTOIT))) ELSE LPAD('0',14,'0') END) AS VR06,
                                      (CASE WHEN ((SUBSTR(A.CDFISCOPERIT,1,1) =  '7') AND (VRALIQITEM = 3.0) AND (CDFISCALIT = '1'))  THEN  TO_CHAR(A.VRIMPOSTOIT) ELSE LPAD('0',14,'0') END) AS VR07,
                                      LPAD('0',14,'0') AS VR08,
                                      LPAD('0',14,'0') AS VR09,
                                      LPAD('0',14,'0') AS VR10,
                                      LPAD('0',14,'0') AS VR11,
                                      LPAD('0',14,'0') AS VR12,
                                      LPAD('0',14,'0') AS VR13,
                                      LPAD('0',14,'0') AS VR14,
                                      LPAD('0',14,'0') AS VR15
                                 FROM
                                    (SELECT VRIMPOSTOIT, CDFISCOPERIT, VRALIQITEM, CDFISCALIT, CDSITUTRIBITNFE, VRCONTITIMP, IP.CDPRODUTO, CDNATUBCALPCIT, N.IDENTRSAIDOP
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO      =  '60'
                                              AND M.CDEMPRESA      =  P_CDEMPRESA
                                              AND M.DTINIPERIODO  >=  P_DTINICIO
                                              AND M.DTFIMPERIODO  <=  P_DTFIM
                                              AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU) NOTA
                                      WHERE IT.CDFILIAL        = NOTA.CDFILIAL
                                        AND IT.NRLANCTONF      = NOTA.NRLANCTONF
                                        AND IT.CDIMPOSTO       = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = '60'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND N.CDFILIAL      = IT.CDFILIAL
                                        AND N.NRLANCTONF    = IT.NRLANCTONF
                                        AND N.CDFILIAL      = NOTA.CDFILIAL
                                        AND N.NRLANCTONF    = NOTA.NRLANCTONF
                                        AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                                        AND ((N.IDENTRSAIDOP   = 'S') OR
                                             ((N.IDENTRSAIDOP  = 'E') AND (IT.CDFISCOPERIT IN (SELECT CDCFOPPADRAO
                                                                                                 FROM CFOPPADRAO
                                                                                                WHERE IDTIPOCFOP = 'D'))))
                                    )A
                              )B
                         GROUP BY 'R17B', TO_CHAR(P_DTINICIO,'MM'),'01'

                       ) LOOP

         V_CONTREG17 := NVL(V_CONTREG17,0) + 1;

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('18'||LPAD(RTRIM(V_CONTREG17), 8, '0'), C_REG_R17B.REG_R17B, P_NRSEQARQMAG);

      END LOOP;

   END IF;
   RETURN(V_CONTREG17);

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DACON_R17','5.60.000'); END;
/