Query to get UserName by Service details in Fusion HCM

-
 SELECT DISTINCT priv.service
,      u.user_login         user_login
-- ,      pu.user_id
 ,      pu.person_id
-- ,      ppf.person_number
,      priv.code               privilege
,      priv.name               privilege_name
--,      priv.description        Privilege_Description
,      fr.top_role_id          role_id
,    rtl.role_name
,      fr.top_role_code        role_code,
pu.active_flag username_status,
pu.suspended suspend_status,
pu.hr_terminated,
(select paf.ASSIGNMENT_STATUS_TYPE 
from per_all_assignments_f paf
where paf.person_id = pu.person_id
and paf.primary_flag = 'Y'
  AND paf.assignment_type in ('E','C','P')
and paf.effective_latest_change = 'Y'
and paf.assignment_sequence = (select max(paf1.assignment_sequence)
from per_all_assignments_f paf1
where paf1.person_id = pu.person_id
and paf1.primary_flag = 'Y'
  AND paf1.assignment_type in ('E','C','P')
  and paf1.effective_latest_change = 'Y'
  and trunc(sysdate) BETWEEN paf1.EFFECTIVE_START_DATE and paf1.EFFECTIVE_END_DATE)
  and trunc(sysdate) BETWEEN paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
) ASSIGNMENT_STATUS_TYPE,
(select paf.ASSIGNMENT_number 
from per_all_assignments_f paf
where paf.person_id = pu.person_id
and paf.primary_flag = 'Y'
  AND paf.assignment_type in ('E','C','P')
and paf.effective_latest_change = 'Y'
and paf.assignment_sequence = (select max(paf1.assignment_sequence)
from per_all_assignments_f paf1
where paf1.person_id = pu.person_id
and paf1.primary_flag = 'Y'
  AND paf1.assignment_type in ('E','C','P')
  and paf1.effective_latest_change = 'Y'
  and trunc(sysdate) BETWEEN paf1.EFFECTIVE_START_DATE and paf1.EFFECTIVE_END_DATE)
  and trunc(sysdate) BETWEEN paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
) ASSIGNMENT_number,
  
  (select paf.assignment_type 
from per_all_assignments_f paf
where paf.person_id = pu.person_id
and paf.primary_flag = 'Y'
  AND paf.assignment_type in ('E','C','P')
and paf.effective_latest_change = 'Y'
and paf.assignment_sequence = (select max(paf1.assignment_sequence)
from per_all_assignments_f paf1
where paf1.person_id = pu.person_id
and paf1.primary_flag = 'Y'
  AND paf1.assignment_type in ('E','C','P')
  and paf1.effective_latest_change = 'Y'
  and trunc(sysdate) BETWEEN paf1.EFFECTIVE_START_DATE and paf1.EFFECTIVE_END_DATE)
  and trunc(sysdate) BETWEEN paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
) assignment_type,
  
  (select paf.action_code 
from per_all_assignments_f paf
where paf.person_id = pu.person_id
and paf.primary_flag = 'Y'
  AND paf.assignment_type in ('E','C','P')
and paf.effective_latest_change = 'Y'
and paf.assignment_sequence = (select max(paf1.assignment_sequence)
from per_all_assignments_f paf1
where paf1.person_id = pu.person_id
and paf1.primary_flag = 'Y'
  AND paf1.assignment_type in ('E','C','P')
  and paf1.effective_latest_change = 'Y'
  and trunc(sysdate) BETWEEN paf1.EFFECTIVE_START_DATE and paf1.EFFECTIVE_END_DATE)
  and trunc(sysdate) BETWEEN paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
) action_code 
FROM   fusion.ase_user_role_mbr ur
,      fusion.ase_role_tl  rtl
,      fusion.ase_user_b u
,      fusion.per_users pu
,    ( SELECT CONNECT_BY_ROOT parent_role_code top_role_code
       ,      CONNECT_BY_ROOT parent_role_id   top_role_id
       ,      CONNECT_BY_ROOT parent_role_guid top_role_guid
       ,      child_role_code role_code
       ,      child_role_id   role_id
       ,      child_role_guid role_guid
       FROM ( SELECT pr.code      parent_role_code
              ,      pr.role_id   parent_role_id
              ,      pr.guid      parent_role_guid
              ,      cr.code      child_role_code
              ,      cr.role_id   child_role_id
              ,      cr.guid      child_role_guid
              FROM fusion.ase_role_b pr
              ,    fusion.ase_role_b cr
              ,    fusion.ase_role_role_mbr rr
              WHERE rr.effective_end_date IS NULL
              AND   cr.role_id = rr.parent_role_id
              AND   pr.role_id = rr.child_role_id ) role_hierarchy
       CONNECT BY NOCYCLE PRIOR child_role_id = parent_role_id
       UNION ALL
       SELECT r.code
       ,      r.role_id
       ,      r.guid
       ,      r.code
       ,      r.role_id
       ,      r.guid
       FROM   fusion.ase_role_b r) fr
