Add inactive account alert in E-Business suite

So you want to know if no one has logged in to e-Business Suite in 6 months and then disable their account? Well you can use Oracle Alert in e-business suite to accomplish just that.

First off you will need the "Alert Manager" responsibility. Then create the following:

The select statement I am using above is:

SELECT
    substr(vdb.name, 1, 8),
    fu.user_name,
    to_char(fu.last_logon_date),
    fu.creation_date,
    apps.hr_employees.employee_id,
    apps.hr_employees.full_name,
    apps.hr_employees.email_address,
    fu.end_date,
    fu.start_date
   into &DB_NAME, &USER_NAME, &LAST_LOGON, &CREATION_DATE &EMPLOYEE_ID, &FULL_NAME, &EMAIL_ADDRESS
FROM
    fnd_user     fu
    INNER JOIN apps.hr_employees ON apps.hr_employees.employee_id = fu.employee_id,
    v$database   vdb
WHERE
    fu.user_name NOT LIKE 'XX%'
    AND fu.user_name NOT IN (
        'MRPUSER',
        'SYSADMIN'
    )
    AND nvl(fu.end_date, sysdate + 1) >= sysdate
    AND sysdate - nvl(fu.last_logon_date, '01-JAN-01') > 180
    AND sysdate - fu.start_date > 180
ORDER BY
    fu.user_name;

You can see that "Alert Details" and "Outputs" shows the variables that this sql will populate:

Now we need to establish a couple of Actions for this alert to perform when it finds inactive users. We want it to send us an email of the users with some details and we then want it to expire the user accounts. So let's create the "Actions" and assign them to an "Action Set".

First one is "Notify Apps Admin"

Below is the text in full:

Following user accounts have been inactive for more than 90 days

=**= Enter summary template below this line =**=
** User Name: &USER_NAME
Full Name: &FULL_NAME
Last Logon: &LAST_LOGON
Email: &EMAIL_ADDRESS
-------------------------------------------------------------------
=**= Enter summary template above this line =**=

Now we need to create an action for disabling inactive users. The "Action Type" is "sql statement script"

 

You can cut and paste the code from below:

exec apps.fnd_user_pkg.DisableUser('&USER_NAME');

*Note: don't end the code with a slash "/". I know it's normal and that the docs say you can but if you do the request will never complete. It will just run forever. Also don't update the fnd_users table. Use the package above as updating any table in ebs is not approved by oracle. You must use the api.

Now you should have the two actions assigned to the Alert. As you can see below I left my termination of user accounts disabled. Do some testing, make sure you aren't terminating something you shouldn't (CEO, your manager you get the idea) and then when ready, hit enable on the second action.

You can do more with this. In fact you could create another action that sends a message to the user telling them you disabled their account and why. You can even create a response set so that if the user responds to your email with a specific phrase their account is enabled again. However I'll leave that to you. If I ever do that I'll update this doc with the details.