I am working in Oracle Fusion HCM and would like to create a query which pulls an employee's base data such as name, location, etc. We also want to include the managers. Our manager structure is as such so that there's 1 line manager and 1 to n (realistically not more than 3) matrix managers, named 'REVIEWER'.

I have a working code that fetches the data, but it gives issues when there's not exactly 2 managers. When there's 1, it shows the same name twice and if there's 3, there is one that is not shown.

Can anyone help me out on how to fetch the correct manager names without using the MIN/MAX aggregrates? My query is already fetching the correct data, but my pivot clause is not working correctly.

Select DISTINCT *
from
(
SELECT DISTINCT
    emplName.DISPLAY_NAME Worker_Name,
    INITCAP(loc.LOCATION_NAME) Location_Name,
    gra.NAME Grade_Name,
    hou.NAME Department_Name,
    ass.MANAGER_TYPE Manager_Type,
    mgr.DISPLAY_NAME Manager_Name,
    REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date,
    aa.ASSIGNMENT_NUMBER

FROM
    PER_ALL_ASSIGNMENTS_M aa,
    PER_ASSIGNMENT_SUPERVISORS_F ass,
    PER_PERSON_NAMES_F emplName,
    PER_ALL_PEOPLE_F empl,
    PER_PERSON_NAMES_F mgr,
    HR_ORGANIZATION_UNITS hou,
    HR_LOCATIONS_ALL_F_VL loc,
    PER_GRADES_F_TL gra,
    PER_CONTRACTS_F ctr

WHERE
    aa.ASSIGNMENT_ID ( ) = ass.ASSIGNMENT_ID 
AND emplName.PERSON_ID = ass.PERSON_ID 
AND ass.MANAGER_ID = mgr.PERSON_ID
AND empl.PERSON_ID = ass.PERSON_ID
AND hou.ORGANIZATION_ID = aa.ORGANIZATION_ID
AND loc.LOCATION_ID = aa.LOCATION_ID
AND gra.GRADE_ID = aa.GRADE_ID
AND ctr.CONTRACT_ID = aa.CONTRACT_ID
AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND to_char(ass.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND to_char(aa.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND to_char(ctr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND gra.SOURCE_LANG = 'US'

AND gra.NAME in (:p_grade)
AND hou.NAME in (:p_department)
AND INITCAP(loc.LOCATION_NAME) in (:p_location)
AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate)
    OR (:p_contractenddate) is null)


) S
Pivot
(
MAX(Manager_Name) Manager1,
MIN(Manager_Name) Manager2
for manager_type in
 ('LINE_MANAGER' as Line_Manager,
  'REVIEWER' as Reviewer
 ))
Piv

The data regarding managers is recorded in PER_ASSIGNMENT_SUPERVISORS_F ass as follows:

Edit: Table formatting was broken

CodePudding user response：

Use:

Select *
from   (
  SELECT ass.assignment_id,
         ass.person_id,
         ass.MANAGER_TYPE Manager_Type,
         mgr.DISPLAY_NAME Manager_Name,
         ROW_NUMBER() OVER (
           PARTITION BY ass.assignment_id, ass.person_id, ass.manager_type
           ORDER BY mgr.display_name
         ) AS rn
  FROM   PER_ASSIGNMENT_SUPERVISORS_F ass
         INNER JOIN PER_PERSON_NAMES_F mgr
         ON (ass.MANAGER_ID = mgr.PERSON_ID)
  WHERE  ass.EFFECTIVE_END_DATE = DATE '4712-12-31'
)
PIVOT (
  MAX(Manager_Name)
  for (manager_type, rn) in (
    ('LINE_MANAGER', 1) as Line_Manager,
    ('REVIEWER', 1) as Reviewer1,
    ('REVIEWER', 2) as Reviewer2,
    ('REVIEWER', 3) as Reviewer3
  )
)

Then join the rest of the tables to that pivoted query (rather than trying to join first and then pivot).

Which, for the (minimal) sample data:

CREATE TABLE PER_ASSIGNMENT_SUPERVISORS_F (assignment_id, person_id, manager_id, manager_type, effective_end_date) AS
SELECT 1, 1, 2, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1, 1, 3, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1, 1, 4, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1, 1, 5, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 2, 2, 3, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 2, 2, 4, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 2, 2, 5, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 3, 3, 4, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 3, 3, 5, 'REVIEWER',     DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 4, 4, 5, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL;

CREATE TABLE PER_PERSON_NAMES_F (person_id, display_name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Beryl' FROM DUAL UNION ALL
SELECT 3, 'Carol' FROM DUAL UNION ALL
SELECT 4, 'Debra' FROM DUAL UNION ALL
SELECT 5, 'Emily' FROM DUAL;

Outputs:

fiddle

CodePudding user response：

Rewrote the query based on MT0s answer. For anyone interested in the end-result:

Select *
from
(
SELECT
    emplName.DISPLAY_NAME Worker_Name,
    INITCAP(loc.LOCATION_NAME) Location_Name,
    gra.NAME Grade_Name,
    hou.NAME Department_Name,
    ass.MANAGER_TYPE Manager_Type,
    mgr.DISPLAY_NAME Manager_Name,
     ROW_NUMBER() OVER (
     PARTITION BY aa.ASSIGNMENT_NUMBER, ass.assignment_id, ass.person_id, gra.NAME, hou.NAME, ass.manager_type
     ORDER BY mgr.display_name
    ) AS rn,
    REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date,
    aa.ASSIGNMENT_NUMBER

FROM
    PER_ALL_ASSIGNMENTS_F aa
    LEFT JOIN PER_ASSIGNMENT_SUPERVISORS_F ass
    ON (aa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
        AND to_char(ass.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712')
    
    LEFT JOIN PER_PERSON_NAMES_F_V emplName
    ON (ass.PERSON_ID = emplName.PERSON_ID 
        AND to_char(emplName.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' 
        AND emplName.NAME_TYPE = 'GLOBAL')
    
    LEFT JOIN PER_ALL_PEOPLE_F empl
    ON (empl.PERSON_ID = ass.PERSON_ID 
        AND to_char(empl.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712')
    
    LEFT JOIN PER_PERSON_NAMES_F mgr
    ON (mgr.PERSON_ID = ass.MANAGER_ID 
        AND to_char(mgr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' 
        AND mgr.NAME_TYPE = 'GLOBAL')
    
    LEFT JOIN HR_ORGANIZATION_UNITS hou
    ON (hou.ORGANIZATION_ID = aa.ORGANIZATION_ID 
        AND to_char(hou.DATE_TO, 'DD/MM/YYYY') = '31/12/4712')
    
    LEFT JOIN HR_LOCATIONS_ALL_F_VL loc
    ON (loc.LOCATION_ID = aa.LOCATION_ID 
        AND to_char(loc.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712')
    
    LEFT JOIN PER_GRADES_F_TL gra
    ON (gra.GRADE_ID = aa.GRADE_ID 
        AND gra.LANGUAGE = 'US' 
        AND to_char(gra.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712')
    
    LEFT JOIN PER_CONTRACTS_F ctr
    ON (ctr.CONTRACT_ID = aa.CONTRACT_ID
        AND to_char(ctr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712')

WHERE 1=1
 AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
 AND to_char(aa.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'

-- PARAMETERS
 AND gra.NAME in (:p_grade)
 AND hou.NAME in (:p_department)
 AND INITCAP(loc.LOCATION_NAME) in (:p_location)
 AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate)
   OR (:p_contractenddate) is null)
) S
Pivot
(
MAX(Manager_Name)
for (manager_type, rn) in (
 ('LINE_MANAGER', 1) as Line_Manager,
 ('REVIEWER', 1) as Reviewer1,
 ('REVIEWER', 2) as Reviewer2,
 ('REVIEWER', 3) as Reviewer3
 ))
Piv