Logging into the Application from PL/SQL

When you're writing PL/SQL code it's pretty normal to want to pretend to be a particular user in a particular responsibility. That way you can write your code 'correctly' against the application views etc but still be able to test it in fine detail from your chosen PL/SQL IDE.

BTW - my IDE of choice - and I've been using it for 10 years now - is the excellent PL/SQL Developer. It has an great integrated debugger for stepping line by line through your code.

Here's a bit of code you can compile onto your dev environment and use in test scripts to simulate being logged in as a particular user.

CREATE OR REPLACE PROCEDURE apps_login(p_user_name VARCHAR2, p_resp_name VARCHAR2) is

/* Shamless Plug!
Visit ebsconsultant.blogspot.com */

v_user_id BINARY_INTEGER;
v_resp_id BINARY_INTEGER;
v_app_id BINARY_INTEGER;
BEGIN
SELECT user_id
INTO v_user_id
FROM APPS.FND_USER
WHERE user_name like p_user_name;

select r.responsibility_id,
urg.responsibility_application_id
into v_resp_id,
v_app_id
from fnd_user_resp_groups urg,
fnd_responsibility_vl r
WHERE r.RESPONSIBILITY_NAME LIKE p_resp_name AND
r.responsibility_id = urg.responsibility_id AND
urg.user_id = v_user_id;

dbms_output.put_line('user_id=' || v_user_id || ' resp_id=' || v_resp_id || ' app_id=' || v_app_id);
FND_GLOBAL.APPS_INITIALIZE(v_user_id, v_resp_id, v_app_id);
FND_SIGNON.set_session(NULL);
COMMIT;
END;

Automating Password Resets

As a freelance EBS Consultant I can end up in all sorts of roles. Sometimes I'm the functional lead for a module, sometimes I'm doing some hard core SQL geekery, but usually, no matter what exciting bits and pieces I'll be working on there is always (unless I'm lucky enough to be working on a greenfield implementation) the background hum of disgruntled users who need supporting.

One common support task is resetting user passwords.

Most grown up websites let the user automatically request a password reset if they can't remember, or if they've locked themselves out of their account.

Since the reasonably early days of 11i Oracle Applications has also offered this functionality but at many clients it isn't enabled by default.

If, when logging in through the web page (as opposed to logging in directly to the forms - see my posting here on how to quickly get into the forms applications) you don't see an option to allow you to reset your password, you need to do the following:

• Find the system profile option called "Local Login Mask"
• Add 8 to it's current value at the Site level.
  
• Bounce the Application Server

Voila!

Oracle uses standard Workflow functionality and email notifications to ensure the user wants their password resetting and notifying them of their new password.

In order for the solution to work then, you need to make sure that Workflow Background engine is ticking along and that all your Users are mapped to email addresses - either directly through the User Define form in System Administrator, or through the user's mapping to a Person - and making sure the Person has an email address defined.

At most sites this will already be in place - so this is a quick win to reduce the volume of support calls you'll be dealing with.

A more detailed explanation can be found here.

Logging into the Forms based applications directly

Back in the good old days, before Oracle decided that HTML based applications was the way to go - you used to log directly in to the Forms based application.

You can still accomplish this - even I believe in R12 - but certainly all the way up to 11.5.10.2.

Simply use a URL like:

http://myappsserver.mycompany.com:[apps port number]/dev60cgi/f60cgi

This will take you directly to the forms version of the application. I've used this approach for years and the only problem I've really found is that, once logged in this way, you can't use any menu options which would jump you back out a HTML bit of the Application. (e.g. Workflow Administrator stuff).

I often just need to login to the Apps to make a targeted small change (e.g. change a system profile option). Having one desktop shortcut for each environment in the above format lets me dive in and out of the application much quicker than having to trawl through the web based login procedure.

Something else that is useful I find is to colour my environments by

Musing and Ramblings (not necessarily in that order)

I'm an Oracle Applications (E-Business Suite) consultant with a broad range of functional and technical experience acquired over 10+ years in the field.

In this blog I hope to share with you random tit-bits I come across. Perhaps little bits of code/sql I've developed; maybe applications functionality that is interesting but rarely known about; or maybe 3rd party solutions to some of the things that we just seem to keep having to do over and over again.