CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R30(
   P_CDEMPRESA              IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIO               IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFIM                  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG            IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER

AS
V_CONTREG30        NUMBER;
V_ORDEM            NUMBER;

BEGIN

 /*----------------------------------R30-------------------------------------*/
   V_ORDEM := 0;

   FOR C_REG_R30 IN (SELECT RPAD('R30',4,' ') AS PARTE1,
                                  (LPAD(VR03,14,' ')                                                                  ||
                                   NVL(VR04,'0000')                                                                   ||
                                   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('0',10,'0')) AS PARTE2
                         FROM
                            (SELECT DECODE (IDORIGDESTNF,'C',NRINSJURCLIE,NRINSJURFORN) AS VR03,
                                            '4407'AS VR04,
                                            (TO_CHAR(DECODE(A.CDIMPOSTONFE,'55',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '55', 'B'),
                                                                           '56',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '56', 'B')))) AS VR05,
                                            (TO_CHAR(DECODE(A.CDIMPOSTONFE,'55',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '55', 'I'),
                                                                           '56',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '56', 'I')))) AS VR06,
                                             LPAD('0',14,'0') AS VR07
                               FROM
                                  (SELECT FO.IDPRIPUBFORN, CL.IDPRIPUBCLIE,IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, N.IDORIGDESTNF, CL.NRINSJURCLIE, FO.NRINSJURFORN, IM.CDIMPOSTONFE
                                     FROM ITEMPROIMPOS IT, ITEMPRODUTO IP,IMPOSTO IM, NOTAFISCAL N, FILIAL F,FORNECEDOR FO, CLIENTE CL,
                                        (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                           FROM IMPOSTONF I, MAPAAPURAC M
                                         WHERE M.CDIMPOSTO     IN ('55','56')
                                           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    IN ('55','56')
                                       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 N.IDENTRSAIDOP     = 'S'
                                       AND N.CDFILIEMIT       = F.CDFILIAL
                                       AND N.CDFORNECED       = FO.CDFORNECED(+)
                                       AND N.CDCLIENTE        = CL.CDCLIENTE(+)
                                       AND ((FO.IDPRIPUBFORN   = 'B') OR (CL.IDPRIPUBCLIE = 'B'))
                                       AND NVL(VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '55', 'B') + VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '56', 'B'),0) > 0
                                      )A
                                  )B
                     GROUP BY 'R30',LPAD('0',10,'0'), VR03, VR04

                     UNION ALL

                        SELECT RPAD('R30',4,' ') AS PARTE1,
                               (LPAD(VR03,14,' ')                                                                 ||
                               NVL(VR04,'0000')                                                                   ||
                               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('0',10,'0')) AS PARTE2
                         FROM
                            (SELECT DECODE (IDORIGDESTNF,'C',NRINSJURCLIE,NRINSJURFORN) AS VR03,
                                           '4409'AS VR04,
                                           (TO_CHAR(DECODE(A.CDIMPOSTONFE,'65',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '65', 'B'),
                                                                          '66',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '66', 'B')))) AS VR05,
                                            LPAD('0',14,'0') AS VR06,
                                           (TO_CHAR(DECODE(A.CDIMPOSTONFE,'65',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '65', 'I'),
                                                                          '66',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '66', 'I')))) AS VR07
                               FROM
                              (SELECT FO.IDPRIPUBFORN, CL.IDPRIPUBCLIE,IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, N.IDORIGDESTNF, CL.NRINSJURCLIE, FO.NRINSJURFORN, IM.CDIMPOSTONFE
                                 FROM ITEMPROIMPOS IT, ITEMPRODUTO IP,IMPOSTO IM, NOTAFISCAL N, FILIAL F,FORNECEDOR FO, CLIENTE CL,
                                    (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                       FROM IMPOSTONF I, MAPAAPURAC M
                                     WHERE M.CDIMPOSTO     IN ('65','66')
                                       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    IN ('65','66')
                                  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 N.IDENTRSAIDOP     = 'S'
                                  AND N.CDFILIEMIT       = F.CDFILIAL
                                  AND N.CDFORNECED       = FO.CDFORNECED(+)
                                  AND N.CDCLIENTE        = CL.CDCLIENTE(+)
                                  AND ((FO.IDPRIPUBFORN   = 'B') OR (CL.IDPRIPUBCLIE = 'B'))
                                  AND NVL(VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '65', 'B') + VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '66', 'B'),0)> 0

                               )A
                           )B
                        GROUP BY 'R30',LPAD('0',10,'0'), VR03, VR04

                       UNION ALL

                        SELECT RPAD('R30',4,' ') AS PARTE1,
                               (LPAD(VR03,14,' ')                                                                 ||
                               NVL(VR04,'0000')                                                                   ||
                               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('0',10,'0')) AS PARTE2
                         FROM
                            (SELECT DECODE (IDORIGDESTNF,'C',NRINSJURCLIE,NRINSJURFORN) AS VR03,
                                           '5952' AS VR04,
                                           (TO_CHAR(DECODE(A.CDIMPOSTONFE,'65',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '65', 'B'),
                                                                          '66',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '66', 'B'),0))) AS VR05,
                                           (TO_CHAR(DECODE(A.CDIMPOSTONFE,'55',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '55', 'I'),
                                                                          '56',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '56', 'I'),0))) AS VR06,
                                           (TO_CHAR(DECODE(A.CDIMPOSTONFE,'65',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '65', 'I'),
                                                                          '66',VALOR_IMPOSTO_ITEM_NFE(A.CDFILIAL, A.NRLANCTONF, A.NRSEQITPROD, '66', 'I'),0))) AS VR07
                               FROM
                                  (SELECT FO.IDPRIPUBFORN, CL.IDPRIPUBCLIE,IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, N.IDORIGDESTNF, CL.NRINSJURCLIE, FO.NRINSJURFORN, IM.CDIMPOSTONFE
                                     FROM ITEMPROIMPOS IT, ITEMPRODUTO IP,IMPOSTO IM, NOTAFISCAL N, FILIAL F,FORNECEDOR FO, CLIENTE CL,
                                        (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                           FROM IMPOSTONF I, MAPAAPURAC M
                                         WHERE M.CDIMPOSTO     IN ('55','56','65','66')
                                           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    IN ('55','56','65','66')
                                      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 N.IDENTRSAIDOP     = 'S'
                                      AND N.CDFILIEMIT       = F.CDFILIAL
                                      AND N.CDFORNECED       = FO.CDFORNECED(+)
                                      AND N.CDCLIENTE        = CL.CDCLIENTE(+)
                                      AND ((FO.IDPRIPUBFORN  = 'V') OR (CL.IDPRIPUBCLIE = 'V'))
                                      AND NVL(VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '55', 'B') + VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '56', 'B'),0) > 0
                                      AND NVL(VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '65', 'B') + VALOR_IMPOSTO_ITEM_NFE(N.CDFILIAL, N.NRLANCTONF, IT.NRSEQITPROD, '66', 'B'),0) > 0

                                   )A
                               )B
                            GROUP BY 'R30',LPAD('0',10,'0'), VR03, VR04

                      ) LOOP

      V_CONTREG30 := NVL(V_CONTREG30,0) + 1;
      V_ORDEM     := NVL(V_ORDEM,0) + 1;

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('26'||LPAD(RTRIM(V_CONTREG30), 8, '0'), C_REG_R30.PARTE1||LPAD(LTRIM(TO_CHAR(V_ORDEM)),4,'0')||C_REG_R30.PARTE2, P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG30);

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DACON_R30','5.55.000'); END;
/
