Tuesday 29 April 2014

Get both User and Employee details in HRMS

SELECT DISTINCT NVL
                      (b.full_name,
                       (SELECT DISTINCT full_name
                                   FROM per_all_people_f ppf
                                  WHERE ppf.person_id = u.employee_id
                                    AND SYSDATE BETWEEN ppf.effective_start_date
                                                    AND ppf.effective_end_date)
                      ) full_name,
                   u.user_name,
                   DECODE
                      (b.user_person_type,
                       'Contact', NULL,
                       (SELECT DISTINCT hr.NAME
                                   FROM hr_all_organization_units hr
                                  WHERE b.organization_id = hr.organization_id
                                    AND SYSDATE BETWEEN hr.date_from
                                                    AND NVL
                                                          (hr.date_to,
                                                           TO_DATE
                                                               ('31-DEC-4712',
                                                                'DD-MON-YYYY'
                                                               )
                                                          ))
                      ) organization_name,
                  DECODE
                      (b.employee_flag,
                       'Y', 'Current_Employee',
                       'N', nvl(d.user_person_type, 'EX-Employee'),
                        d.user_person_type
                      ) employee_status,
                   application_name, responsibility_name,
                   login_resp.last_logon_date, ur.start_date resp_start_date
              FROM fnd_user u,
                   fnd_user_resp_groups_direct ur,
                   fnd_responsibility_vl r,
                   fnd_application_vl a,
                   fnd_security_groups_vl s,
                   (SELECT ppf.full_name, ppf.person_id,
                           NVL (ppf.current_employee_flag, 'N') employee_flag,
                           paf.organization_id, user_person_type
                      FROM per_all_people_f ppf,
                           per_assignments_x paf,
                           per_person_types ppt
                     WHERE ppf.person_id = paf.person_id
                       AND paf.primary_flag = 'Y'
                       AND SYSDATE BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
                       AND SYSDATE BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
                       AND paf.assignment_type =
                              (SELECT DISTINCT a.assignment_type
                                          FROM (SELECT person_id,
                                                       assignment_type
                                                  FROM per_assignments_x) a,
                                               (SELECT   person_id,
                                                         COUNT
                                                            (assignment_type
                                                            ) c
                                                    FROM per_assignments_x
                                                GROUP BY person_id) b
                                         WHERE a.person_id = b.person_id
                                           AND a.person_id = ppf.person_id
                                           AND a.assignment_type =
                                                  DECODE (b.c,
                                                          1, a.assignment_type,
                                                          'E'
                                                         ))
                       AND ppf.current_npw_flag IS NULL
                       AND ppt.person_type_id = ppf.person_type_id) b,
                   (SELECT   fu.user_id, fu.user_name, flr.responsibility_id,
                             MAX (flr.start_time) last_logon_date
                        FROM fnd_user fu,
                             fnd_logins fl,
                             fnd_login_responsibilities flr
                       WHERE fu.user_id = fl.user_id(+) AND fl.login_id = flr.login_id(+)
                    GROUP BY fu.user_name, fu.user_id, flr.responsibility_id
                    ORDER BY 2) login_resp,
                    (SELECT DISTINCT user_person_type, pptuf.person_id
                                   FROM per_person_type_usages_f pptuf,
                                        per_person_types ppt
                                  WHERE pptuf.person_type_id =
                                                            ppt.person_type_id
                                    AND ppt.user_person_type NOT LIKE
                                                                   '%mployee%'
                                    AND ppt.user_person_type NOT IN
                                           ('Former Family Member',
                                            'Retiree',
                                            'Participant'
                                           )
                                    AND SYSDATE
                                           BETWEEN pptuf.effective_start_date
                                               AND pptuf.effective_end_date) d
             WHERE a.application_id = r.application_id
               AND u.user_id = ur.user_id
               AND r.application_id = ur.responsibility_application_id
               AND r.responsibility_id = ur.responsibility_id
               AND ur.start_date <= SYSDATE
               AND NVL (ur.end_date, SYSDATE + 1) > SYSDATE
               AND u.start_date <= SYSDATE
               AND NVL (u.end_date, SYSDATE + 1) > SYSDATE
               AND r.start_date <= SYSDATE
               AND NVL (r.end_date, SYSDATE + 1) > SYSDATE
               AND ur.security_group_id = s.security_group_id
               AND ur.user_id = login_resp.user_id(+)
               AND ur.responsibility_id = login_resp.responsibility_id(+)
               AND u.employee_id = b.person_id(+)
               AND u.employee_id = d.person_id(+)
       ORDER BY user_name, application_name, responsibility_name

No comments:

Post a Comment