3 Replies Latest reply on Sep 18, 2017 6:46 PM by stejac

    Profile Option Values With Site and Responsibility Level With User Details

    976758
      Dear All,

       

      I am developing a query to extract profile values of 'HR: Security Profile' with user details,
      following is the format and query.

       

      Active Responsibility Name

      Responsibility Level

      Site Level

      User Level

      User Name

      Employee Number

      Employee Name


       

      Query
      ---
      /* Formatted on 2017/09/12 09:37 (Formatter Plus v4.8.8) */
      SELECT DISTINCT fpo.user_profile_option_name,
      frt.responsibility_name "Active Responsibility Name",
      fat.application_name "Application Name",
      psp.security_profile_name "Responsibility level",
      fu.user_name "User Name",
      (SELECT DISTINCT employee_number
      FROM per_all_people_f papf
      WHERE papf.person_id =
      fu.employee_id
      AND TRUNC (SYSDATE)
      BETWEEN papf.effective_start_date
      AND papf.effective_end_date)
      employee_number,
      (SELECT DISTINCT full_name
      FROM per_all_people_f papf
      WHERE papf.person_id = fu.employee_id
      AND TRUNC (SYSDATE)
      BETWEEN papf.effective_start_date
      AND papf.effective_end_date)
      full_name,
      (SELECT DISTINCT current_employee_flag
      FROM per_all_people_f papf
      WHERE papf.person_id =
      fu.employee_id
      AND TRUNC (SYSDATE)
      BETWEEN papf.effective_start_date
      AND papf.effective_end_date)
      current_employee_flag,
      CASE
      WHEN NVL (fu.end_date, SYSDATE) >= SYSDATE
      AND encrypted_user_password <> 'INVALID'
      THEN 'Active'
      WHEN NVL (fu.end_date, SYSDATE) < SYSDATE
      THEN 'In Active'
      WHEN encrypted_user_password = 'INVALID'
      THEN 'LOCKED'
      WHEN encrypted_user_password = 'EXTERNAL'
      THEN 'EXTERNAL'
      WHEN encrypted_user_password = 'EXTERNAL'
      THEN 'EXTERNAL'
      WHEN encrypted_user_password = 'EXTERNAL'
      THEN 'PENDING'
      WHEN encrypted_user_password = 'UNASSIGNED'
      THEN 'UNASSIGNED'
      END emp_status,
      profile_option_value
      FROM fnd_user_resp_groups_direct furg,
      applsys.fnd_user fu,
      applsys.fnd_responsibility_tl frt,
      applsys.fnd_responsibility fr,
      applsys.fnd_application_tl fat,
      applsys.fnd_application fa,
      fnd_profile_option_values fpov,
      fnd_profile_options_vl fpo,
      fnd_responsibility_vl frv,
      applsys.fnd_profile_options_tl fpotl,
      apps.per_business_groups pbg,
      hr.per_security_profiles psp
      WHERE furg.user_id = fu.user_id
      AND furg.responsibility_id = frt.responsibility_id
      AND fr.responsibility_id = frt.responsibility_id
      AND fa.application_id = fat.application_id
      AND fr.application_id = fat.application_id
      AND fpov.level_value = frt.responsibility_id
      AND fpo.profile_option_id = fpov.profile_option_id
      AND furg.responsibility_id = frv.responsibility_id
      AND fpotl.profile_option_name = fpo.profile_option_name
      AND psp.business_group_id = pbg.business_group_id(+)
      AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
      AND fpotl.user_profile_option_name = 'HR: Security Profile'
      --and frt.responsibility_name='Global Super HRMS Manager'
      AND frt.LANGUAGE = 'US'
      AND fpotl.LANGUAGE = 'US'
      AND fat.LANGUAGE = 'US'
      --AND fpov.level_id = 10003
      --and fu.user_name='WAHMED6'
      --and fpov.LEVEL_VALUE is not null
      AND furg.start_date <= SYSDATE
      AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
      AND frv.start_date <= SYSDATE
      AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
      ORDER BY 1;

       

      Here, I need to get the site level and user level information, I'm not able to get the exact information.

       

      Could anyone help me how to get the site level information of profile?

       

      Thanks in advance.

       

      Regards
        • 1. Re: Profile Option Values With Site and Responsibility Level With User Details
          976758

          Could any one help me on this please

          • 2. Re: Profile Option Values With Site and Responsibility Level With User Details
            Jim-D

            Welcome!  The Getting Started community is not intended for product specific questions.

            Could you please specify the name of the Oracle software you're using, so an admin can move your post to the appropriate product space for you?

            • 3. Re: Profile Option Values With Site and Responsibility Level With User Details
              stejac

              just merge the 2 next select

               

              select p.profile_option_name SHORT_NAME,

                     n.user_profile_option_name NAME,

                     decode(v.level_id,10001,'Site',

                                       10002,'Application',

                                       10003,'Responsibility',

                                       10004,'User',

                                       10005,'Server',

                                       10006,'Org',

                                       10007, decode(to_char(v.level_value2),'-1','Responsibility',decode(to_char(v.level_value),'-1','Server','Server+Resp')),

                            'UnDef') LEVEL_SET,

                     decode(to_char(v.level_id),'10001','',

                                                '10002',app.application_short_name,

                                                '10003',rsp.responsibility_key,

                                                '10004',usr.user_name,

                                                '10005',svr.node_name,

                                                '10006',org.name,

                                                '10007',decode(to_char(v.level_value2),'-1',rsp.responsibility_key,

                                                          decode(to_char(v.level_value),'-1',(select node_name from fnd_nodes where node_id = v.level_value2),

                                                            (select node_name from fnd_nodes where node_id = v.level_value2) || '-' || rsp.responsibility_key)),

                            'UnDef') "CONTEXT",

                     v.profile_option_value VALUE

                from fnd_profile_options       p,

                     fnd_profile_option_values v,

                     fnd_profile_options_tl    n,

                     fnd_user                  usr,

                     fnd_application           app,

                     fnd_responsibility        rsp,

                     fnd_nodes                 svr,

                     hr_operating_units        org

              where p.profile_option_id = v.profile_option_id(+)

                 and p.profile_option_name = n.profile_option_name

                 and upper(p.profile_option_name)-- = 'XX_APPROB_ECR_GL'

                 --like ('POS%') or upper(p.profile_option_name) like ('PON%'))

                 in --('OKC_ARTICLE_DOC_SEQ_CATEGORY', 'UNIQUE:SEQ_NUMBERS')

                          (select profile_option_name

                             from fnd_profile_options_tl

                            where upper(user_profile_option_name) like

                                  --upper('%OKC%clause%')

                                  --upper('OKC:Clause Sequence Category')

                                  upper('OSO : Langue préférée par défaut')

                                  )

                 and usr.user_id(+) = v.level_value

                 and rsp.application_id(+) = v.level_value_application_id

                 and rsp.responsibility_id(+) = v.level_value

                 and app.application_id(+) = v.level_value

                 and svr.node_id(+) = v.level_value

                 and org.organization_id(+) = v.level_value

                 and n.LANGUAGE ='FRC' --'US'

              order by short_name, user_profile_option_name, level_id, level_set;

               

               

              SELECT *

              FROM WF_LOCAL_ROLES w,

                   fnd_user u,

                   per_all_people_f p

              where w.name = u.user_name

              and u.EMPLOYEE_ID = p.person_id;