CREATE OR REPLACE PROCEDURE SPED_ECF_BLOCO9_ENCERRAMENTO(
   P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
   P_NRSEQARQMAG IN VARCHAR,
   P_NRORDEMATUAL IN NUMBER,
   P_NRORDENACAO OUT NUMBER)
IS
   V_LINHA VARCHAR2(4000);
   V_NRORDREGARQ NUMBER;
BEGIN
   V_NRORDREGARQ := P_NRORDEMATUAL;

   BEGIN
      SELECT '|9001'                  ||'|'|| /* Nome do Registro */
             '1'                      ||'|'   /* Identificador de Movimento */
        INTO V_LINHA
        FROM DUAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_LINHA := NULL;
   END;

   V_NRORDREGARQ := V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_LINHA, P_NRSEQARQMAG);

   FOR C_9900 IN
      (SELECT '|9900|'||
               SUBSTR(DSREGARQMAG,2,4)||'|'||
               TO_CHAR(COUNT(*))||'|' AS DSLINHA
         FROM ARQMAGNETICO
        WHERE NRSEQARQMAG = P_NRSEQARQMAG
          AND SUBSTR(DSREGARQMAG,2,1) <> '9'
        GROUP BY SUBSTR(DSREGARQMAG,2,4)
        ORDER BY SUBSTR(DSREGARQMAG,2,4)) LOOP

      V_NRORDREGARQ := V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_9900.DSLINHA, P_NRSEQARQMAG);
   END LOOP;


   SELECT '|9990|'||
          TO_CHAR(COUNT(*) + 2)||'|' AS DSLINHA
     INTO V_LINHA
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = P_NRSEQARQMAG
      AND SUBSTR(DSREGARQMAG,2,1) = '9';

   V_NRORDREGARQ := V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_LINHA, P_NRSEQARQMAG);

   SELECT '|9999|'||
          TO_CHAR(COUNT(*) + 1)||'|'
     INTO V_LINHA
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = P_NRSEQARQMAG;

   V_NRORDREGARQ := V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_LINHA, P_NRSEQARQMAG);

   P_NRORDENACAO := V_NRORDREGARQ;
END;
/

