DECLARE
p_userid NUMBER;
p_respid NUMBER;
p_applid NUMBER;
x_resp_name VARCHAR2(50);
BEGIN
SELECT user_id
INTO p_userid FROM fnd_user
WHERE user_name ='MFG';
SELECT responsibility_id
INTO p_respid FROM fnd_responsibility_tl
WHERE responsibility_name = 'Purchasing, Vision Operations (USA)';
p_applid := 201; -- Puchasing Module
fnd_global.apps_initialize(p_userid, p_respid, p_applid);
SELECT fnd_profile.value('RESP_NAME') INTO x_resp_name FROM dual;
Dbms_Output.put_line(x_resp_name);
END;
Similarly
SELECT fnd_profile.value('PROFILEOPTION')
,fnd_profile.value('MFG_ORGANIZATION_ID')
,fnd_profile.value('ORG_ID')
,fnd_profile.value('LOGIN_ID')
,fnd_profile.value('USER_ID')
,fnd_profile.value('USERNAME')
,fnd_profile.value('CONCURRENT_REQUEST_ID')
,fnd_profile.value('GL_SET_OF_BKS_ID')
,fnd_profile.value('SO_ORGANIZATION_ID')
,fnd_profile.value('APPL_SHRT_NAME')
,fnd_profile.value('RESP_NAME')
,fnd_profile.value('RESP_ID')
FROM DUAL;