CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R08(
   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_CONTREG08        NUMBER;

BEGIN

 /*----------------------------------TIPO R08A-------------------------------------*/

   IF (P_PISPASEP = '3') THEN
      FOR C_REG_R08 IN (SELECT (RPAD('R08A',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(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR03,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD('0',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(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('0',318,'0')                                                                  ||
                                 LPAD('0',10,'0'))REG_R08A
                          FROM
                             (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 0.65) 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 = 0.65) AND (CDSITUTRIBITNFE NOT IN ('07','49','99'))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR02,
                                     (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.65))THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR03,
                                     (CASE WHEN ((A.CDSITUTRIBITNFE IN ('06')) OR ((A.CDSITUTRIBITNFE IN ('07')) AND (SUBSTR(LPAD(A.CDFISCOPERIT,4,'0'),2,3) IN ('109','110')))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR05,
                                      DECODE(A.CDSITUTRIBITNFE,'04',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR06,
                                     (CASE WHEN A.CDFISCOPERIT IN ('5551','6551','7551')THEN  TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR07,
                                     (CASE WHEN ((SUBSTR(A.CDFISCOPERIT,1,1)  =  '7') AND (CDSITUTRIBITNFE = '08')) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR08,
                                     (CASE WHEN (((SUBSTR(A.CDFISCOPERIT,1,1) <> '7') AND (CDSITUTRIBITNFE = '08')) OR ((A.CDSITUTRIBITNFE IN ('07')) AND (SUBSTR(LPAD(A.CDFISCOPERIT,4,'0'),2,3) NOT IN ('109','110')))) THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR09,
                                      DECODE(A.CDSITUTRIBITNFE,'09',A.VRCONTITIMP,LPAD('0',14,'0')) AS VR10,
                                     (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.00))THEN TO_CHAR(A.VRCONTITIMP) ELSE LPAD('0',14,'0') END) AS VR11,
                                      LPAD('0',14,'0') AS VR12,
                                      LPAD('0',14,'0') AS VR13
                                FROM
                                   (SELECT VRCONTITIMP, CDTPITEMPROD, CDSITUTRIBITNFE, CDFISCOPERIT,VRALIQITEM,CDNATUBCALPCIT, IP.CDPRODUTO
                                      FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, RELACPRODITGEN RE,IMPOSTO IM, NOTAFISCAL N, FILIAL F,
                                         (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                            FROM IMPOSTONF I, MAPAAPURAC M
                                           WHERE M.CDIMPOSTO     =  '50'
                                             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 = '50'
                                       AND IT.CDFILIAL        = IP.CDFILIAL
                                       AND IT.NRLANCTONF      = IP.NRLANCTONF
                                       AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                       AND IP.CDPRODUTO       = RE.CDPRODUTO
                                       AND RE.CDLIVROMAG      = P_LIVRO
                                       AND N.CDFILIAL         = IT.CDFILIAL
                                       AND N.NRLANCTONF       = IT.NRLANCTONF
                                       AND N.CDFILIAL         = NOTA.CDFILIAL
                                       AND N.NRLANCTONF       = NOTA.NRLANCTONF
                                       AND N.CDFILIEMIT       = F.CDFILIAL
                                       AND F.IDCUMUPISCOFIL   = 'N'
                                       AND N.IDENTRSAIDOP     = 'S'
                                   )A
                             )B
                         GROUP BY 'R08A', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R08A',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(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR03,0)),'000000000000.00'),'.')),'14','0')   ||
                                  LPAD('0',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(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('0',318,'0')                                                                  ||
                                  LPAD('0',10,'0'))REG_R08A
                           FROM
                              (SELECT  (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 0.65) 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 VR01,
                                       (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 0.65) 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 VR02,
                                       (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.65))THEN TO_CHAR(DECODE(A.IDENTRSAIDOP,'S',A.VRBASECALCIT,(0-A.VRBASECALCIT)))  ELSE LPAD('0',14,'0') END) AS VR03,
                                        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,
                                        LPAD('0',14,'0') AS VR12,
                                        LPAD('0',14,'0') AS VR13
                                 FROM
                                    (SELECT CDTPITEMPROD, VRBASECALCIT, VRALIQITEM, CDSITUTRIBITNFE, VRCONTITIMP, CDNATUBCALPCIT, IP.CDPRODUTO, N.IDENTRSAIDOP
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, RELACPRODITGEN RE,IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                                      FROM IMPOSTONF I, MAPAAPURAC M
                                                     WHERE M.CDIMPOSTO     =  '50'
                                                       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 = '50'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND IP.CDPRODUTO       = RE.CDPRODUTO
                                        AND RE.CDLIVROMAG      = P_LIVRO
                                        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   = 'N'
                                        AND ((N.IDENTRSAIDOP   = 'S') OR 
                                             ((N.IDENTRSAIDOP  = 'E') AND (IT.CDFISCOPERIT IN (SELECT CDCFOPPADRAO
                                                                                                 FROM CFOPPADRAO
                                                                                                WHERE IDTIPOCFOP = 'D'))))

                                    )A
                              )B
                         GROUP BY 'R08A', TO_CHAR(P_DTINICIO,'MM'),'01'

                         UNION ALL

                         SELECT (RPAD('R08A',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.VR03,0)),'000000000000.00'),'.')),'14','0')   ||
                                 LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VR01+B.VR02+B.VR03,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(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('0',318,'0')                                                                  ||
                                 LPAD('0',10,'0'))REG_R08A
                           FROM
                              (SELECT (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'B') AND (VRALIQITEM = 0.65) 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 VR01,
                                      (CASE WHEN ((CLASSIFICA_PRODUTOS_DACON('R',CDPRODUTO,P_LIVRO,CDNATUBCALPCIT,CDSITUTRIBITNFE) = 'O') AND (VRALIQITEM = 0.65) 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 VR02,
                                      (CASE WHEN ((A.CDSITUTRIBITNFE = '05')AND(VRALIQITEM = 0.65))THEN TO_CHAR (DECODE(A.IDENTRSAIDOP,'S',A.VRIMPOSTOIT,(0-A.VRIMPOSTOIT))) ELSE LPAD('0',14,'0') END) AS VR03,
                                       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,
                                       LPAD('0',14,'0') AS VR12,
                                       LPAD('0',14,'0') AS VR13
                                 FROM
                                    (SELECT CDTPITEMPROD,VRIMPOSTOIT,CDFISCOPERIT,VRALIQITEM,CDSITUTRIBITNFE,IP.CDPRODUTO,CDNATUBCALPCIT, VRCONTITIMP, N.IDENTRSAIDOP
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, RELACPRODITGEN RE,IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                             FROM IMPOSTONF I, MAPAAPURAC M
                                            WHERE M.CDIMPOSTO      =  '50'
                                              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 = '50'
                                        AND IT.CDFILIAL        = IP.CDFILIAL
                                        AND IT.NRLANCTONF      = IP.NRLANCTONF
                                        AND IT.NRSEQITPROD     = IP.NRSEQITPROD
                                        AND IP.CDPRODUTO       = RE.CDPRODUTO
                                        AND RE.CDLIVROMAG      = P_LIVRO
                                        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   = 'N'
                                        AND ((N.IDENTRSAIDOP   = 'S') OR 
                                             ((N.IDENTRSAIDOP  = 'E') AND (IT.CDFISCOPERIT IN (SELECT CDCFOPPADRAO
                                                                                                 FROM CFOPPADRAO
                                                                                                WHERE IDTIPOCFOP = 'D'))))
                                    )A
                              )B
                         GROUP BY 'R08A', TO_CHAR(P_DTINICIO,'MM'),'01'

           ) LOOP

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

           INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('10'||LPAD(RTRIM(V_CONTREG08), 8, '0'), C_REG_R08.REG_R08A, P_NRSEQARQMAG);


      END LOOP;
   END IF;

   RETURN(V_CONTREG08);
END;
/

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

