CREATE OR REPLACE FUNCTION ARQUIVO_DIC_51(
   P_LIVRO        IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   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_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE,
   P_SEQUENCIAL   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CONTADOR     IN NUMBER,
   P_STTOTAL      IN VARCHAR2,
   P_CONTGERAL    IN NUMBER) RETURN VARCHAR2
AS
   V_STTOTAL        VARCHAR2(4000);
   V_CONTGERAL      NUMBER;
   V_CONTREG        NUMBER;
   V_CONTADOR       NUMBER;
BEGIN
   V_CONTGERAL := NVL(P_CONTGERAL,0);
   V_CONTREG := 0;

   V_CONTADOR := P_CONTADOR;
   FOR C_REGISTRO51 IN (SELECT '51'             ||
                               NRINSJ           ||
                               NRINSEST         ||
                               DATA             ||
                               SGESTADO         ||
                               SERIE            ||
                               NRNOTAFISC       ||
                               CDFISCOPERNF     ||
                               VRCONTABILNF     ||
                               VRIMPOSTONF      ||
                               VRISENTRIB       ||
                               VROUTCRED        ||
                               RPAD(' ',20, ' ')||
                               IDSTATUS         ||
                               VRBASECALCNF     ||
                               RPAD(' ',45, ' ') AS DSARQUMAG, DATA
                        FROM(
                           SELECT '51',
                                  LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0') AS NRINSJ,
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ') AS NRINSEST,
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                  RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISC,
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRCONTABILNF, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF, '00000000000.00'),'.','')) AS VRBASECALCNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.',''))   AS VRIMPOSTONF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.',''))   AS VRISENTRIB,
                                  LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.',''))   AS VROUTCRED,
                                  DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                           FROM
                              FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                              (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                                      N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                      SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                 FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P,
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                      FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO I
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND
                                            M.CDINSCESTA   = P_CDINSCESTA    AND
                                            M.DTINIPERIODO = P_DTINICIAL     AND
                                            M.DTFIMPERIODO = P_DTFINAL       AND
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                            IP.CDIMPOSTO   = I.CDIMPOSTO     AND
                                            I.CDIMPOSTONFE = P_CDIMPOSTO) IC

                                WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND
                                      N.CDINSCESTA      = P_CDINSCESTA                  AND
                                      N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL    AND
                                      N.IDSTATUS   IN ('A', 'E')                        AND
                                      N.CDFILIAL        = I.CDFILIAL                    AND
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND
                                      P.CDIMPOSTONFE    = '10'                          AND
                                      I.CDIMPOSTO       = P.CDIMPOSTO                   AND
                                      I.NRSEQUMAPAPU IS NOT NULL                        AND
                                      I.CDFILIAL        = IC.CDFILIAL                   AND
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                    AND
                                      NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'        AND
                                      N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                                      NVL(MD.CDTPREGFISC, '50') IN ('50', '51')
                                GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                                         N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF

                           WHERE FO.CDFORNECED(+)   = NF.CDFORNECED            AND
                                 FA.CDFORNECED      = NF.CDFORNECED            AND
                                 EF.CDFORNECED(+)   = FA.CDFORNECED            AND
                                 EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P') AND
                                 EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS)  AND
                                 (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0)

                           UNION ALL
                           SELECT '51',
                                  '00000000000000' AS NRINSJ,
                                  'ISENTO        ' AS NRINSEST,
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                  'EX' AS SGESTADO,
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISC,
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRCONTABILNF, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF, '00000000000.00'),'.','')) AS VRBASECALCNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.',''))   AS VRIMPOSTONF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.',''))   AS VRISENTRIB,
                                  LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.',''))   AS VROUTCRED,
                                  DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                           FROM
                              FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                              (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                                      N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                      SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P,
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                      FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO I
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND
                                            M.CDINSCESTA   = P_CDINSCESTA    AND
                                            M.DTINIPERIODO = P_DTINICIAL     AND
                                            M.DTFIMPERIODO = P_DTFINAL       AND
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                            IP.CDIMPOSTO   = I.CDIMPOSTO     AND
                                            I.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND
                                      N.CDINSCESTA      = P_CDINSCESTA                  AND
                                      N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL    AND
                                      N.IDSTATUS   IN ('A', 'E')                        AND
                                      N.CDFILIAL        = I.CDFILIAL                    AND
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND
                                      P.CDIMPOSTONFE    = '10'                          AND
                                      I.CDIMPOSTO       = P.CDIMPOSTO                   AND
                                      I.CDFILIAL        = IC.CDFILIAL                   AND
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                    AND
                                      I.NRSEQUMAPAPU IS NOT NULL                        AND
                                      SUBSTR(I.CDFISCOPERNF,1,1) = '3'                  AND
                                      N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                                      NVL(MD.CDTPREGFISC, '50') IN ('50', '51')
                                GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                                         N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                           WHERE FO.CDFORNECED(+)   = NF.CDFORNECED            AND
                                 FA.CDFORNECED      = NF.CDFORNECED            AND
                                 EF.CDFORNECED(+)   = FA.CDFORNECED            AND
                                 EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P') AND
                                 EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS)  AND
                                 (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0)

                           UNION ALL

                           SELECT '51',
                                  LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0')AS NRINSJ,
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                  RPAD(NVL(EC.SGESTADO,' '),2,' ') AS SGESTADO,
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISC,
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF,'00000000000.00'),'.','')) AS VRBASECALCNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.','')) AS VROUTRCRED,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.','')) AS VRISENTRIB,
                                  DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                           FROM
                              CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                              (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                                      N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                      SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P,
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                      FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO I
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND
                                            M.CDINSCESTA   = P_CDINSCESTA    AND
                                            M.DTINIPERIODO = P_DTINICIAL     AND
                                            M.DTFIMPERIODO = P_DTFINAL       AND
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                            IP.CDIMPOSTO   = I.CDIMPOSTO     AND
                                            I.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND
                                      N.CDINSCESTA      = P_CDINSCESTA                  AND
                                      N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL     AND
                                      N.IDSTATUS   IN ('A', 'E')                        AND
                                      N.CDFILIAL        = I.CDFILIAL                    AND
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND
                                      P.CDIMPOSTONFE    = '10'                          AND
                                      I.CDIMPOSTO       = P.CDIMPOSTO                   AND
                                      I.NRSEQUMAPAPU IS NOT NULL                        AND
                                      I.CDFILIAL        = IC.CDFILIAL                   AND
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                    AND
                                      NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'        AND
                                      N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                                      NVL(MD.CDTPREGFISC, '50') IN ('50', '51')
                                GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                                         N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                           WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE             AND
                                 CA.CDCLIENTE       = NF.CDCLIENTE             AND
                                 EC.CDCLIENTE(+)    = CA.CDCLIENTE             AND
                                 EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P') AND
                                 EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS)  AND
                                 (INSTR(NVL(P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0)
                           UNION ALL

                           SELECT '51',
                                  '00000000000000' AS NRINSJ,
                                  'ISENTO        'AS NRINSEST,
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                  'EX' AS SGESTADO,
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISC,
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF, '00000000000.00'),'.','')) AS VRBASECALCNF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                  LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.','')) AS VROUTRCRED,
                                  LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.','')) AS VRISENTRIB,
                                  DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                           FROM
                              CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                              (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                                      N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                      SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                      SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P,
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                      FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO I
                                      WHERE M.CDEMPRESA     = P_CDEMPRESA     AND
                                            M.CDINSCESTA    = P_CDINSCESTA    AND
                                            M.DTINIPERIODO  = P_DTINICIAL     AND
                                            M.DTFIMPERIODO  = P_DTFINAL       AND
                                            M.CDIMPOSTO     = P_CDIMPOSTO     AND
                                            M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU AND
                                            IP.CDIMPOSTO    = I.CDIMPOSTO     AND
                                            I.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND
                                      N.CDINSCESTA      = P_CDINSCESTA                  AND
                                      N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL     AND
                                      N.IDSTATUS   IN ('A', 'E')                        AND
                                      N.CDFILIAL        = I.CDFILIAL                    AND
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND
                                      P.CDIMPOSTONFE    = '10'                          AND
                                      I.CDIMPOSTO       = P.CDIMPOSTO                   AND
                                      I.NRSEQUMAPAPU IS NOT NULL                        AND
                                      I.CDFILIAL        = IC.CDFILIAL                   AND
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                    AND
                                      SUBSTR(I.CDFISCOPERNF,1,1) = '7'                  AND
                                      N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                                      NVL(MD.CDTPREGFISC, '50') IN ('50', '51')
                                GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                                         N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                           WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE             AND
                                 CA.CDCLIENTE       = NF.CDCLIENTE             AND
                                 EC.CDCLIENTE(+)    = CA.CDCLIENTE             AND
                                 EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P') AND
                                 EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS) AND
                                 (INSTR(NVL(P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0)) NF

                        ORDER BY DATA) LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO51.DSARQUMAG, P_SEQUENCIAL);

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      V_CONTREG   := NVL(V_CONTREG,0)+1;
      V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
   END LOOP;

   IF V_CONTREG > 0 THEN
     V_STTOTAL := P_STTOTAL||'51'||LPAD(RTRIM(TO_CHAR(V_CONTREG) ),8,'0');
     RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0))||V_STTOTAL);     
   END IF;

   IF V_CONTREG = 0 THEN
      RETURN('0');
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DIC_51','5.30.000'); END;
/

