Inactive Manager Assignment Query – Oracle Fusion

Introduction:

This blog has the SQL query that can be used to pull the data of employees whose manager assignment is inactive even if the manager is active.

Cause of the issue:

Business wants to know the list of employees whose manager assignment is inactive even if the manager is active.

How do we solve:
Extract the data using below SQL query.

–** Query **–

 SELECT PAPF.person_number     Employee_Number

        ,PPNF_E.full_name       Employee_Name

        ,PEA.Email_address      Employee_Email_Address

        ,PAAM.assignment_number Employee_Assignment_Number

        ,PAPF_S.person_number   Supervisor_Number

        ,PPNF_S.full_name       Supervisor_Name

        ,PAAM_S.assignment_number Supervisor_Assignment_Number

        ,HOU.name               Department

        ,HBU.name               BusinessUnit     

  FROM per_all_people_f             PAPF

        ,per_all_assignments_m        PAAM

        ,per_assignment_supervisors_f PASF

        ,per_person_names_f           PPNF_E

        ,per_person_names_f           PPNF_S

        ,per_all_people_f             PAPF_S

        ,per_email_addresses          PEA

        ,hr_organization_units        HOU

        ,hr_organization_units        HBU

        ,per_periods_of_service       PPOS

        ,per_all_assignments_m        PAAM_S

        ,per_periods_of_service       PPOS_S

 WHERE PAPF.person_id               = PAAM.person_id

   AND PAAM.primary_assignment_flag = ‘Y’

   AND PAAM.assignment_type         = ‘E’

   AND PAAM.effective_latest_change = ‘Y’

   AND PAAM.assignment_status_type  = ‘ACTIVE’

   AND PAPF.person_id               = PASF.person_id

   AND PAAM.assignment_id           = PASF.assignment_id

   AND PASF.manager_type            = ‘LINE_MANAGER’

   AND PASF.primary_flag            = ‘Y’

   AND PPNF_E.person_id             = PAPF.person_id

   AND PPNF_E.name_type             = ‘GLOBAL’

   AND PPNF_S.person_id             = PASF.manager_id

   AND PPNF_S.name_type             = ‘GLOBAL’

   AND PASF.manager_id              = PAPF_S.person_id

   AND papf.person_id               = PEA.person_id

   AND PEA.email_type               = ‘W1’

   AND PAAM.organization_id         = HOU.organization_id(+)

   AND PAAM.Business_unit_id        = HBU.organization_id(+)

   AND PAPF.person_id               = PPOS.person_id

   AND PAAM.period_of_service_id    = PPOS.period_of_service_id

   AND PPOS.primary_flag            = ‘Y’

   AND PPOS.actual_termination_date IS NULL

   –*Block1: To check if Manager is Active **–

   AND PASF.manager_id                = PAAM_S.person_id

   AND PAAM_S.primary_assignment_flag = ‘Y’

   AND PAAM_S.assignment_type         = ‘E’

   AND PAAM_S.effective_latest_change = ‘Y’

   AND PAAM_S.assignment_status_type  = ‘ACTIVE’

   AND PASF.manager_id                = PPOS_S.person_id

   AND PAAM_S.period_of_service_id    = PPOS_S.period_of_service_id

   AND PPOS_S.primary_flag            = ‘Y’

   AND PPOS_S.actual_termination_date IS NULL

   –*Block1:end **–

   AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN PAAM.effective_start_date AND PAAM.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN PASF.effective_start_date AND PASF.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN PPNF_E.effective_start_date AND PPNF_E.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN PPNF_S.effective_start_date AND PPNF_S.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(PEA.date_from, SYSDATE)) AND TRUNC(NVL(PEA.date_to, SYSDATE))

   AND TRUNC(SYSDATE) BETWEEN PAAM_S.effective_start_date AND PAAM_S.effective_end_date

   AND NOT EXISTS (SELECT PAAM1.assignment_id

                         FROM per_all_assignments_m PAAM1

                        WHERE PAAM1.assignment_id = PASF.manager_assignment_id

                          AND PAAM1.assignment_status_type = ‘ACTIVE’

                          AND PAAM1.primary_assignment_flag = ‘Y’

                                                 AND PAAM1.assignment_type         = ‘E’

                                                 AND PAAM1.effective_latest_change = ‘Y’

                          AND TRUNC(SYSDATE) BETWEEN PAAM1.effective_start_date AND PAAM1.effective_end_date)  

   AND EXISTS (SELECT 1

                 FROM per_users PU

                        WHERE PU.person_id = PAPF.person_id

                          AND NVL(PU.suspended, ‘Y’) <> ‘Y’)