To View users attached to Profile Option and their respective Responsibilities, Menu Name, Request Group.

Requirement is as  shown below.

Count how many user attached to the responsibility, Responsibility name, Responsibility End Date(As per responsibility defining form to know responsibility is active or not), Application name, Menu Name, Request Group Name, Value of profile options (MO: Operating Unit, MO: Default Operating Unit, MO: Security profile, HR: Security Profile,  GL Ledger Name). Below query might help you which is created by you last time.

Perfect name of profile option is as given below.

MO: Operating Unit

MO: Default Operating Unit

MO: Security Profile

HR: Security Profile

GL Ledger Name

Note: We are getting correct information like Count how many user attached to the Responsibility, Responsibility name, Responsibility End Date(As per responsibility defining form to know responsibility is active or not), Application name, Menu Name, Request Group Name from the query which are marked in red colour. I need profile option values.

select

COUNT( FU.USER_ID) Users,

--user_name

a.responsibility_name

,b.end_date

,b.responsibility_key

,c.user_menu_name

,g.request_group_name request_group

,e.application_name

from FND_USER_RESP_GROUPS_DIRECT FNDG,

FND_USER FU,

fnd_responsibility_tl a

,fnd_responsibility b

,fnd_menus_tl c

,fnd_application_tl e

,fnd_request_groups g

WHERE 

FU.USER_ID = FNDG.USER_ID

--AND a.RESPONSIBILITY_name

and  e.application_name = :Application_name --Human Resources

and a. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

and b. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

and e.application_id = b.application_id

and g.request_group_id(+) = b.request_group_id

and c.menu_id = b.menu_id

and a.language = 'US'

and c.language = 'US'

and e.language = 'US'

group by a.responsibility_name,b.end_date,b.responsibility_key,c.user_menu_name, e.application_name,g.request_group_name

order by a.responsibility_name

 -----------------------------------------------------------------------------------------------------------------
 

select

                  --COUNT( FU.USER_ID) Users,

                     --user_name,

                  a.responsibility_name,fndg.end_date Resp_End_date

                  ,c.user_menu_name

                  ,e.application_name

                  ,g.request_group_name request_group

                  ,Decode(fpo.profile_option_name,'GL_SET_OF_BKS_NAME','GL Ledger Name','XLA_MO_SECURITY_PROFILE_LEVEL','MO: Security Profile','DEFAULT_ORG_ID','MO: Default Operating Unit','ORG_ID','MO: Operating Unit','PER_SECURITY_PROFILE_ID','HR: Security Profile','')Profile_Name

                  ,NVL(xxtest_sql(fpo.sql_validation,fpov.profile_option_value,fpo.profile_option_name),fpov.profile_option_value) Value_Profile

                  --Select Distinct fpo.profile_option_name

                  from

                  FND_USER_RESP_GROUPS_DIRECT FNDG,

                  FND_USER FU,

                  fnd_responsibility_tl a

                  ,fnd_responsibility b

                  ,fnd_menus_tl c

                  ,fnd_application_tl e

                  ,fnd_request_groups g

                         ,fnd_user_resp_groups_all furg

                        --,fnd_user fu

                        --,fnd_responsibility_vl fr

                        ,fnd_profile_option_values fpov

                        ,fnd_profile_options_vl fpo

                  WHERE

                  fu.user_id= furg.user_id

                     --AND fu.user_name=nvl(:P_USER,fu.user_name)

                     --AND fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)

                     AND a.responsibility_id= furg.responsibility_id

                     AND b.responsibility_id= furg.responsibility_id

                     --AND (furg.end_date >sysdate OR furg.end_date IS NULL)

                     AND fpov.level_value=a.responsibility_id

                     AND fpov.level_value=b.responsibility_id

                     AND fpov.profile_option_id= fpo.profile_option_id

                     AND fpov.application_id=fpo.application_id

                     AND fpo.user_profile_option_name in ( 'MO: Operating Unit','MO: Default Operating Unit','MO: Security Profile','HR: Security Profile','GL Ledger Name')

                  and FU.USER_ID = FNDG.USER_ID

                  --AND a.RESPONSIBILITY_name

                  --and  e.application_name = :Application_name --Human Resources

                  and a. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

                  and b. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

                  and e.application_id = b.application_id

                  and g.request_group_id(+) = b.request_group_id

                  and c.menu_id = b.menu_id

                  and a.language = 'US'

                  and c.language = 'US'

                  and e.language = 'US'

                  and user_name = nvl(:P_USER_NAME,user_name)-- give here user_name and check the ssame

                  --group by a.responsibility_name,c.user_menu_name, e.application_name,g.request_group_name

                  --,NVL(xxtest_sql(fpo.sql_validation,fpov.profile_option_value,fpo.profile_option_name),fpov.profile_option_value)

                  order by 1,2,5

