CREATE OR REPLACE FUNCTION SPED_BLOCO9(
   P_VERSAO       IN VARCHAR2,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_LIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_ORIGBLOCOK   IN DADOSGERSPED.IDORIGBLOCOK%TYPE,
   P_CONTREGA     IN NUMBER,
   P_DTINICIAL    DATE,
   P_DTFINAL      DATE) RETURN NUMBER
AS                                                                                                       
   V_MOVIMENT     NUMBER;
   V_TOTREG9      NUMBER;
   V_CONTREG9     NUMBER;
   V_TOTREG       NUMBER;
   V_CONTREGA     NUMBER;
   V_SEQUPROD     NUMBER;
   V_QUANTANTES   NUMBER;
   V_QUANTDEPOIS  NUMBER;
   V_0200         VARCHAR2(400);
   V_0150         VARCHAR2(4000);
   V_0175         VARCHAR2(4000);
   V_UNID         VARCHAR2(400);
   V_UNIDADE      VARCHAR2(400);
   V_CDARVPROD    VARCHAR2(400);
   V_CONTCTBL     VARCHAR2(100);
   V_CDCHAVEREG   VARCHAR2(4);
   V_DSCFOPPADRAO VARCHAR2(500);
   V_VAFMG        VARCHAR2(01);
   V_UNIDADEVAF   VARCHAR2(01);
   V_SGUNID       VARCHAR2(10);
   V_PRODUTO      VARCHAR2(10);
