Şub
19
2019

Kullanıcı – Sorumluluk – Güvenlik Profili scripti

Yazar şafak bilir Yorumlar

Merhaba,

Aşağıdaki script ile belli bir departmandaki aktif çalışanlara ait kullanıcılara atanmış sorumluluklar, bu sorumluluklara bağlı menü (alt menülere girmeden) ve fonksiyonlar ve bu sorumluluklara atanmış güvenlik profili değerleri (HR: Security Profile) listelenebilir. Ayrıca, her bir güvenlik profiline ait, eğer var ise, atanmış organizasyon hiyerarşileri de görüntülenebilir. Menü ve fonksiyonları görüntülerken de, hariç bırakılmış (Menu Exclusions ekranından) kayıtları ayrıca görebilirsiniz.

Not: Bu arada, farklı bir bakış açısıyla daha önce Mustafa Korkmaz üstadımızın hazırlamış olduğu linkteki scripti de mutlaka inceleyin derim.



SELECT ad_soyad,
       org,
       responsibility_name,
       prompt,
       yetki,
       SECURITY_PROFILE_NAME,
       ORGANIZATION_HIERARCHY
  FROM (SELECT emp.ad_soyad,
               emp.org,
               fr.responsibility_name,
               fme.prompt,
               'Var' yetki,
               sec.SECURITY_PROFILE_NAME,
               sec.ORGANIZATION_HIERARCHY
          FROM fnd_responsibility_vl fr,
               fnd_menu_entries_vl fme,
               fnd_user_resp_groups_direct fug,
               fnd_user fus,
               (SELECT paf.person_id,
                       paf.employee_number,
                       paf.first_name || ' ' || paf.last_name ad_soyad,
                       hou.name org
                  FROM per_all_people_f paf,
                       per_all_assignments_f pas,
                       hr_all_organization_units hou
                 WHERE     pas.person_id = paf.person_id
                       AND pas.assignment_type = 'E'
                       AND pas.primary_flag = 'Y'
                       AND pas.organization_id = hou.organization_id
                       AND hou.name = :p_department
                       AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                               AND paf.effective_end_date
                       AND TRUNC (SYSDATE) BETWEEN pas.effective_start_date
                                               AND pas.effective_end_date) emp,
               (SELECT fv.level_id,
                       fv.LEVEL_VALUE,
                       sp.SECURITY_PROFILE_NAME,
                       sp.ORGANIZATION_HIERARCHY
                  FROM fnd_profile_options fo,
                       fnd_profile_option_values fv,
                       PER_SECURITY_PROFILES_V sp
                 WHERE     fv.level_id = 10003 -- sorumluluk bazlı tanımlanan profil değerleri için
                       AND fo.profile_option_id = fv.profile_option_id
                       AND fo.profile_option_name = 'PER_SECURITY_PROFILE_ID'
                       AND fv.PROFILE_OPTION_VALUE =
                              TO_CHAR (sp.SECURITY_PROFILE_ID)) sec
         WHERE     fr.menu_id = fme.menu_id
               AND fme.grant_flag = 'Y'
               AND fme.prompt IS NOT NULL
               AND fug.responsibility_id = fr.responsibility_id
               AND TRUNC (SYSDATE) BETWEEN fr.start_date
                                       AND NVL (fr.end_date,
                                                TRUNC (SYSDATE) + 1)
               AND TRUNC (SYSDATE) BETWEEN fug.start_date
                                       AND NVL (fug.end_date,
                                                TRUNC (SYSDATE) + 1)
               AND fus.user_id = fug.user_id
               AND fus.employee_id = emp.person_id
               AND fr.responsibility_id = sec.LEVEL_VALUE(+)
        UNION ALL
        -- exclusions
        (                                                     -- for sub menus
         SELECT emp.ad_soyad,
                emp.org,
                fr.responsibility_name,
                fm.user_menu_name prompt,
                'Alt Menü Hariç' yetki,
                NULL SECURITY_PROFILE_NAME,
                NULL ORGANIZATION_HIERARCHY
           FROM fnd_responsibility_vl fr,
                fnd_resp_functions frf,
                fnd_menus_vl fm,
                fnd_user_resp_groups_direct fug,
                fnd_user fus,
                (SELECT paf.person_id,
                        paf.employee_number,
                        paf.first_name || ' ' || paf.last_name ad_soyad,
                        hou.name org
                   FROM per_all_people_f paf,
                        per_all_assignments_f pas,
                        hr_all_organization_units hou
                  WHERE     pas.person_id = paf.person_id
                        AND pas.assignment_type = 'E'
                        AND pas.primary_flag = 'Y'
                        AND pas.organization_id = hou.organization_id
                        AND hou.name = :p_department
                        AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                                AND paf.effective_end_date
                        AND TRUNC (SYSDATE) BETWEEN pas.effective_start_date
                                                AND pas.effective_end_date)
                emp
          WHERE     fr.responsibility_id = frf.responsibility_id(+)
                AND frf.action_id = fm.menu_id
                AND frf.rule_type = 'M'
                AND fug.responsibility_id = fr.responsibility_id
                AND TRUNC (SYSDATE) BETWEEN fr.start_date
                                        AND NVL (fr.end_date,
                                                 TRUNC (SYSDATE) + 1)
                AND TRUNC (SYSDATE) BETWEEN fug.start_date
                                        AND NVL (fug.end_date,
                                                 TRUNC (SYSDATE) + 1)
                AND fus.user_id = fug.user_id
                AND fus.employee_id = emp.person_id
         UNION ALL
         --for functions
         SELECT emp.ad_soyad,
                emp.org,
                fr.responsibility_name,
                fff.user_function_name prompt,
                'Fonksiyon Hariç' yetki,
                NULL SECURITY_PROFILE_NAME,
                NULL ORGANIZATION_HIERARCHY
           FROM fnd_responsibility_vl fr,
                fnd_resp_functions frf,
                fnd_form_functions_vl fff,
                fnd_user_resp_groups_direct fug,
                fnd_user fus,
                (SELECT paf.person_id,
                        paf.employee_number,
                        paf.first_name || ' ' || paf.last_name ad_soyad,
                        hou.name org
                   FROM per_all_people_f paf,
                        per_all_assignments_f pas,
                        hr_all_organization_units hou
                  WHERE     pas.person_id = paf.person_id
                        AND pas.assignment_type = 'E'
                        AND pas.primary_flag = 'Y'
                        AND pas.organization_id = hou.organization_id
                        AND hou.name = :p_department
                        AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                                AND paf.effective_end_date
                        AND TRUNC (SYSDATE) BETWEEN pas.effective_start_date
                                                AND pas.effective_end_date)
                emp
          WHERE     fr.responsibility_id = frf.responsibility_id(+)
                AND frf.rule_type = 'F'
                AND frf.action_id = fff.function_id
                AND fug.responsibility_id = fr.responsibility_id
                AND TRUNC (SYSDATE) BETWEEN fr.start_date
                                        AND NVL (fr.end_date,
                                                 TRUNC (SYSDATE) + 1)
                AND TRUNC (SYSDATE) BETWEEN fug.start_date
                                        AND NVL (fug.end_date,
                                                 TRUNC (SYSDATE) + 1)
                AND fus.user_id = fug.user_id
                AND fus.employee_id = emp.person_id))

About
Latest Posts

şafak bilir

Gazi Üniversitesi Endüstri Mühendisliği bölümünden 2006 yılında mezun
oldu. Medical Park ve Florence Nightingale İnsan Kaynakları birimlerinde
çalıştı. Yaklaşık 2,5 yıl Workcube ERP yazılımı ile İnsan Kaynakları-Bordro
danışmanlığı ve Proje Yöneticiliği görevlerinde bulundu. 2014 yılından bu
yana ise Oracle HR modülü İş Analisti olarak çalışmaktadır.