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;