Date Track History Change Field Summary query in Oracle HRMS

Hi,

I would like to give you a query to get the Date Track History data from database for an assignment because the record history is not available for this data/there is no direct table to get this data.

There is a view called PER_ASSIGNMENT_HISTORY_VIEW which gives related information but not the change field summary in one field.

We have to write our customized query according to our requirement. I have given the snapshot of the form data in the query output below.

Date Track History is a function in Oracle HRMS to view all the history of changes made (for example for an assignment). This form shows the From Date, To Date and Change Field Summary of the assignment.

Navigation: HRMS Manager Responsibility -- People -- Enter and Maintain -- Query for a particular employee -- Assignment -- Click on Date Track History button on the menu bar.

The form looks like below with From Date, To Date, Change Field Summary. And there is a button to see Full History as well.

Note: As there are only few changes for this employee like Grade, Category, Job, Position and Status - I have used only these changes for my query as an example. You can add more according to your requirement.

This is very simple query but query looks big because there are UNIONs used in the query to get all changes.

Nothing much to explain about this other than pasting my query here :)


Query to get Date track history of an assignment:

SELECT
   TO_CHAR(effective_start_date,'DD-MON-RRRR') "From Date",
   TO_CHAR(effective_end_date,'DD-MON-RRRR') "To Date",
   listagg (change_type, ',') 
WITHIN GROUP 
(ORDER BY change_type) "Change Field Summary"
FROM
(SELECT 'Grade' change_type,      
       a.effective_start_date
       ,a.effective_end_date      
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      AND NVL (a2.PEOPLE_GROUP_ID, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date 
    AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b.person_id = b1.person_id
                                 --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                               )
AND a.assignment_id =  :p_assignment_id
and NVL(a.grade_id,9.9) <> (SELECT NVL(a4.grade_id,9.9)
                            FROM per_all_assignments_f A4
                           WHERE A4.assignment_type = 'E'
                             AND A4.effective_end_date = (a.effective_start_date-1)  
                             AND a4.person_id = a.person_id
                             AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                   FROM per_all_assignments_f a5 
                                                  WHERE a5.person_id = a4.person_id
                                                               AND a5.effective_end_date = a.effective_start_date-1)
                                                   AND ROWNUM<2)                                      
UNION
SELECT 'Assignment Status' change_type,       
       a.effective_start_date   
       ,a.effective_end_date     
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      AND NVL (a2.PEOPLE_GROUP_ID, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date  
    AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b.person_id = b1.person_id
                                 --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                               )
AND a.assignment_id =  :p_assignment_id
  AND NVL(a.assignment_status_type_id,9.9) <> (SELECT NVL(a4.assignment_status_type_id,9.9)
                                                 FROM per_all_assignments_f A4
                                              WHERE A4.assignment_type = 'E'
                                            AND A4.effective_end_date = (a.effective_start_date-1)  
                                            AND a4.person_id = a.person_id
                                         AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                                FROM per_all_assignments_f a5 
                                                              WHERE a5.person_id = a4.person_id
                                                                             AND a5.effective_end_date = a.effective_start_date-1)
                                                          AND ROWNUM<2)                                      
UNION
SELECT 'Position' change_type,       
       a.effective_start_date  
       ,a.effective_end_date      
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      AND NVL (a2.PEOPLE_GROUP_ID, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date 
    AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b1.person_id = b.person_id
                                   --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                                )
AND a.assignment_id =  :p_assignment_id
and NVL(a.position_id,9.9) <> (SELECT NVL(a4.position_id,9.9)
                            FROM per_all_assignments_f A4
                           WHERE A4.assignment_type = 'E'
                             AND A4.effective_end_date = (a.effective_start_date-1)  
                             AND a4.person_id = a.person_id
                             AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                   FROM per_all_assignments_f a5 
                                                  WHERE a5.person_id = a4.person_id
                                                               AND a5.effective_end_date = a.effective_start_date-1)
                                                   AND ROWNUM<2)                                      
UNION
SELECT 'Hours' change_type,       
       a.effective_start_date 
       ,a.effective_end_date      
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      AND NVL (a2.people_group_id, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date  
    AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b1.person_id = b.person_id
                                 --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                               )
