ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

SELECT fu.user_name user_name
      ,fu.description user_description
      ,fu.email_address user_email
      ,ppf.full_name employee_name
      ,hou.name business_group
      ,fr.responsibility_name resp_name
FROM apps.fnd_user fu
    ,apps.per_all_people_f ppf
    ,apps.hr_all_organization_units hou
    ,apps.fnd_user_resp_groups_all fur
    ,apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
--AND fr.responsibility_name LIKE 'Shipping User'
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name
        ,fr.responsibility_name

Active Employees and Their Jobs in oracle apps

Active Employees and Their Jobs

SELECT papf.full_name emp_name,
      papf.current_employee_flag current_emp_flag,
      papf.employee_number emp_number,
      b.d_job_id job_title,
      papf.email_address email

FROM  apps.PER_ALL_PEOPLE_F       papf,
      apps.PER_ALL_ASSIGNMENTS_F asg,
      apps.PER_ASSIGNMENTS_V7    b,
      apps.PER_JOBS              pjb
   
WHERE papf.person_id       = asg.person_id(+)
AND asg.person_id          = b.person_id
AND b.effective_start_date =
  (SELECT MAX (b2.effective_start_date)
  FROM apps.per_assignments_v7 b2
  WHERE b2.person_id = b.person_id
  )
AND NVL (papf.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (asg.effective_end_date, SYSDATE  + 1) > SYSDATE
AND b.job_id                                   = pjb.job_id(+)
ORDER BY papf.full_name

Employee supervisor Oracle R12 SQL Query

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id

  ORDER BY leve1_full_name;