BEGIN

   V_VAFMG      := 'N';
   V_UNIDADEVAF := 'N';
   
   /*CONTADOR DA TABELA TMP_REGDADOSSPED*/
   BEGIN
      SELECT COUNT(CDCHAVEREG)
        INTO V_SEQUPROD
        FROM TMP_REGDADOSSPED;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_SEQUPROD:= 0;
   END;

   /* REGISTRO 0150 - Cadastro do Participante*/
   V_CONTREGA := NVL(P_CONTREGA,0);
   FOR C_0150 IN (SELECT CDCHAVEREG
                    FROM TMP_REGDADOSSPED
                   WHERE CDREGISTRO = '0150'
                     AND CDCHAVEREG IS NOT NULL
                     AND LENGTH(CDCHAVEREG) > 3
                   ORDER BY CDCHAVEREG) LOOP

      SELECT MAX('|0150'                            || '|' ||
             REPLACE(REPLACE(REPLACE(REPLACE(CODIGO,'.'),'-'),'/'),' ')                || '|' ||
             TRIM(REPLACE(REPLACE(LTRIM(RTRIM(NMRAZAO)), CHR(10), ' ' ), CHR(9), ' ')) || '|' ||
             LPAD(CDPAIS,5,'0')                     || '|' ||
             CNPJ1                                  || '|' ||
             CPF                                    || '|' ||
             INSCESTA                               || '|' ||
             MUNICIPIO                              || '|' ||
             SUFRAMA                                || '|' ||
             RETIRA_CARACTER_ESPECIAL(ENDERECO,'S') || '|' ||
             RETIRA_CARACTER_ESPECIAL(NUMERO,'S')   || '|' ||
             SUBSTR(RETIRA_CARACTER_ESPECIAL(COMPLEMENT,'S'),1,60) || '|' ||
             RETIRA_CARACTER_ESPECIAL(BAIRRO,'S')   || '|' )
        INTO V_0150
        FROM (SELECT 'L'||FI.CDFILIAL                   AS CODIGO,
                     NVL(FI.NMRAZSOCFILI, FI.NMFILIAL)  AS NMRAZAO,
                     DECODE(FI.IDENDEMISFIL,'I',PI.CDPAISBACEN,PA.CDPAISBACEN) AS CDPAIS,
                     REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.',''),'/',''),'-','') AS CNPJ1,
                     ''                                 AS CPF,
                     ''                                 AS CEI,
                     ''                                 AS NIT,
                     DECODE(FI.IDENDEMISFIL,'I',I.SGESTADO,EF.SGESTADO) AS ESTADO,
                     TRIM(DECODE(FI.CDINSCESTA, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FI.CDINSCESTA,'.',''),'/',''),'-',''))) AS INSCESTA,
                     ''                                 AS INSCESTST,
                     DECODE(DECODE(FI.IDENDEMISFIL,'I',PI.CDPAISBACEN,PA.CDPAISBACEN),'1058',LPAD(NVL(DECODE(FI.IDENDEMISFIL,'I',MI.CDMUNICIBGE,M.CDMUNICIBGE),'0'),7,'0'),'') AS MUNICIPIO,
                     ''                                 AS INSCMUNI,
                     ''                                 AS SUFRAMA,
                     FI.CDFILIAL                        AS CNPJ,
                     DECODE(FI.IDENDEMISFIL,'I',NVL(I.DSENDEIESTSEP,I.DSENDEIEST),NVL(EF.DSENDEFILISEP,EF.DSENDEFILI)) AS ENDERECO,
                     DECODE(FI.IDENDEMISFIL,'I',I.NRENDEIESTSEP,EF.NRENDEFILISEP) AS NUMERO,
                     DECODE(FI.IDENDEMISFIL,'I',I.DSCOMPENDIES,EF.DSCOMPENDFIL) AS COMPLEMENT,
                     DECODE(FI.IDENDEMISFIL,'I',I.NMBAIRIEST,EF.NMBAIRFILI) AS BAIRRO
                FROM FILIAL FI, ENDEFILI EF, MUNICIPIO M, PAIS PA, INSCRESTAD I, PAIS PI, MUNICIPIO MI
               WHERE EF.CDFILIAL        = FI.CDFILIAL
                 AND ((EF.IDTPENDEFILI  = NVL(FI.IDENDEMISFIL,'P')) OR (FI.IDENDEMISFIL = 'I' AND EF.IDTPENDEFILI = 'P'))
                 AND EF.CDPAIS          = M.CDPAIS
                 AND EF.SGESTADO        = M.SGESTADO
                 AND EF.CDMUNICIPIO     = M.CDMUNICIPIO
                 AND EF.CDPAIS          = PA.CDPAIS
                 AND 'L'||FI.CDFILIAL   = C_0150.CDCHAVEREG
                 AND FI.CDINSCESTA      = I.CDINSCESTA
                 AND I.CDPAIS           = MI.CDPAIS
                 AND I.SGESTADO         = MI.SGESTADO
                 AND I.CDMUNICIPIO      = MI.CDMUNICIPIO
                 AND I.CDPAIS           = PI.CDPAIS
               UNION
              SELECT 'C'||CL.CDCLIENTE                             AS CODIGO,
                     CL.NMRAZSOCCLIE                               AS NMRAZAO,
                     PA.CDPAISBACEN                                AS CDPAIS,
                     DECODE(LENGTH(REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')), 14, REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')) AS CNPJ1,
                     DECODE(LENGTH(REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')), 11, REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')) AS CPF,
                     ''                                            AS CEI,
                     ''                                            AS NIT,
                     EC.SGESTADO                                   AS ESTADO,
                     TRIM(DECODE(CL.NRINSESTCLIE, 'ISENTO', '', REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),'/',''),'-',''))) AS INSCESTA,
                     ''                                            AS INSCESTST,
                     DECODE(PA.CDPAISBACEN,'1058',LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0'),'') AS MUNICIPIO,
                     ''                                            AS INSCMUNI,
                     ''                                            AS SUFRAMA,
                     CL.CDCLIENTE                                  AS CNPJ,
                     NVL(EC.DSENDECLIESEP,EC.DSENDECLIE)           AS ENDERECO,
                     EC.NRENDECLIESEP                              AS NUMERO,
                     EC.DSCOMPENDCLI                               AS COMPLEMENT,
                     EC.NMBAIRCLIE                                 AS BAIRRO
                FROM CLIENTE CL, ENDECLIE EC, MUNICIPIO M, PAIS PA
               WHERE EC.CDCLIENTE       = CL.CDCLIENTE
                 AND EC.IDTPENDECLIE    = NVL(CL.IDENDEMISCLI,'P')
                 AND EC.CDPAIS          = M.CDPAIS
                 AND EC.SGESTADO        = M.SGESTADO
                 AND EC.CDMUNICIPIO     = M.CDMUNICIPIO
                 AND EC.CDPAIS          = PA.CDPAIS
                 AND 'C'||CL.CDCLIENTE  = C_0150.CDCHAVEREG
               UNION
              SELECT 'F'||FO.CDFORNECED                               AS CODIGO,
                     FO.NMRAZSOCFORN                                  AS NMRAZAO,
                     PA.CDPAISBACEN                                   AS CDPAIS,
                     DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')), 14, REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')) AS CNPJ1,
                     DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')), 11, REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')) AS CPF,
                     ''                                               AS CEI,
                     ''                                               AS NIT,
                     EF.SGESTADO                                      AS ESTADO,
                     TRIM(DECODE(FO.NRINSESTFORN, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),'/',''),'-',''))) AS INSCESTA,
                     ''                                               AS INSCESTST,
                     DECODE(PA.CDPAISBACEN,'1058',LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0'),'') AS MUNICIPIO,
                     REPLACE(REPLACE(FO.NRINSMUNFORN,'-',''),'/','')  AS INSCMUNI,
                     ''                                               AS SUFRAMA,
                     FO.CDFORNECED                                    AS CNPJ,
                     NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN)              AS ENDERECO,
                     EF.NRENDEFORNSEP                                 AS NUMERO,
                     EF.DSCOMPENDFOR                                  AS COMPLEMENT,
                     EF.NMBAIRFORN                                    AS BAIRRO
                FROM FORNECEDOR FO, ENDEFORN EF, MUNICIPIO M, PAIS PA
               WHERE EF.CDFORNECED      = FO.CDFORNECED
                 AND EF.IDTPENDEFORN    = NVL(FO.IDENDEMISFOR,'P')
                 AND EF.CDPAIS          = M.CDPAIS
                 AND EF.SGESTADO        = M.SGESTADO
                 AND EF.CDMUNICIPIO     = M.CDMUNICIPIO
                 AND EF.CDPAIS          = PA.CDPAIS
                 AND 'F'||FO.CDFORNECED = C_0150.CDCHAVEREG
               UNION
              SELECT T.CDADMINCART     AS CODIGO,
                     NVL(T.NMRAZSOCADMCART,T.NMTIPORECE) AS NMRAZAO,
                     P.CDPAISBACEN     AS CDPAIS,
                     REPLACE(REPLACE(REPLACE(T.CDADMINCART,'.',''),'/',''),'-','') AS CNPJ1,
                     ''                AS CPF,
                     ''                AS CEI,
                     ''                AS NIT,
                     T.SGESTADO        AS ESTADO,
                     ''                AS INSCESTA,
                     ''                AS INSCESTST,
                     DECODE(P.CDPAISBACEN,'1058',M.CDMUNICIBGE,'') AS MUNICIPIO,
                     ''                AS INSCMUNI,
                     ''                AS SUFRAMA,
                     ''                AS CNPJ,
                     T.DSLOGRECE||' '||T.DSENDERECE AS ENDERECO,
                     T.NRENDERECE      AS NUMERO,
                     T.DSCOMPLERECE    AS COMPLEMENT,
                     B.NMBAIRRO        AS BAIRRO
                FROM TIPORECE T, MUNICIPIO M, BAIRRO B, PAIS P
               WHERE T.CDADMINCART = C_0150.CDCHAVEREG
                 AND T.CDPAIS      = M.CDPAIS(+)
                 AND T.SGESTADO    = M.SGESTADO(+)
                 AND T.CDMUNICIPIO = M.CDMUNICIPIO(+)
                 AND T.CDPAIS      = B.CDPAIS(+)
                 AND T.SGESTADO    = B.SGESTADO(+)
                 AND T.CDMUNICIPIO = B.CDMUNICIPIO(+)
                 AND T.CDBAIRRO    = B.CDBAIRRO(+)
                 AND T.CDPAIS      = P.CDPAIS(+));

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('06'||LPAD(RTRIM(V_CONTREGA), 8, '0'), NVL(V_0150,C_0150.CDCHAVEREG), P_NRSEQARQMAG);

      /* REGISTRO 0175 - Alteracao Cadastro de Participante */
      FOR C_0175 IN (SELECT MAX(DTALTERACAO) AS DTALTERACAO, CDCHAVE, CDCAMPO
                       FROM ALTERAPARTSPED
                      WHERE CDCHAVE       = C_0150.CDCHAVEREG
                        AND DTALTERACAO   BETWEEN P_DTINICIAL AND P_DTFINAL
                        AND DSCONTEUDOANT IS NOT NULL
                   GROUP BY CDCHAVE, CDCAMPO) LOOP

         IF (C_0175.CDCAMPO = '03' OR C_0175.CDCAMPO = '05' OR C_0175.CDCAMPO = '06' OR C_0175.CDCAMPO = '09') THEN
            SELECT ('|0175'                             || '|' ||
                   TO_CHAR(MAX(DTALTERACAO),'ddmmyyyy') || '|' ||
                   CDCAMPO                              || '|' ||
                   MAX(TRIM(REPLACE(REPLACE(REPLACE(RETIRA_CARACTER_ESPECIAL(DSCONTEUDOANT,'S'),'.'),'/'),'-'))) || '|') AS C0175
              INTO V_0175
              FROM ALTERAPARTSPED
             WHERE CDCHAVE     = C_0175.CDCHAVE
               AND CDCAMPO     = C_0175.CDCAMPO
               AND DTALTERACAO = C_0175.DTALTERACAO
             GROUP BY CDCAMPO;

            V_CONTREGA := NVL(V_CONTREGA,0) + 1;
            INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
                ('06'||LPAD(RTRIM(V_CONTREGA), 8, '0'), V_0175, P_NRSEQARQMAG);
         END IF;

         IF (C_0175.CDCAMPO = '04' OR C_0175.CDCAMPO = '08' OR C_0175.CDCAMPO = '10' OR C_0175.CDCAMPO = '11' OR C_0175.CDCAMPO = '12' OR C_0175.CDCAMPO = '13') THEN

            SELECT SUM(CONT)
              INTO V_QUANTANTES
              FROM (SELECT COUNT(*) AS CONT
                      FROM NOTAFISCAL N
                     WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN P_DTINICIAL AND TRUNC(C_0175.DTALTERACAO-1)
                       AND N.IDORIGDESTNF||N.CDFORNECED   = C_0175.CDCHAVE
                       AND N.CDEMPRESA    = P_CDEMPRESA
                       AND N.CDINSCESTA   = P_CDINSCESTA
                       AND N.IDORIGDESTNF = 'F'
                       AND N.IDSTATUS  IN ('A','E')
                     UNION ALL
                    SELECT COUNT(*) AS CONT
                      FROM NOTAFISCAL N
                     WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN P_DTINICIAL AND TRUNC(C_0175.DTALTERACAO-1)
                       AND N.IDORIGDESTNF||N.CDCLIENTE    = C_0175.CDCHAVE
                       AND N.CDEMPRESA    = P_CDEMPRESA
                       AND N.CDINSCESTA   = P_CDINSCESTA
                       AND N.IDORIGDESTNF = 'C'
                       AND N.IDSTATUS  IN ('A','E')
                     UNION ALL
                    SELECT COUNT(*) AS CONT
                      FROM NOTAFISCAL N
                     WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN P_DTINICIAL AND TRUNC(C_0175.DTALTERACAO-1)
                       AND N.IDORIGDESTNF||DECODE(N.IDENTRSAIDOP,'S', N.CDFILIRECE, N.CDFILIEMIT) = C_0175.CDCHAVE
                       AND N.CDEMPRESA    = P_CDEMPRESA
                       AND N.CDINSCESTA   = P_CDINSCESTA
                       AND N.IDORIGDESTNF = 'L'
                       AND N.IDSTATUS  IN ('A','E'));

            SELECT SUM(CONT)
              INTO V_QUANTDEPOIS
              FROM (SELECT COUNT(*) AS CONT
                     FROM NOTAFISCAL N
                    WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN TRUNC(C_0175.DTALTERACAO) AND P_DTFINAL
                      AND N.IDORIGDESTNF||N.CDFORNECED   = C_0175.CDCHAVE
                      AND N.CDEMPRESA    = P_CDEMPRESA
                      AND N.CDINSCESTA   = P_CDINSCESTA
                      AND N.IDORIGDESTNF = 'F'
                      AND N.IDSTATUS  IN ('A','E')
                   UNION ALL
                   SELECT COUNT(*) AS CONT
                     FROM NOTAFISCAL N
                    WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN TRUNC(C_0175.DTALTERACAO) AND P_DTFINAL
                      AND N.IDORIGDESTNF||N.CDCLIENTE    = C_0175.CDCHAVE
                      AND N.CDEMPRESA    = P_CDEMPRESA
                      AND N.CDINSCESTA   = P_CDINSCESTA
                      AND N.IDORIGDESTNF = 'C'
                      AND N.IDSTATUS  IN ('A','E')
                   UNION ALL
                   SELECT COUNT(*) AS CONT
                     FROM NOTAFISCAL N
                    WHERE DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) BETWEEN TRUNC(C_0175.DTALTERACAO)  AND P_DTFINAL
                      AND N.IDORIGDESTNF||DECODE(N.IDENTRSAIDOP,'S', N.CDFILIRECE, N.CDFILIEMIT) = C_0175.CDCHAVE
                      AND N.CDEMPRESA    = P_CDEMPRESA
                      AND N.CDINSCESTA   = P_CDINSCESTA
                      AND N.IDORIGDESTNF = 'L'
                      AND N.IDSTATUS  IN ('A','E'));

            IF (NVL(V_QUANTANTES,0) > 0 AND NVL(V_QUANTDEPOIS,0) > 0) THEN

               SELECT ('|0175'                                    || '|' ||
                      TO_CHAR(MAX(DTALTERACAO),'DDMMYYYY')        || '|' ||
                      CDCAMPO                                     || '|' ||
                      MAX(RETIRA_CARACTER_ESPECIAL(DSCONTEUDOANT,'S')) || '|') AS C0175
                 INTO V_0175
                 FROM ALTERAPARTSPED
                WHERE CDCHAVE     = C_0175.CDCHAVE
                  AND CDCAMPO     = C_0175.CDCAMPO
                  AND DTALTERACAO = C_0175.DTALTERACAO
                GROUP BY CDCAMPO;

               V_CONTREGA := NVL(V_CONTREGA,0) + 1;
               INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                   ('06'||LPAD(RTRIM(V_CONTREGA), 8, '0'), V_0175, P_NRSEQARQMAG);
            END IF;
         END IF;
      END LOOP;
   END LOOP;

   /*Atualiza a tabela de alteracao dos produtos, para gravar a referencia de envio no SPED*/
   UPDATE ALTERAPRODSPED
      SET DTENVIASPED = P_DTINICIAL
    WHERE DTENVIASPED IS NULL
      AND CDPRODUTO   IN (SELECT CDCHAVEREG
                            FROM TMP_REGDADOSSPED
                           WHERE CDREGISTRO = '0200'
                             AND NVL(CDCAMPO2,'N') = 'N')
      AND DTALTERACAO <= P_DTFINAL;
   COMMIT;

   /* REGISTRO 0200 - Identificacao do Item (Produtos e Servicos) */
   FOR C_0200 IN (SELECT CDCHAVEREG, NVL(CDCAMPO1,'N') AS GERA_0210, CDCAMPO3 AS CDCLASFISC
                    FROM TMP_REGDADOSSPED
                   WHERE CDREGISTRO = '0200'
                     AND NVL(CDCAMPO2,'N') = 'N') LOOP

      SELECT MAX('|0200'       || '|' ||
             P.CDARVPROD       || '|' ||
             RETIRA_CARACTER_ESPECIAL(P.NMPRODUTO,'S') || '|' ||
             P.CDBARPRODUTO    || '|' ||
             ''                || '|' || /* COD_ANT_ITEM */
             P.SGUNIDADE       || '|' ||
             R.CDTPITEMPROD    || '|' || /* TIPO ITEM */
             NVL(C_0200.CDCLASFISC, P.CDCLASFISC) || '|' || /* CLAS. FISCAL */
             P.CDEXTIPI        || '|' ||
             R.CDGENPROD       || '|' ||
             ''                || '|' ||
             ''                || '|' ||
             CASE WHEN TO_NUMBER(P_VERSAO) >= 11 THEN TRIM(REPLACE(P.CDCEST,'.')) ||'|' ELSE '' END), MAX(P.SGUNIDADE), MAX(P.CDARVPROD)
        INTO V_0200, V_UNIDADE, V_CDARVPROD
        FROM RELACPRODITGEN R, PRODUTO P
       WHERE P.CDPRODUTO     = C_0200.CDCHAVEREG
         AND R.CDPRODUTO(+)  = P.CDPRODUTO
         AND R.CDLIVROMAG(+) = P_LIVROMAG;

      IF V_0200 IS NOT NULL THEN
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), V_0200, P_NRSEQARQMAG);
      END IF;

      /*REGISTRO 0205: Alteracao do Item*/
      FOR C_REG0205 IN (SELECT '|0205'                                     ||'|'||
                               RETIRA_CARACTER_ESPECIAL(DSCONTEUDOANT,'S') ||'|'||
                               TO_CHAR(DTINICIAL,'DDMMYYYY')               ||'|'||
                               TO_CHAR(DTALTERACAO,'DDMMYYYY')             ||'|'||
                               ''  ||'|' AS ARQ_0205
                          FROM ALTERAPRODSPED
                         WHERE DTENVIASPED  = P_DTINICIAL
                           AND CDPRODUTO    = C_0200.CDCHAVEREG
                           AND DTALTERACAO <= P_DTFINAL) LOOP

         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0205.ARQ_0205, P_NRSEQARQMAG);
      END LOOP;

      /* REGISTRO 0210 - Consumo Especifico Padronizado */
      IF C_0200.GERA_0210 = 'S' THEN
         FOR C_REG0210 IN (SELECT '|0210'           || '|' ||
                                  P.CDARVPRODINSUMO || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(NVL(P.QTDADEINSUMO,0),'999999999990.000000')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(NVL(P.QTDPERDAPERCENT,0),'90.0000')), '.', ',')          || '|' AS ARQ_0210,
                                  P.CDPRODINSUMO AS CDPRODUTO
                             FROM BLOCOK_PRODUCAO_0210 P
                            WHERE P.CDEMPRESA  = P_CDEMPRESA
                              AND P.CDINSCESTA = P_CDINSCESTA
                              AND P.CDPRODUTO  = C_0200.CDCHAVEREG
                              AND P_ORIGBLOCOK IN ('1','2')
                            UNION ALL
                            SELECT '|0210'            || '|' ||
                                   PR.CDARVPROD       || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(NVL(A.QTPRODBRUTA/NVL(A.QTSERVPRODUZIDO,1),0),'999999999990.000000')), '.', ',') || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(NVL(100-(A.QTPRODLIQ*100/NVL(A.QTPRODBRUTA,1)),0),'90.0000')), '.', ',')         || '|' AS ARQ_0210,
                                   A.CDPRODCPLA AS CDPRODUTO
                              FROM PRODPLANEJBLOCOK A, PLAREALBLOCOK P, PRODUTO PR, RELACPRODITGEN R, TIPOITEMBLOCOK T
                             WHERE A.CDPRODACABADO   = C_0200.CDCHAVEREG
                               AND A.CDPRODCPLA      = PR.CDPRODUTO
                               AND A.NRPLAREAL       = P.NRPLAREAL
                               AND P.DTINIPLAREAL    BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND P.CDEMPRESA       = P_CDEMPRESA
                               AND P.CDINSCESTA      = P_CDINSCESTA
                               AND PR.CDPRODUTO      = R.CDPRODUTO
                               AND R.CDLIVROMAG      = P_LIVROMAG
                               AND R.CDTPITEMPROD    = T.CDTPITEMPROD
                               AND T.IDITEMCONSUMIDO = 'S'
                               AND P_ORIGBLOCOK IN ('0','2')) LOOP

            /*CODIGO DO PRODUTO*/
            SELECT MAX(CDCHAVEREG)
              INTO V_PRODUTO
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REG0210.CDPRODUTO
               AND CDREGISTRO = '0200';

            IF (C_REG0210.CDPRODUTO IS NOT NULL) AND (V_PRODUTO IS NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2)
               VALUES
                  ('0200', V_SEQUPROD, C_REG0210.CDPRODUTO, 'N', 'S');
            END IF;

            V_CONTREGA := NVL(V_CONTREGA,0) + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0210.ARQ_0210, P_NRSEQARQMAG);
         END LOOP;
      END IF;

      /* REGISTRO 0220 - Fatores de Conversao de Unidades */
      FOR C_REG0220 IN (SELECT ('|0220'      || '|' ||
                               SGUNIDADE     || '|' ||
                               REPLACE(LTRIM(TO_CHAR(VRFATOCONV, '999999999990.00')), '.', ',') || '|'   ) AS ARQ_0220,
                               SGUNIDADE, UNIDADE, VRFATOCONV
                          FROM (SELECT PR.SGUNIDADE  AS SGUNIDADE,
                                       SUM(PR.VRFATOCONV) AS VRFATOCONV,
                                       PR.SGUNIDADE AS UNIDADE
                                  FROM PRODUTO PR, UNIDMEDIDA UN
                                 WHERE PR.SGUNIDADE = UN.SGUNIDADE
                                   AND UN.SGUNIDADE = V_UNIDADE
                                   AND PR.CDARVPROD = V_CDARVPROD
                                 GROUP BY PR.SGUNIDADE
                                 UNION
                                SELECT T.CDCHAVEREG  AS SGUNIDADE,
                                       PR.VRFATOCONV AS VRFATOCONV,
                                       PR.SGUNIDADE  AS UNIDADE
                                  FROM PRODUTO PR, PRODUTO PE, TMP_REGDADOSSPED T
                                 WHERE PE.CDARVPROD  = V_CDARVPROD
                                   AND PE.CDPRODUTO  = PR.CDPRODESTO
                                   AND PR.CDARVPROD <> V_CDARVPROD
                                   AND PR.SGUNIDADE  = T.CDCAMPO1
                                   AND PR.VRFATOCONV = T.CDCAMPO2
                                   AND T.CDREGISTRO  = '0190')) LOOP
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0220.ARQ_0220, P_NRSEQARQMAG);

         /*UNIDADE DE MEDIDA */
         SELECT MAX(CDCHAVEREG)
           INTO V_UNID
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REG0220.SGUNIDADE
            AND CDREGISTRO = '0190';

         IF (V_UNID IS NULL) AND (C_REG0220.SGUNIDADE IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
            VALUES
              ('0190', V_SEQUPROD, C_REG0220.SGUNIDADE);
         END IF;
         V_UNID := NULL;
      END LOOP;
   END LOOP;

   /*Atualiza a tabela de alteracao dos produtos, para gravar a referencia de envio no SPED*/
   UPDATE ALTERAPRODSPED
      SET DTENVIASPED = P_DTINICIAL
    WHERE DTENVIASPED IS NULL
      AND CDPRODUTO   IN (SELECT CDCHAVEREG
                            FROM TMP_REGDADOSSPED
                           WHERE CDREGISTRO = '0200'
                             AND NVL(CDCAMPO2,'N') = 'S')
      AND DTALTERACAO <= P_DTFINAL;
   COMMIT;


   /*GERA O REGISTRO 0200 PARA PRODUTOS QUE APARECEM NO 0210, MAS NAO FORAM USADOS NO BLOCO K*/
   FOR C_0200C IN (SELECT '|0200'          || '|' ||
                          P.CDARVPROD      || '|' ||
                          TRIM(P.NMPRODUTO)|| '|' ||
                          P.CDBARPRODUTO   || '|' ||
                          ''               || '|' || /* COD_ANT_ITEM */
                          P.SGUNIDADE      || '|' ||
                          R.CDTPITEMPROD   || '|' || /* TIPO ITEM */
                          P.CDCLASFISC     || '|' || /* CLAS. FISCAL */
                          P.CDEXTIPI       || '|' ||
                          R.CDGENPROD      || '|' ||
                          ''               || '|' ||
                          ''               || '|' ||
                          CASE WHEN TO_NUMBER(P_VERSAO) >= 11 THEN TRIM(REPLACE(P.CDCEST,'.')) ||'|' ELSE '' END AS ARQ_0200,
                          P.SGUNIDADE AS UNIDADE, P.CDARVPROD AS PRODUTO, P.CDPRODUTO
                     FROM TMP_REGDADOSSPED T, RELACPRODITGEN R, PRODUTO P
                    WHERE T.CDREGISTRO        = '0200'
                      AND NVL(T.CDCAMPO2,'N') = 'S'
                      AND P.CDPRODUTO         = T.CDCHAVEREG
                      AND R.CDPRODUTO(+)      = P.CDPRODUTO
                      AND R.CDLIVROMAG(+)     = P_LIVROMAG) LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0200C.ARQ_0200, P_NRSEQARQMAG);

      /*UNIDADE DE MEDIDA */
      SELECT MAX(CDCHAVEREG)
        INTO V_UNID
        FROM TMP_REGDADOSSPED
       WHERE CDCHAVEREG = C_0200C.UNIDADE
         AND CDREGISTRO = '0190';

      IF (V_UNID IS NULL) AND (C_0200C.UNIDADE IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_REGDADOSSPED
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
         VALUES
           ('0190', V_SEQUPROD, C_0200C.UNIDADE);
      END IF;
      V_UNID := NULL;

      /*REGISTRO 0205: Alteracao do Item*/
      FOR C_REG0205 IN (SELECT '|0205'                                     ||'|'||
                               RETIRA_CARACTER_ESPECIAL(DSCONTEUDOANT,'S') ||'|'||
                               TO_CHAR(DTINICIAL,'DDMMYYYY')               ||'|'||
                               TO_CHAR(DTALTERACAO,'DDMMYYYY')             ||'|'||
                               ''  ||'|' AS ARQ_0205
                          FROM ALTERAPRODSPED
                         WHERE DTENVIASPED = P_DTINICIAL
                           AND CDPRODUTO   = C_0200C.CDPRODUTO
                           AND DTALTERACAO <= P_DTFINAL) LOOP

         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0205.ARQ_0205, P_NRSEQARQMAG);
      END LOOP;

      FOR C_REG0220 IN (SELECT ('|0220'      || '|' ||
                               SGUNIDADE     || '|' ||
                               REPLACE(LTRIM(TO_CHAR(VRFATOCONV, '999999999990.00')), '.', ',') || '|'   ) AS ARQ_0220
                          FROM (SELECT PR.SGUNIDADE  AS SGUNIDADE,
                                       SUM(PR.VRFATOCONV) AS VRFATOCONV
                                  FROM PRODUTO PR, UNIDMEDIDA UN
                                 WHERE PR.SGUNIDADE = UN.SGUNIDADE
                                   AND UN.SGUNIDADE = C_0200C.UNIDADE
                                   AND PR.CDARVPROD = C_0200C.PRODUTO
                                 GROUP BY PR.SGUNIDADE)) LOOP

         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0220.ARQ_0220, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

   /* REGISTRO 0190 - UNIDADE DE MEDIDA*/
   FOR C_0190 IN (SELECT '|0190'            || '|' ||
                         TRIM(U.SGUNIDADE)  || '|' ||
                         TRIM(U.DSUNIDADE)  || '|' AS UNID,
                         TRIM(U.SGUNIDADE) AS SGUNIDADE , U.DSUNIDADE AS DSUNIDADE
                    FROM TMP_REGDADOSSPED R, UNIDMEDIDA U
                   WHERE R.CDREGISTRO = '0190'
                     AND R.CDCHAVEREG = U.SGUNIDADE
                     AND R.CDCAMPO1   IS NULL
                   UNION
                  SELECT '|0190'            || '|' ||
                         TRIM(R.CDCHAVEREG) || '|' ||
                         TRIM(U.DSUNIDADE)  || '|' AS UNID,
                         TRIM(U.SGUNIDADE) AS SGUNIDADE , U.DSUNIDADE AS DSUNIDADE
                    FROM TMP_REGDADOSSPED R, UNIDMEDIDA U
                   WHERE R.CDREGISTRO = '0190'
                     AND R.CDCAMPO1   = U.SGUNIDADE) LOOP
      IF UPPER(C_0190.DSUNIDADE) = 'UNIDADE' THEN
         V_SGUNID     := C_0190.SGUNIDADE;
         V_UNIDADEVAF := 'S';
      END IF;

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('07'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0190.UNID, P_NRSEQARQMAG);
   END LOOP;

   /*Verifica se gerou o registro 1400 para Minas Gerais, que exige codigo do produto fixo*/
   BEGIN
      SELECT CASE WHEN COUNT(*) > 0 THEN 'S' ELSE 'N' END
        INTO V_VAFMG
        FROM ARQMAGNETICO
       WHERE DSREGARQMAG LIKE '|1400|Outras_Entradas_a_Detalhar_por_Municipio%'
         AND NRSEQARQMAG = P_NRSEQARQMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VAFMG := 'N';
   END;

   IF V_VAFMG = 'S' THEN
      /*O codigo do produto no registro 1400 relacionado ao VAF para o Estado de MG exige codigo de unidade fixo*/
      IF V_UNIDADEVAF = 'N' THEN
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('07'||LPAD(RTRIM(V_CONTREGA), 8, '0'), '|0190|UNIDAD|UNIDADE|', P_NRSEQARQMAG);
         V_SGUNID := 'UNIDAD';
      END IF;

      V_0200 := '|0200'                                    || '|' ||
                'Outras_Entradas_a_Detalhar_por_Municipio' || '|' ||
                'Outras Entradas a Detalhar por Municipio' || '|' ||
                ''                                         || '|' ||
                ''                                         || '|' ||
                V_SGUNID                                   || '|' || /* UNIDADE */
                '99'                                       || '|' || /* TIPO ITEM */
                ''                                         || '|' || /* CLAS. FISCAL */
                ''                                         || '|' ||
                ''                                         || '|' ||
                ''                                         || '|' ||
                ''                                         || '|' ||
                ''                                         || '|' ;
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), V_0200, P_NRSEQARQMAG);
   END IF;

   /*REGISTRO 0300 - IDENTIFICACAO DO BEM*/
   FOR C_REG0300 IN (SELECT '|0300'                 ||'|'||
                            SUBSTR(CDBEMATIVO,1,60) ||'|'||
                            IDENT_MERC              ||'|'||
                            DESCRICAO               ||'|'||
                            ''                      ||'|'||
                            CDCTBLENTRPR            ||'|'||
                            PARCELA                 ||'|' AS DSARQUMAG,
                            '|0305'                 ||'|'||
                            CDCENTCUST              ||'|'||
                            DSOBSBEMATIVO           ||'|'||
                            ''                      ||'|' AS DSARQUMAG305,
                            CDCTBLENTRPR
                       FROM (SELECT BA.CDBEMATIVO, BA.NMBEMATIVO||'-'||FA.NMFABRIC||'-'||MO.NMMODELO AS DESCRICAO,  '1' AS IDENT_MERC,
                                    TO_NUMBER(SUBSTR(MAX(PB.NRPARCELACIAP),1,INSTR(MAX(PB.NRPARCELACIAP),'/')-1)) AS PARCELA, NVL(CDCTBLENTRPR,CTBL_PRODUTO(P.CDPRODUTO,'E')) AS CDCTBLENTRPR,
                                    CDCENTCUST, BA.DSOBSBEMATIVO
                               FROM TMP_REGDADOSSPED TP, BEMATIVO BA, PARCELACIAPBEM PB, MODELO MO,FABRICANTE FA, PRODUTO P
                              WHERE BA.CDEMPRESA  = PB.CDEMPRESA
                                AND BA.CDBEMATIVO = PB.CDBEMATIVO
                                AND BA.CDBEMATIVO = TP.CDCHAVEREG
                                AND TP.CDCAMPO1   = PB.CDIMPOSTO
                                AND TP.CDCHAVEREG = BA.CDBEMATIVO
                                AND BA.CDMODELO   = MO.CDMODELO(+)
                                AND TP.CDCAMPO2   = P.CDPRODUTO(+)
                                AND BA.CDFABRIC   = FA.CDFABRIC(+)
                                AND TP.CDREGISTRO = '0300'
                              GROUP BY BA.CDBEMATIVO, BA.NMBEMATIVO||'-'||FA.NMFABRIC||'-'||MO.NMMODELO,  '1',NVL(CDCTBLENTRPR,CTBL_PRODUTO(P.CDPRODUTO,'E')),CDCENTCUST,DSOBSBEMATIVO))LOOP

      /*Registro G300*/
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0300.DSARQUMAG, P_NRSEQARQMAG);
      /*registro G305*/
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0300.DSARQUMAG305, P_NRSEQARQMAG);

      /* PLANO DE CONTAS REFERENCIAL */
      SELECT MAX(CDCHAVEREG)
        INTO V_CONTCTBL
        FROM TMP_REGDADOSSPED
       WHERE CDCHAVEREG = C_REG0300.CDCTBLENTRPR
         AND CDREGISTRO = '0500';

      IF (V_CONTCTBL IS NULL) AND (C_REG0300.CDCTBLENTRPR IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_REGDADOSSPED
            (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
         VALUES
           ('0500', V_SEQUPROD, C_REG0300.CDCTBLENTRPR);
      END IF;
   END LOOP;

   V_CDCHAVEREG := NULL;
   V_DSCFOPPADRAO := NULL;

   /*REGISTRO 0400 - Natureza da Operacao/Prestacao*/
   FOR C_0400 IN (SELECT SUBSTR(CDCHAVEREG,2,3) AS CDFISO,
                         CDCHAVEREG, CDCAMPO2 AS IDENTRSAIDOP
                    FROM TMP_REGDADOSSPED
                   WHERE CDREGISTRO = '0400'
                     AND SUBSTR(CDCHAVEREG,2,3) IS NOT NULL
                     AND CDCONTREGI IS NOT NULL
                   ORDER BY CDCHAVEREG) LOOP

      SELECT C_0400.CDCHAVEREG, MAX(DSCFOPPADRAO)
        INTO  V_CDCHAVEREG, V_DSCFOPPADRAO
        FROM CFOPPADRAO
       WHERE CDCFOPPADRAO = C_0400.CDCHAVEREG;

      IF V_CDCHAVEREG IS NULL THEN
          SELECT  C_0400.CDCHAVEREG, MAX(DSCDFISCOPER)
          INTO  V_CDCHAVEREG, V_DSCFOPPADRAO
          FROM CODFISCOPER
          WHERE CDFISCOPER   = C_0400.CDFISO
            AND IDENTRSAIDOP = C_0400.IDENTRSAIDOP;
      END IF;

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), '|0400|'||V_CDCHAVEREG|| '|' ||TRIM(V_DSCFOPPADRAO)|| '|' , P_NRSEQARQMAG);
      V_CDCHAVEREG := NULL;
      V_DSCFOPPADRAO := NULL;
   END LOOP;

   /*REGISTRO 0450 - Informacao Complementar do documento fiscal*/
   FOR C_0450 IN (SELECT '|0450'     || '|' ||
                         CDCHAVEREG  || '|' ||
                         CDCAMPO1    || '|'  AS ARQ_0450
                    FROM TMP_REGDADOSSPED
                   WHERE CDREGISTRO = '0450' ) LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0450.ARQ_0450, P_NRSEQARQMAG);
   END LOOP;

   /*REGISTRO 0460 - Observacoes do Lancamento Fiscal */
   FOR C_0460 IN (SELECT '|0460'     || '|' ||
                         CDCHAVEREG  || '|' ||
                         CDCAMPO1    || '|' AS ARQ_0460
                    FROM TMP_REGDADOSSPED
                   WHERE CDREGISTRO = '0460' ) LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0460.ARQ_0460, P_NRSEQARQMAG);
   END LOOP;

   /*REGISTRO 0500 - PLANO DE CONTAS CONTABEIS*/
   FOR C_0500 IN (SELECT '|0500'                              ||'|'||
                         TO_CHAR(CASE WHEN CTB.DTULTATUCCTB > P_DTFINAL
                                      THEN P_DTFINAL
                                      ELSE CTB.DTULTATUCCTB END,'DDMMYYYY') ||'|'||
                         NVL(CTB.IDNATUCONT,'01')             ||'|'||
                         CTB.IDTIPOCONT                       ||'|'||
                         CTB.CDNVCONTCTBL                     ||'|'||
                         CTB.CDCONTCTBL                       ||'|'||
                         CTB.NMCONTCTBL                       ||'|' AS DSARQUMAG
                    FROM TMP_REGDADOSSPED TP, CONTCTBL CTB
                   WHERE TP.CDCHAVEREG = CTB.CDCONTCTBL
                     AND TP.CDREGISTRO = '0500')LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0500.DSARQUMAG, P_NRSEQARQMAG);
   END LOOP;

   /*REGISTRO 0600 - Centro de custos*/
   FOR C_0600 IN (SELECT '|0600'                          ||'|'||
                         TO_CHAR(DTULTATUCCUS,'DDMMYYYY') ||'|'||
                         CDCENTCUST                       ||'|'||
                         NMCENTCUST                       ||'|' AS ARQ_0600
                    FROM CENTCUST CC, TMP_REGDADOSSPED TP
                   WHERE CC.CDCENTCUST = TP.CDCHAVEREG
                     AND TP.CDREGISTRO = '0600')LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_0600.ARQ_0600, P_NRSEQARQMAG);
   END LOOP;

   V_CONTREGA := NVL(V_CONTREGA,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('10'||LPAD(RTRIM(V_CONTREGA), 8, '0'), '|0990'|| '|' ||V_CONTREGA|| '|' , P_NRSEQARQMAG);
   COMMIT;

   /* contador do bloco 0  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = '0'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES 
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /* contador do bloco B  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'B'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /* contador do bloco C  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE 
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'C'
                    AND NRSEQARQMAG = P_NRSEQARQMAG 
                  GROUP BY SUBSTR(DSREGARQMAG,2,4) 
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP 
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1; 
      INSERT INTO ARQMAGNETICO 
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES 
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG); 
   END LOOP;
   /* contador do bloco D  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE 
                   FROM ARQMAGNETICO 
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'D' 
                    AND NRSEQARQMAG = P_NRSEQARQMAG 
                  GROUP BY SUBSTR(DSREGARQMAG,2,4) 
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP 
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1; 
      INSERT INTO ARQMAGNETICO 
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
      VALUES 
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG); 
   END LOOP;
   /* contador do bloco E  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE 
                   FROM ARQMAGNETICO 
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'E' 
                    AND NRSEQARQMAG = P_NRSEQARQMAG 
                  GROUP BY SUBSTR(DSREGARQMAG,2,4) 
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP 
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1; 
      INSERT INTO ARQMAGNETICO 
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
      VALUES 
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG); 
   END LOOP;
   /* contador do bloco G  */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'G'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /* contador do bloco H */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'H'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /* contador do bloco K */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'K'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /* contador do bloco 1 */
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGNETICO
                  WHERE SUBSTR(DSREGARQMAG,2,1) = '1'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9001'|| '|' ||'1'|| '|', P_NRSEQARQMAG);
   V_TOTREG := 0;
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   SELECT COUNT(*)
     INTO V_TOTREG
     FROM ARQMAGNETICO
    WHERE SUBSTR(DSREGARQMAG,2,4) = '9900'
      AND NRSEQARQMAG = P_NRSEQARQMAG;
   IF V_TOTREG IS NOT NULL THEN
      V_TOTREG := NVL(V_TOTREG,0) + 3;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9900'|| '|' ||V_TOTREG|| '|', P_NRSEQARQMAG);
   END IF;
   V_CONTREG9 := NVL(V_CONTREG9,0) + 2;
   INSERT INTO ARQMAGNETICO 
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
   VALUES
     ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9990'|| '|' ||'1'|| '|', P_NRSEQARQMAG); 
   V_CONTREG9 := NVL(V_CONTREG9,0);  
   INSERT INTO ARQMAGNETICO 
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
   VALUES 
     ('90'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9999'|| '|' ||'1'|| '|', P_NRSEQARQMAG);
   IF V_CONTREG9 <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('89'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9001'|| '|' ||V_MOVIMENT|| '|' , P_NRSEQARQMAG);
   V_CONTREG9 := NVL(V_CONTREG9,0) + 2;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('95'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9990'|| '|' ||V_CONTREG9|| '|', P_NRSEQARQMAG);
   DELETE FROM TMP_REGDADOSSPED;
   COMMIT;
   RETURN(V_CONTREG9);
END;