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;