SQL & PL/SQL

Hi,
below my code:

CREATE TABLE TAB_TO_EM
(
  EM_NUMBER           VARCHAR2(32),
  NAME_FIRST       VARCHAR2(32),
  NAME_LAST        VARCHAR2(32),
  MAILBOX            VARCHAR2(32)
);

ALTER TABLE TAB_TO_EM
ADD (CONSTRAINT PK_EM_NUMBER PRIMARY KEY (EM_NUMBER));


CREATE TABLE EM
(
  EM_ID            VARCHAR2(32),
  EM_NUMBER           VARCHAR2(32),
  NAME_FIRST       VARCHAR2(32),
  NAME_LAST        VARCHAR2(32),
  EMAIL            VARCHAR2(32)
);

ALTER TABLE EM
ADD (CONSTRAINT PK_EM_ID PRIMARY KEY (EM_ID));

THE PRIMARY KEY EM_ID COMES FROM THE TAB_TO_EM TABLE LINKED IN THIS WAY:
NAME_LAST||'_'||SUBSTR(NAME_FIRST,1,3)||'_'||SUBSTR(EM_NUMBER,-2)

I created this procedure to insert a new EM_NUMBER into EM table:

CREATE OR REPLACE PROCEDURE INS_NEW_RECORD IS
ERR_NUM                          NUMBER;
ERR_MSG                          VARCHAR2(300);
V_COUNT                          NUMBER;
V_EM_ID                          VARCHAR2(64);



      CURSOR A IS
      SELECT A.EM_NUMBER, A.NAME_FIRST, A.NAME_LAST, A.MAILBOX
      FROM TAB_TO_EM A;

BEGIN
      FOR CUR_A IN A
  LOOP
             SELECT COUNT(*)
             INTO V_COUNT
             FROM EM2 B
             WHERE B.EM_NUMBER=CUR_A.EM_NUMBER;

             IF V_COUNT = 0 THEN
             
             -- here insert a new record

                          INSERT INTO EM2 (EM_ID, EM_NUMBER, NAME_FIRST,NAME_LAST, EMAIL)
                          VALUES (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2),
                          CUR_A.EM_NUMBER, CUR_A.NAME_FIRST,CUR_A.NAME_LAST, CUR_A.MAILBOX);

             END IF;
  END LOOP;
END INS_NEW_RECORD;

Example of my table:

Insert into TAB_TO_EM
   (EM_NUMBER, NAME_FIRST, NAME_LAST, MAILBOX)
 Values
   ('22333', 'AAAA', 'BBBB', 'XXXX');
Insert into TAB_TO_EM
   (EM_NUMBER, NAME_FIRST, NAME_LAST, MAILBOX)
 Values
   ('11222', 'AAAA', 'BBBB', 'XXXX');
Insert into TAB_TO_EM
   (EM_NUMBER, NAME_FIRST, NAME_LAST, MAILBOX)
 Values
   ('00122', 'AAAA', 'BBBB', 'XXXX');
commit;

   
execute INS_NEW_RECORD;
ORA-00001: ORA-00001: unique constraint violated (PK_EM_ID)
ORA-06512: a "INS_NEW_RECORD", line 25
ORA-06512: a line 2

I tried to modify my procedure:

CREATE SEQUENCE AFM.SEQ_EM_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  ORDER;
 

IF V_COUNT = 0 THEN
       
       --verify duplicate pkey

             SELECT (NAME_LAST||'_'||SUBSTR(NAME_FIRST,1,3)||SUBSTR(EM_NUMBER,-2))
             INTO V_EM_ID
             FROM EM B
             WHERE (NAME_LAST||'_'||SUBSTR(NAME_FIRST,1,3)||SUBSTR(EM_NUMBER,-2)) = (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2))
             AND ROWNUM=1;
             
             IF V_EM_ID = (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2)) THEN
             
                      INSERT INTO EM2 (EM_ID, EM_NUMBER, NAME_FIRST,NAME_LAST, EMAIL)
                      VALUES (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2)||'_'||SUBSTR ('000' || SEQ_EM_ID.NEXTVAL, -3),
                      CUR_A.EM_NUMBER, CUR_A.NAME_FIRST,CUR_A.NAME_LAST, CUR_A.MAILBOX);
             ELSE


                          INSERT INTO EM2 (EM_ID, EM_NUMBER, NAME_FIRST,NAME_LAST, EMAIL)
                          VALUES (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2),
                          CUR_A.EM_NUMBER, CUR_A.NAME_FIRST,CUR_A.NAME_LAST, CUR_A.MAILBOX);

             END IF;
      END IF;

execute INS_NEW_RECORD;
ORA-01403 no data found

I'd like to create a stored procedure with these conditions:
IF em_id is duplicate insert new em_id= (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2)||'_'||SUBSTR ('000' || SEQ_EM_ID.NEXTVAL, -3)
ELSE em_id= (CUR_A.NAME_LAST||'_'||SUBSTR(CUR_A.NAME_FIRST,1,3)||SUBSTR(CUR_A.EM_NUMBER,-2)

How can I write my procedure correctly?

Thanks in advance!