create or replace procedure BUILDEMPLID(emp_cursor out sys_refcursor) is
n_emplid number;
n_emplid1 number;
n_len number;
r_cursor sys_refcursor;
begin
--UPDATE THE LAST ASSIGNMENT+1 IN THE INSTALLATION TABLE
UPDATE PS_INSTALLATION SET EMPLID_LAST_EMPL = EMPLID_LAST_EMPL + 1;
SELECT EMPLID_LAST_EMPL, emplid_length
INTO n_emplid, n_len
FROM PS_INSTALLATION;
IF n_emplid > power(10, n_len) - 1 THEN
UPDATE PS_INSTALLATION SET EMPLID_LAST_EMPL = EMPLID_LAST_EMPL - 1;
DBMS_OUTPUT.PUT_LINE('Employee numbers already have a maximum');
ELSE
--GET THE LATEST WORK NUMBER
SELECT TO_CHAR(EMPLID_LAST_EMPL) INTO n_emplid1 FROM PS_INSTALLATION;
open r_cursor for
select substr(power(10, n_len), 2, n_len - length(n_emplid1)) ||
to_char(n_emplid1) emplid
from dual;
emp_cursor:= r_cursor;
END IF;
COMMIT;
end;
