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!

Query to display Responsibility wise profile Options and it's Values

Discussion in 'Oracle Apps Technical' started by jagadekara, Sep 13, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    The following is a Query to display Responsibility wise profile Options and it's Values

    Code (SQL):
    SELECT fu.user_name
          ,fr.responsibility_name
          ,fpo.user_profile_option_name
          ,NVL(xxtest_sql(fpo.sql_validation,fpov.profile_option_value,
              fpo.profile_option_name),fpov.profile_option_value) profile_option_value
      FROM 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 fr.responsibility_id= furg.responsibility_id
       AND (furg.end_date >sysdate OR furg.end_date IS NULL)
       AND fpov.level_value=fr.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 ('HR:User Type','JG: Territory','JG: Application','JG: Product','Upload File Size Limit',
                                            'MO: Security Profile','MO: Operating Unit','HR:Business Group','HR: Security Profile',
                                            'GL Ledger Name','GL: Data Access Set','Sequential Numbering','AP: Use Invoice Batch Controls',
                                            'MO: Default Operating Unit','SLA: Enable Data Access Set Security in Subledger',
                                            'FSG: Expand Parent Value','SLA: Enable Subledger Transaction Security in GL',
                                            'Default Country','OIE:Allow Credit Lines','OIE:Allow Non-Base Pay','OIE:Approver Required',
                                            'OIE:Enable Approver','OIE:Enable Cost Center','OIE:Enable Expense Allocations',
                                            'OIE:Purpose Required','OIE:Report Number Prefix','OIE:Carry Advances Forward','OIE:Enable Advances')
    ORDER BY 2,3



    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;