AND a.assignment_id =  :p_assignment_id
and NVL(a.normal_hours,9.9) <> (SELECT NVL(a4.normal_hours,9.9)
                            FROM per_all_assignments_f A4
                           WHERE A4.assignment_type = 'E'
                             AND A4.effective_end_date = (a.effective_start_date-1)  
                             AND a4.person_id = a.person_id
                             AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                   FROM per_all_assignments_f a5 
                                                  WHERE a5.person_id = a4.person_id
                                                               AND a5.effective_end_date = a.effective_start_date-1)
                                                   AND ROWNUM<2)                                      
UNION
SELECT 'Employment Category' change_type,       
       a.effective_start_date 
       ,a.effective_end_date      
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      AND NVL (a2.PEOPLE_GROUP_ID, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date 
  AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b1.person_id = b.person_id
                                 --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                                   )
AND a.assignment_id =  :p_assignment_id  
and NVL(a.employment_category,'XXX') <> (SELECT NVL(a4.employment_category,'XXX')
                            FROM per_all_assignments_f A4
                           WHERE A4.assignment_type = 'E'
                             AND A4.effective_end_date = (a.effective_start_date-1)  
                             AND a4.person_id = a.person_id
                             AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                   FROM per_all_assignments_f a5 
                                                  WHERE a5.person_id = a4.person_id
                                                               AND a5.effective_end_date = a.effective_start_date-1)
                                                   AND ROWNUM<2)
UNION
SELECT 'Job' change_type,       
       a.effective_start_date
       ,a.effective_end_date        
  FROM per_all_assignments_f a,
       per_all_people_f b
 WHERE a.assignment_type = 'E'
   AND b.original_date_of_hire <> a.effective_start_date 
   AND NOT EXISTS (SELECT NULL
                     FROM per_all_assignments_f A2
                    WHERE a2.assignment_id = a.assignment_id
                      AND a2.organization_id = a.organization_id
                      AND a2.assignment_number  =   a.assignment_number
                      AND a2.assignment_status_type_id = a.assignment_status_type_id
                      AND NVL (a2.position_id, 9.9) = NVL (a.position_id, 9.9)
                      AND NVL (a2.grade_id, 9.9) =  NVL (a.GRADE_ID, 9.9)
                      and nvl(a2.job_id,9.9) = nvl(a.job_id,9.9)
                      AND NVL (a2.PEOPLE_GROUP_ID, 9.9) = NVL (a.people_group_id, 9.9)
                      AND NVL (a2.supervisor_id, 9.9) = NVL (a.supervisor_id, 9.9)                       
                      AND NVL(a2.normal_hours, 9.9) = NVL(a.normal_hours, 9.9)
                      AND NVL(a2.employment_category,'XXX') = NVL(a.employment_category,'XXX')
                      AND a2.effective_start_date = (SELECT MAX (a3.effective_start_date)
                                                       FROM   per_all_assignments_f a3
                                                      WHERE   a3.person_id = a.person_id
                                                        AND a3.assignment_type = 'E'
                                                        AND a3.effective_start_date < a.effective_start_date))
    AND a.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
  AND a.person_id = b.person_id
  -- Include person type, current emp flag if required
  AND b.effective_start_date = (SELECT MAX(effective_start_date) 
                                  FROM per_all_people_f b1
                                 WHERE b1.person_id = b.person_id
                                   --AND b1.effective_start_date BETWEEN :p_effective_start_date AND :p_effective_end_date
                                )
AND a.assignment_id =  :p_assignment_id
and NVL(a.job_id,9.9) <> (SELECT NVL(a4.job_id,9.9)
                            FROM per_all_assignments_f A4
                           WHERE A4.assignment_type = 'E'
                             AND A4.effective_end_date = (a.effective_start_date-1)  
                             AND a4.person_id = a.person_id
                             AND a4.effective_start_date = (SELECT MAX(a5.effective_start_date) 
                                                   FROM per_all_assignments_f a5 
                                                  WHERE a5.person_id = a4.person_id
                                                               AND a5.effective_end_date = a.effective_start_date-1)
                                                   AND ROWNUM<2)   
) cust_tab                                                                                                     
GROUP BY effective_start_date,effective_end_date

-----------------------------------------------------------------------------------------------------------

Do suggest/comment if you have any concerns.

Thank You.


Cheers,
Rajesh