Function:

CREATE OR REPLACE
  FUNCTION xxtest_sql
    (
      p_qry IN VARCHAR2,
      p_val IN VARCHAR2,
      p_name VARCHAR2)
    RETURN VARCHAR2
                          IS
    lv_qry VARCHAR2(4000) := p_qry;
    lv1    VARCHAR2(2000);
    sql1   VARCHAR2(4000);
    sql2   VARCHAR2(4000);
    lv2    VARCHAR2(2000);
    lv3    VARCHAR2(2000);
    lp1    VARCHAR2(100) := 'INT';
  BEGIN
    IF ( p_name LIKE 'HR%MENU' OR p_name ='IBU_HOME_QMENU_NAME') THEN
      SELECT user_menu_name
      INTO lv2
      FROM fnd_menus_vl fmv
      WHERE fmv.menu_name = p_val;
    ELSIF ( p_name        = 'PER_SECURITY_PROFILE_ID' ) THEN
      SELECT S.SECURITY_PROFILE_NAME
      INTO lv2
      FROM PER_SECURITY_PROFILES S,
        PER_BUSINESS_GROUPS O
      WHERE O.BUSINESS_GROUP_ID (+) = S.BUSINESS_GROUP_ID
      AND s.security_profile_id     = p_val;
    ELSIF (p_name                   = 'OM_UI_REFRESH_METHOD') THEN
      SELECT MEANING
      INTO lv2
      FROM OE_LOOKUPS
      WHERE LOOKUP_TYPE ='UI_REFRESH_METHOD'
      AND lookup_code   = p_val;
    ELSE
      -- DBMS_OUTPUT.put_line(replace(

substr(lv_qry,instr(lv_qry,'SQL=',1,1)+1,instr(lv_qry,'INTO',1,1)-1)||substr(lv_qry,instr(lv_qry,'FROM',1,1)-1),'"',null)||' AND '||substr(lv_qry,instr(lv_qry,',',1,1)+1,(instr(lv_qry,'INTO',1,1) -instr(lv_qry,',',1,1))-1)||' = ');
      lv1 := SUBSTR(lv_qry,1,INSTR(lv_qry,'\',1,1)-1)||SUBSTR(lv_qry,INSTR(lv_qry,'\',1,2)+1);
      lv1 := REPLACE(upper(lv1),'COLUMN = ','COLUMN=');
      lv1 := SUBSTR(lv1,1,
      CASE
      WHEN INSTR(upper(lv1),'COLUMN=',1,1) = 0 THEN
        LENGTH(lv1)
      ELSE
        INSTR(upper(lv1),'COLUMN=',1,1) -1
      END );
      lv1 := REPLACE (REPLACE(lv1,'SQL=',NULL),'SQL = ',NULL);
      lv1 := REPLACE(lv1,'"',NULL);
      lv1 := SUBSTR(lv1,1,INSTR(upper(lv1),'INTO',1,1)                                                           -1)||SUBSTR(lv1,INSTR(upper(lv1),'FROM',1,1));
      SELECT SUBSTR(lv1,1,DECODE(INSTR(upper(lv1),'ORDER BY',1,1),0,LENGTH(lv1),(INSTR(upper(lv1),'ORDER BY',1,1)-1)))
      INTO lv1
      FROM dual ;

      sql1 := lv1||
      CASE
      WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN
        ' WHERE '
      ELSE
        ' AND '
      END ||SUBSTR(lv1,INSTR(lv1,',',1,1)+1,(INSTR(upper(lv1),'FROM',1,1)-INSTR(lv1,',',1,1))-1)||' = '''||p_val||'''';
      BEGIN
        EXECUTE IMMEDIATE sql1 INTO lv2,
        lv3;
      EXCEPTION
      WHEN OTHERS THEN
        lv2 := NULL;
      END;
      IF(lv2 IS NULL) THEN
        sql2 := lv1||
        CASE
        WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN
          ' WHERE '
        ELSE
          ' AND '
        END ||SUBSTR(lv1,INSTR(upper(lv1),'SELECT',1,1)+6,(INSTR(lv1,',',1,1)-INSTR(upper(lv1),'SELECT',1,1)-6))||' = '''||p_val||'''';
        EXECUTE IMMEDIATE sql2 INTO lv3,
        lv2;
      END IF;
    END IF;
    RETURN lv2 ;
  EXCEPTION
  WHEN OTHERS THEN
    -- return (Sql1);
    RETURN NULL;
  END;