CREATE OR REPLACE PROCEDURE NOVO_CODIGO_ALFA(
   P_CONTADOR    IN  NOVOCODIGO.CDCONTADOR%TYPE,
   P_SEQUENCIAL  OUT NOVOCODIGO.NRSEQUENCIAL%TYPE,
   P_QTDE        IN  NUMBER DEFAULT 1,
   P_NRORG       IN  NOVOCODIGO.NRORG%TYPE DEFAULT 1)

AS
   V_COUNT   NUMBER;
   V_QTDE    NUMBER;
   V_CODIGO  VARCHAR2(20);
   V_CODSEQ  VARCHAR2(20);
   V_NEXTCD  VARCHAR2(01);
   V_BREAK   BOOLEAN;
   V_NRORG NOVOCODIGO.NRORG%TYPE;

BEGIN
   V_NRORG := P_NRORG;
   IF V_NRORG IS NULL THEN
      V_NRORG := 1;
   END IF;
   SELECT COUNT(CDCONTADOR) INTO V_COUNT
   FROM NOVOCODIGO
   WHERE CDCONTADOR = P_CONTADOR AND NRORG = V_NRORG;

   IF V_COUNT > 0 THEN
      SELECT NRSEQUENCIAL
      INTO   V_CODIGO
      FROM   NOVOCODIGO
      WHERE  CDCONTADOR = P_CONTADOR AND NRORG = V_NRORG
      FOR UPDATE;

      V_QTDE := 1;
      WHILE V_QTDE <= P_QTDE LOOP
         V_COUNT := LENGTH(V_CODIGO);
         V_BREAK := FALSE;
         WHILE (V_COUNT > 0) AND (NOT V_BREAK) LOOP
            IF SUBSTR(V_CODIGO, V_COUNT, 1) = 'Z' THEN
               V_CODIGO := SUBSTR(V_CODIGO, 1, V_COUNT-1)||'A'||SUBSTR(V_CODIGO, V_COUNT+1);
            ELSE
               V_NEXTCD := CHR(ASCII(SUBSTR(V_CODIGO, V_COUNT, 1))+1);
               V_CODIGO := SUBSTR(V_CODIGO, 1, V_COUNT-1)||V_NEXTCD||SUBSTR(V_CODIGO, V_COUNT+1);

               V_BREAK := TRUE;
            END IF;

            V_COUNT := V_COUNT - 1;
         END LOOP;

         IF V_QTDE = 1 THEN
            V_CODSEQ := V_CODIGO;
         END IF;

         V_QTDE := V_QTDE + 1;
      END LOOP;

      UPDATE NOVOCODIGO
      SET NRSEQUENCIAL = V_CODIGO
      WHERE CDCONTADOR = P_CONTADOR AND NRORG = V_NRORG;
   ELSE
      V_CODIGO := LPAD('A',20,'A');
      V_CODSEQ := V_CODIGO;

      V_QTDE := 1;
      WHILE V_QTDE <= P_QTDE-1 LOOP
         V_COUNT := LENGTH(V_CODIGO);
         V_BREAK := FALSE;
         WHILE (V_COUNT > 0) AND (NOT V_BREAK) LOOP
            IF SUBSTR(V_CODIGO, V_COUNT, 1) = 'Z' THEN
               V_CODIGO := SUBSTR(V_CODIGO, 1, V_COUNT-1)||'A'||SUBSTR(V_CODIGO, V_COUNT+1);
            ELSE
               V_NEXTCD := CHR(ASCII(SUBSTR(V_CODIGO, V_COUNT, 1))+1);
               V_CODIGO := SUBSTR(V_CODIGO, 1, V_COUNT-1)||V_NEXTCD||SUBSTR(V_CODIGO, V_COUNT+1);

               V_BREAK := TRUE;
            END IF;

            V_COUNT := V_COUNT - 1;
         END LOOP;

         V_QTDE := V_QTDE + 1;
      END LOOP;

      INSERT INTO NOVOCODIGO (CDCONTADOR, NRSEQUENCIAL, NRORG)
      VALUES (P_CONTADOR, V_CODIGO, V_NRORG);
   END IF;

   P_SEQUENCIAL := V_CODSEQ;

   COMMIT;
END;


/

-- VERSAO --
BEGIN ATUVEROBJ('NOVO_CODIGO_ALFA','5.30.009'); END;
/
