1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need Query for Responsibility wise profile options and values

Discussion in 'Oracle Apps Technical' started by jagadekara, Aug 22, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I need a query to display responsibility name, profile option name and profile value in oracle apps r12.

    I got Responsibility name and profile option name but I am not able to get Profile value.

    Navigation
    ----------

    System Adiministrator resp --> Profile -->System

    here un check site and profiles with no values and enter any responsibility name and say find.

    In first column Profile option name is coming. In 4th column some values are there. So I need those values as it is.

    Any help?
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekar,

    You can get the profile option values with the following query:

    Code (SQL):
    SELECT b.user_profile_option_name "Long Name" ,
      a.profile_option_name "Short Name" ,
      NVL(g.responsibility_name,c.level_value)  "Level Value" ,
      c.PROFILE_OPTION_VALUE "Profile Value",
      b.sql_validation
    FROM apps.fnd_profile_options a ,
      apps.FND_PROFILE_OPTIONS_VL b ,
      apps.FND_PROFILE_OPTION_VALUES c ,
      apps.FND_USER d ,
      apps.FND_USER e ,
      apps.FND_RESPONSIBILITY_VL g ,
      apps.FND_APPLICATION h
    WHERE 1                   =1
    AND a.profile_option_name = b.profile_option_name
    AND a.profile_option_id   = c.profile_option_id
    AND a.application_id      = c.application_id
    AND c.last_updated_by     = d.user_id (+)
    AND c.level_value         = e.user_id (+)
    AND c.level_value         = g.responsibility_id (+)
    AND c.level_value         = h.application_id (+)
      --
    AND c.level_id            = 10003
    AND g.responsibility_name = 'US Super HRMS Manager'
    ORDER BY b.user_profile_option_name,  c.level_id
    ;
    As for your specific line:
    I'm afraid, this gonna be quite complex as you can imagine if you run above query (by modifying and using your responsibility name on line "AND g.responsibility_name = 'US Super HRMS Manager' ") and check the b.sql_validation field from which I guess the displayed value comes given that the profile value is very contextual.

    Hope this will help.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks for the reply Rajen,

    Your query gave below details.

    LONG Name Short Name Level value Profile Value SQL_VALIDATION
    -------------------------------------------------------------------------------------------------------------------------------------------------
    HR:User Type HR_USER_TYPE US Super HRMS Manager INT "SQL="SELECT L.MEANING User_Type, L.LOOKUP_CODE
    INTO :VISIBLE_OPTION_VALUE, :pROFILE_OPTION_VALUE
    FROM FND_COMMON_LOOKUPS L
    WHERE L.LOOKUP_TYPE = 'HR_USER_TYPE'
    AND L.APPLICATION_ID BETWEEN 800 AND 899"
    COLUMN="User_Type(*)""

    But I need to display Profile value as HR with Payroll User

    If we run the query in SQL Validation giving that record, but I need it in same query.

    For each line seperate SQl validation is there. So how can we achieve this?
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    That's what I meant to say by above. It looks more like a LOV/Validation type of statement which is used directly in Oracle Forms to build LOV/Validation and is specific to each profile option. Extracting the SQL part from this field dynamically to run in a standalone query would be quite complicated.

    You can try using SUBSTR/REGEXP_SUBSTR/REPLACE etc.... I'll give a try in my spare time. :)

    Note: Haven't seen any API neither which could help :(
     
  5. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekara,

    As Rajen said it's complex to get as it is values. Even if you check the Form Code they have used the below code .

    user_exit('FND VALIDPROFILE :pROFILE_VALUES.SQL_VALIDATION '||
    ':pROFILE_VALUES.USER_PROFILE_OPTION_NAME '||
    val_mode||' :GLOBAL.FND_POM_CANCEL');


    Here user_exit is a different language (other than sql or Pl/Sql) function.


    Regards
    Sambasiva Reddy.K
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen and Samba...
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekar,

    For this I have created one function. Check with the below function and query. It is not working for some profiles. This case get the query from Profile Options table and hard code that Profile Option name then get the value from that query.

    Function:

    Code (SQL):
    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') 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;
       
       ELSE
               
                  lv1 := SUBSTR(lv_qry,1,INSTR(lv_qry,'\',1,1)-1)||SUBSTR(lv_qry,INSTR(lv_qry,'\',1,2)+1);
                   lv1 := replace(lv1,'
    COLUMN = ','COLUMN=');
                  lv1 := SUBSTR(lv1,1,CASE WHEN INSTR(lv1,'
    COLUMN=',1,1) = 0 THEN length(lv1) ELSE INSTR(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  (null);
    END;
    Query :

    Code (SQL):
    SELECT po.profile_option_name "NAME", po.user_profile_option_name,
    DECODE (TO_CHAR (pov.level_id),
    '10001', 'SITE',
    '10002', 'APP',
    '10003', 'RESP',
    '10005', 'SERVER',
    '10006', 'ORG',
    '10004', 'USER',
    '???'
    ) "LEVEL",
    DECODE (TO_CHAR (pov.level_id),
    '10001', '',
    '10002', app.application_short_name,
    '10003', rsp.responsibility_key,
    '10005', svr.node_name,
    '10006', org.NAME,
    '10004', usr.user_name,
    '???'
    ) "CONTEXT",  rsp.RESPONSIBILITY_NAME,
    pov.profile_option_value "VALUE"
    , NVL(xxtest_sql( po.sql_validation  ,pov.profile_option_value,po.profile_option_name ),pov.profile_option_value) val
    ,po.sql_validation
    FROM apps.fnd_profile_options_vl po,
    apps.fnd_profile_option_values pov,
    apps.fnd_user usr,
    apps.fnd_application app,
    apps.fnd_responsibility_vl rsp,
    apps.fnd_nodes svr,
    apps.hr_operating_units org
    WHERE 1 = 1
    AND pov.application_id = po.application_id
    AND pov.profile_option_id = po.profile_option_id
    AND usr.user_id(+) = pov.level_value
    AND rsp.application_id(+) = pov.level_value_application_id
    AND rsp.responsibility_id(+) = pov.level_value
    AND app.application_id(+) = pov.level_value
    AND svr.node_id(+) = pov.level_value
    AND org.organization_id(+) = pov.level_value
    AND po.profile_option_name LIKE   'HR%US%'

    Regards
    Sambasiva Reddy.K
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank you so much Samba...

    It's working fine for my requirement (Only for Selected Profiles)