--@C:\Web_409\Srv\Src\Cli\SAP\SQL\0004_SAPTRGO_USR_APELIDO_FORNECEDOR.sql

CREATE OR REPLACE TRIGGER USR_APELIDO_FORNECEDOR
BEFORE INSERT ON FORNECEDOR
FOR EACH ROW
DECLARE
   V_DSAPELIDO  VARCHAR2(50);
   V_COUNT      INTEGER;
   V_INAUXILIAR INTEGER;

BEGIN
   V_DSAPELIDO := SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),1,10);

   SELECT COUNT(*)
   INTO V_COUNT
   FROM FORNECEDOR
   WHERE USR_DSAPELIDO = V_DSAPELIDO;

   IF V_COUNT = 0 THEN
      SELECT COUNT(*)
      INTO V_COUNT
      FROM CLIENTE
      WHERE USR_DSAPELIDO = V_DSAPELIDO;
   END IF;

   IF V_COUNT = 0 THEN
      :NEW.USR_DSAPELIDO := V_DSAPELIDO;
      :NEW.CDSENHAFOR := V_DSAPELIDO;
   ELSE
      V_INAUXILIAR := 11;
      V_DSAPELIDO := SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),1,9);
      WHILE (V_COUNT <> 0) AND (V_INAUXILIAR <= LENGTH(:NEW.NMFANTFORN)) LOOP
         V_DSAPELIDO := SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),1,9) || SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),V_INAUXILIAR,1);

         SELECT COUNT(*)
         INTO V_COUNT
         FROM FORNECEDOR
         WHERE USR_DSAPELIDO = V_DSAPELIDO;

         IF V_COUNT = 0 THEN
            SELECT COUNT(*)
            INTO V_COUNT
            FROM CLIENTE
            WHERE USR_DSAPELIDO = V_DSAPELIDO;
         END IF;

         V_INAUXILIAR := V_INAUXILIAR + 1;
      END LOOP;

      IF V_COUNT <> 0 THEN
         V_INAUXILIAR := 0;
         WHILE (V_COUNT <> 0) LOOP
            IF LENGTH(TO_CHAR(V_INAUXILIAR)) > 1 THEN
               V_DSAPELIDO := SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),1,10 - LENGTH(TO_CHAR(V_INAUXILIAR)));
            ELSE
               V_DSAPELIDO := SUBSTR(REPLACE(:NEW.NMFANTFORN,' ',''),1,9);
            END IF;
            V_DSAPELIDO := V_DSAPELIDO || TO_CHAR(V_INAUXILIAR);

            SELECT COUNT(*)
            INTO V_COUNT
            FROM FORNECEDOR
            WHERE USR_DSAPELIDO = V_DSAPELIDO;

            IF V_COUNT = 0 THEN
               SELECT COUNT(*)
               INTO V_COUNT
               FROM CLIENTE
               WHERE USR_DSAPELIDO = V_DSAPELIDO;
            END IF;

            V_INAUXILIAR := V_INAUXILIAR + 1;
         END LOOP;
      END IF;
      :NEW.USR_DSAPELIDO := V_DSAPELIDO;
      :NEW.CDSENHAFOR := V_DSAPELIDO;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_APELIDO_FORNECEDOR','5.01.003'); END;
/

