create or replace 
PROCEDURE ADD_STAFF_MEMBER (pStaffID varchar2, pFirstName varchar2, 
												pLastname varchar2, pEmail varchar2, 
												pContactNo varchar2, pRoleName varchar2) AS
		

    err_null_req_values EXCEPTION;
		error_name			varchar2(20);
    gStaffRolePK number(10);
	
  BEGIN		
		if (pStaffID is NULL) or (pFirstName is NULL) then
			RAISE err_null_req_values;
		else
    SELECT ROLE_PK INTO gStaffRolePK FROM TCS_ROLE WHERE ROLE_NAME = pRoleName;
			Insert Into TCS_STAFF (Staff_PK, Staff_ID, First_Name, Lastname, Password, 
			Email, Contact_No, Is_Active) 
			Values (Seq_Staff_PK.nextval, pStaffID, pFirstname, PLastname, 'password', pEmail, pContactNo, 'Y');
      Insert Into TCS_STAFF_ROLE (Staff_Role_PK, Staff_PK, Role_PK)
			Values (Seq_Staff_Role_PK.nextval, Seq_Staff_PK.currval, gStaffRolePK);
		end if;
		COMMIT;
	EXCEPTION
		when err_null_req_values then
			RAISE_APPLICATION_ERROR(-20003, 'Required value(s) not entered');
		when DUP_VAL_ON_INDEX then
			error_name := strip_constraint_name(SQLERRM);
			if (error_name = 'UN_EMAIL') then
				RAISE_APPLICATION_ERROR(-20011, 'Duplicate Email Address');
			elsif (error_name = 'UN_STAFFID') then
				RAISE_APPLICATION_ERROR(-20012, 'Duplicate Staff ID Number');
			else
				RAISE_APPLICATION_ERROR(-20001, 'An unexpected error occurred. Unable to perform operation');
			end if;
		when others then
			RAISE_APPLICATION_ERROR(-20001, 'An unexpected error occurred. Unable to perform operation');
	END;