/*
@C:\WEB_407\CLI\SRC\CLI\COAN\SQL\ALTERA_NOVOCOD.SQL
*/

CREATE OR REPLACE PROCEDURE ALTERA_NOVOCOD
   IS
      V_COUNT        INTEGER;
      V_NRLANCESTQ   VARCHAR2(10);
BEGIN
   FOR C_FILIAL IN(SELECT CDFILIAL
                   FROM PARAMFILIAL P
                   WHERE IDCTRLESTQ = 'S')LOOP
      SELECT COUNT(*)
      INTO V_COUNT
      FROM NOVOCODIGO
      WHERE CDCONTADOR = 'LANCTOEST'||C_FILIAL.CDFILIAL;

      SELECT MAX(NRLANCESTQ)
      INTO V_NRLANCESTQ
      FROM LANCTOESTOQ
      WHERE CDFILIAL = C_FILIAL.CDFILIAL;

      IF NOT V_NRLANCESTQ IS NULL THEN

         IF V_COUNT > 0 THEN
            UPDATE NOVOCODIGO
            SET NRSEQUENCIAL = LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_NRLANCESTQ)) + 1)),20,'0')
            WHERE CDCONTADOR = 'LANCTOEST'||C_FILIAL.CDFILIAL;
         ELSE
            INSERT  INTO NOVOCODIGO
               (CDCONTADOR, NRSEQUENCIAL)
            VALUES
              ('LANCTOEST'||C_FILIAL.CDFILIAL, LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_NRLANCESTQ)) + 1)),20,'0'));
         END IF;
      END IF;
   END LOOP;
END;

/
