CREATE OR REPLACE FUNCTION SEF2_BLOCO9(
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_MOVBLOCOH    IN NUMBER) RETURN NUMBER
AS
   V_MOVIMENT     NUMBER;
   V_CONTREG9     NUMBER;
   V_TOTREG       NUMBER;
BEGIN
   V_CONTREG9 := 0;

   /* CONTADOR 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
        ('901'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR 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
        ('902'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR 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
        ('903'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR 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
        ('904'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR 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
        ('905'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR 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
        ('906'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /* CONTADOR BLOCO 9*/
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('907'||LPAD(RTRIM(V_CONTREG9), 7, '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
        ('908'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||'9900'|| '|' ||V_TOTREG|| '|', P_NRSEQARQMAG);
   END IF;

   V_CONTREG9 := NVL(V_CONTREG9,0) + 2;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('909'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||'9990'|| '|' ||'1'|| '|', P_NRSEQARQMAG);

   V_CONTREG9 := NVL(V_CONTREG9,0);
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('910'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9900'|| '|' ||'9999'|| '|' ||'1'|| '|', P_NRSEQARQMAG);

   IF V_CONTREG9 <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;

   /* REGISTRO 9001 */
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('900'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9001'|| '|' ||NVL(P_MOVBLOCOH,V_MOVIMENT)|| '|' , P_NRSEQARQMAG);

   /* REGISTRO 9990 */
   V_CONTREG9 := NVL(V_CONTREG9,0) + 2;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('998'||LPAD(RTRIM(V_CONTREG9), 7, '0'), '|9990'|| '|' ||V_CONTREG9|| '|', P_NRSEQARQMAG);

   DELETE FROM TMP_REGDADOSSEF2;

   COMMIT;

   RETURN(V_CONTREG9);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SEF2_BLOCO9','5.70.xxx'); END;
/