Why need change user name in this case?
I think you should only change users last name attribute.
Excellent answer Jari, but my users do not want to use the login name that carries their spouse's last name in it, since they are divorced. Tough bunch of folks
I do not know all logic what you have around APP_USER and how it work so it is hard to help.
Probably you need anyway program some own logic to handle this requirement.
Appreciate your help and time Jari, below is my game plan, unless someone has a better idea,
I plan on doing the following to use user_id for auditing of transaction records:
1. Get the user_id to use in the tables audit trail fields
user_id := apex_util.get_user_id(v('app_user'));
user_id := apex_util.get_current_user_id;
2. If a user changes their name then use
My 2 cents.
Generally you would never want to use the username as your primary/foreign key anywhere - a user id would be your best bet.
But considering your username is your "identity", typically identities don't change. Obviously your IT dept doesn't have a policy, but where I work identities (we use Active Directory) are considering authoritative and do not change. So if you get married, it doesn't change. If you get divorced, it doesn't change. You can obviously change the last name attribute of a person, but their identity shouldn't change based on life events.
Makes sense Scott. Will check and implement with my folks...