,    ( SELECT privrole.role_id
       ,      priv.code
       ,      privtl.name
       ,      privtl.description
       ,      privrole.effective_start_date policy_start_date
       ,      privrole.effective_end_date policy_end_date
       ,      metric.metric_name service
       FROM   fusion.ase_priv_role_mbr privrole
       ,      fusion.ase_privilege_b priv
       ,      fusion.ase_privilege_tl privtl
       ,    ( SELECT vl.translated_value metric_set_code
              ,      vl.description      metric_set_name
              FROM   fnd_vs_value_sets vs
              ,      fnd_vs_values_b v
              ,      fnd_vs_values_tl vl
              WHERE  vs.value_set_code = 'ORA_PER_METRICS_SETS'
              AND    v.value_set_id = vs.value_set_id
              AND    v.enabled_flag = 'Y'
              AND    SYSDATE between nvl(v.start_date_active , SYSDATE) AND nvl(v.end_date_active , SYSDATE)
              AND    vl.value_id = v.value_id
              AND    vl.language = 'US' ) metricset
       ,    ( SELECT vs.value_set_code metric_set_code
              ,      vl.translated_value metric_code
              ,      vl.description metric_name
              FROM   fnd_vs_value_sets vs
              ,      fnd_vs_values_b v
              ,      fnd_vs_values_tl vl
              WHERE  v.value_set_id = vs.value_set_id
              AND    v.enabled_flag = 'Y'
              AND    v.flex_value_attribute17 is null
              AND    SYSDATE between nvl(v.start_date_active , SYSDATE) AND nvl(v.end_date_active , SYSDATE)
              AND    vl.value_id = v.value_id
              AND    vl.language = 'US' ) metric
       ,    ( SELECT vs.value_set_code metric_code
              ,      vl.translated_value priv_code
              FROM   fnd_vs_value_sets vs
              ,      fnd_vs_values_b v
              ,      fnd_vs_values_tl vl
              WHERE  v.value_set_id = vs.value_set_id
              AND    v.enabled_flag = 'Y'
              AND    SYSDATE between nvl(v.start_date_active , SYSDATE) AND nvl(v.end_date_active , SYSDATE)
              AND    vl.value_id = v.value_id
              AND    vl.language = 'US' ) metricpriv
       WHERE  priv.privilege_id = privrole.privilege_id
       AND    priv.privilege_id = privtl.privilege_id
       AND    privtl.Language  = 'US'
       AND    metric.metric_set_code = metricset.metric_set_code
       AND    metricpriv.metric_code = metric.metric_code
       AND    priv.code = metricpriv.priv_code ) priv
WHERE  ur.user_id = u.user_id
AND    rtl.role_id = ur.role_id
AND    rtl.language = 'US'
AND    fr.top_role_id = ur.role_id
AND    priv.role_id = fr.role_id
AND    NVL( ur.effective_end_date , SYSDATE ) >= SYSDATE 
AND    priv.policy_start_date <= NVL( ur.effective_end_date , SYSDATE )
AND    NVL( priv.policy_end_date , SYSDATE ) >= SYSDATE
AND    u.user_guid = pu.user_guid
AND  ( pu.suspended is null or pu.suspended = 'N' )
AND    u.user_login not in
       ( 'orcladmin'
       , 'weblogic_read_only'
       , 'xelsysadm'
       , 'oamSoftwareUser'
       , 'IDROUser'
       , 'PolicyROUser'
       , 'PolicyRWUser'
       , 'oimAdminUser'
       , 'IDMPolicyROUser'
       , 'IDMPolicyRWUser'
       , 'faoperator'
       , 'saas_readonly'
       , 'em_monitoring'
       , 'oamAdminUser'
       , 'weblogic_idm'
       , 'IDRWUser'
       , 'PUBLIC'
       , 'FAAdmin'
       , 'weblogic'
       , 'XELOPERATOR'
       , 'OblixAnonymous'
       , 'OCLOUD9_osn_APPID' )
AND    u.user_login not like 'FUSION_APPS_%_APPID'
order by service, user_login

Comments

Post a Comment

Popular posts from this blog

Query to get Salary Change details in Fusion HCM

-
 SELECT papf.person_number PERSON_NUMBER, (select full_name from per_person_names_f ppnf where ppnf.person_id = papf.person_id and name_type = 'GLOBAL' and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date) person_name, ple.name LEGAL_EMPLOYE, (CASE  when cs.SALARY_BASIS_CODE = 'ANNUAL' THEN 'Salary Change'  else  'Hourly Rate Change' end)  ACTION_NAME, to_char(cs_old.salary_amount) OLD_DATA,  to_char(CS.salary_amount) NEW_DATA    ,TO_CHAR(cs.date_from, 'MM/DD/YYYY') EFFECTIVE_DATE    ,  TO_CHAR(cs.creation_date, 'MM/DD/YYYY') CHANGED_DATE    , cs.LAST_UPDATED_BY CHANGED_BY           from  fusion.CMP_SALARY cs,           CMP_SALARY cs_old,   PER_ALL_ASSIGNMENTS_M  PAAM,   PER_ALL_PEOPLE_F PAPF   ,per_legal_employers ple          where 1 = 1    AND PAPF.PERSON_ID = P...
Read more

Oracle Fusion HCM - Enhancements to Summary of Changes and History Sections in Redwood Workforce Structures Pages

-
Image
 The  Summary of Changes  and  History  sections on Redwood  Workforce Structures  pages now display  additional attributes , providing a more detailed view of historical and recent changes to your workforce data. These enhancements apply to the following Redwood pages: Redwood Page New Attributes in Summary of Changes and History Sections Departments Status Divisions Status Disability Organizations Status Enterprise HCM Information Status Jobs Annual Working Duration Scheduling Group Standard Annual Working Duration Standard Working Hours Standard Working Frequency Locations Annual Working Duration Standard Annual Working Duration Standard Working Hours Standard Working Frequency Reporting Establishments Status Worker Unions Status These additional attributes are now visible in both the  Summary of Changes  and  History  sections, enabling users to quickly access and review key data points related to structural and job-related...
Read more

🧾 Streamlining Payroll Operations with Oracle Payroll Activity Center

-
Image
Managing payroll efficiently across global teams has never been more critical—or more complex. Oracle Fusion HCM introduces the  Payroll Activity Center , part of the Redwood user experience, to help payroll teams take control with a centralized, intelligent, and highly actionable interface. Designed with usability and impact in mind, this hub simplifies payroll monitoring, boosts accuracy, and brings AI-powered insight to the forefront. 🌟 What Is the Payroll Activity Center? The  Payroll Activity Center  is an action-focused workspace tailored to payroll professionals. It aggregates relevant applications, displays role-specific KPIs, and provides intuitive navigation through the payroll cycle—all within Oracle’s new Redwood design system. This replaces the older Payroll Dashboard and brings everything you need into a single, streamlined view. 🔑 Key Features Aggregated Applications View relevant payroll applications, flows, and data all in one place. In-Workflow Communi...
Read more