Total Visits: 40
In this article we will look into fetching all modified attributes for an employee from assignment table if the auditing functionality has been enabled.
If you haven’t enabled audit and want to know how to do it, please refer this step by step guide to enable audit.
Once you have enabled audit, all update/correct transactions will be tracked on the audit tables ending with underscore (_). Ex for PER_ALL_ASSIGNMENTS_M, the audit table will be PER_ALL_ASSIGNMENTS_M_
Even after enabling the audit if you want to query the changes, it would be a tough task as there is no easy way to identify which field has been modified, so we have to build custom queries to identify the fields that were updated.
Here is a sample query that can be used as a baseline for building such queries. This query fetches the changes to Department, Job, Grade, Location, Work At Home Flag, Assignment Name, Union Flag, Hourly Salaried Flag, Internal Building, Mailstop, Full Part Time, Reg Temp, Working Hours, Assignment Status, UNION, Establishment, Officer Code, Worker Category and Work Tax Address Id fields on assignment table.
Trending Content
SELECT *
FROM (
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.current_dept
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) current_val,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.prev_dept
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) prev_val,
'Department' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.organization_id current_dept,
LAG(organization_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_dept
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT job_code
FROM per_jobs_f pj
WHERE pj.job_id = at.current_job
AND CURRENT_DATE BETWEEN pj.effective_start_date
AND pj.effective_end_date
AND rownum = 1
) AS current_val,
(
SELECT DISTINCT job_code
FROM per_jobs_f pj
WHERE pj.job_id = at.prev_job
AND CURRENT_DATE BETWEEN pj.effective_start_date
AND pj.effective_end_date
AND rownum = 1
) prev_val,
'Job' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.job_id current_job,
LAG(job_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_job
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT grade_code
FROM per_grades_f pg
WHERE pg.grade_id = at.current_grade
AND CURRENT_DATE BETWEEN pg.effective_start_date
AND pg.effective_end_date
AND rownum = 1
) AS current_val,
(
SELECT DISTINCT grade_code
FROM per_grades_f pg
WHERE pg.grade_id = at.prev_grade
AND CURRENT_DATE BETWEEN pg.effective_start_date
AND pg.effective_end_date
AND rownum = 1
) prev_val,
'Grade' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.grade_id current_grade,
LAG(grade_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_grade
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT INTERNAL_LOCATION_CODE
FROM per_locations pl
WHERE pl.location_id = at.current_location
AND rownum = 1
) AS current_val,
(
SELECT DISTINCT INTERNAL_LOCATION_CODE
FROM per_locations pl
WHERE pl.location_id = at.prev_location
AND rownum = 1
) prev_val,
'Location' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.location_id current_location,
LAG(location_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_location
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_wah AS current_val,
at.prev_wah prev_val,
'Work At Home Flag' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.work_at_home current_wah,
LAG(work_at_home, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_wah
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_an AS current_val,
at.prev_an prev_val,
'Assignment Name' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.assignment_name current_an,
LAG(assignment_name, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_an
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_uf AS current_val,
at.prev_uf prev_val,
'Union Flag' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.LABOUR_UNION_MEMBER_FLAG current_uf,
LAG(LABOUR_UNION_MEMBER_FLAG, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_uf
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_hs AS current_val,
at.prev_hs prev_val,
'Hourly Salaried Flag' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.HOURLY_SALARIED_CODE current_hs,
LAG(HOURLY_SALARIED_CODE, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_hs
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_build AS current_val,
at.prev_build prev_val,
'Internal Building' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.INTERNAL_BUILDING current_build,
LAG(INTERNAL_BUILDING, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_build
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_ms AS current_val,
at.prev_ms prev_val,
'Mailstop' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.INTERNAL_MAILSTOP current_ms,
LAG(INTERNAL_MAILSTOP, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_ms
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_fp AS current_val,
at.prev_fp prev_val,
'Full Part Time' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.FULL_PART_TIME current_fp,
LAG(FULL_PART_TIME, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_fp
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
at.current_rt AS current_val,
at.prev_rt prev_val,
'Reg Temp' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.PERMANENT_TEMPORARY_FLAG current_rt,
LAG(PERMANENT_TEMPORARY_FLAG, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_rt
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
to_char(at.current_hours) AS current_val,
to_char(at.prev_hours) prev_val,
'Working Hours' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.NORMAL_HOURS current_hours,
LAG(NORMAL_HOURS, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_hours
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT user_status
FROM per_assignment_status_types_tl pastt
WHERE pastt.assignment_status_type_id = at.current_as
AND rownum = 1
) AS current_val,
(
SELECT DISTINCT user_status
FROM per_assignment_status_types_tl pastt
WHERE pastt.assignment_status_type_id = at.prev_as
AND rownum = 1
) prev_val,
'Assignment Status' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.assignment_status_type_id current_as,
LAG(assignment_status_type_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_as
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.current_union
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) current_val,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.prev_union
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) prev_val,
'UNION' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.union_id current_union,
LAG(union_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_union
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.current_re
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) current_val,
(
SELECT DISTINCT name
FROM HR_Organization_Units_F_TL HOUFT
WHERE houft.organization_id = at.prev_re
AND rownum = 1
AND CURRENT_DATE BETWEEN houft.effective_start_date
AND houft.effective_end_date
) prev_val,
'Establishment' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.establishment_id current_re,
LAG(establishment_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_re
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT segment1
FROM per_people_groups ppg
WHERE ppg.people_group_id = at.current_pg
AND rownum = 1
) current_val,
(
SELECT DISTINCT segment1
FROM per_people_groups ppg
WHERE ppg.people_group_id = at.prev_pg
AND rownum = 1
) prev_val,
'Officer Code' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.people_group_id current_pg,
LAG(people_group_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_pg
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
current_ec current_val,
prev_ec prev_val,
'Worker Category' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.employment_category current_ec,
LAG(employment_category, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_ec
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
UNION
SELECT *
FROM (
SELECT DISTINCT papf.person_number,
at.assignment_number,
at.audit_action,
at.action_code,
at.reason_code,
at.effective_start_date,
at.effective_end_date,
at.created_by,
at.creation_date,
at.last_updated_by,
at.last_update_date,
(
SELECT DISTINCT CASE
WHEN paf_work_ta.address_line_1 IS NULL
THEN ' '
ELSE paf_work_ta.address_line_1 || ', ' || paf_work_ta.town_or_city || ', ' || paf_work_ta.region_2 || ' ' || paf_work_ta.postal_code
END AS work_tax_address
FROM per_addresses_f paf_work_ta
WHERE current_wta = paf_work_ta.address_id
AND CURRENT_DATE BETWEEN paf_work_ta.effective_start_date
AND paf_work_ta.effective_end_date
AND rownum = 1
) current_val,
(
SELECT DISTINCT CASE
WHEN paf_work_ta.address_line_1 IS NULL
THEN ' '
ELSE paf_work_ta.address_line_1 || ', ' || paf_work_ta.town_or_city || ', ' || paf_work_ta.region_2 || ' ' || paf_work_ta.postal_code
END AS work_tax_address
FROM per_addresses_f paf_work_ta
WHERE prev_wta = paf_work_ta.address_id
AND CURRENT_DATE BETWEEN paf_work_ta.effective_start_date
AND paf_work_ta.effective_end_date
AND rownum = 1
) prev_val,
'Work Tax Address Id' AS changed_val,
audit_change_bit_map_
FROM (
SELECT 'Assignment' AS OBJECT,
paam.assignment_number,
paam.action_code,
paam.reason_code,
paam.assignment_id,
paam.effective_start_date,
paam.effective_end_date,
paam.person_id,
paam.created_by,
paam.creation_date,
paam.last_updated_by,
paam.last_update_date,
paam.audit_change_bit_map_,
CASE
WHEN audit_action_type_ = 'UPDATE'
THEN 'Update'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date = last_update_date
THEN 'Creation'
WHEN audit_action_type_ = 'HISTORY'
AND creation_date <> last_update_date
THEN 'History'
WHEN audit_action_type_ = 'INSERT'
THEN 'New'
WHEN audit_action_type_ = 'DELETE'
THEN 'Deleted'
END Audit_Action,
paam.tax_address_id current_wta,
LAG(tax_address_id, 1, 0) OVER (
PARTITION BY assignment_id ORDER BY last_update_date
) Prev_wta
FROM fusion.per_all_assignments_m_ paam
WHERE paam.assignment_type = 'E'
ORDER BY last_update_date DESC
) at
INNER JOIN per_all_people_f papf ON papf.person_id = at.person_id
AND CURRENT_DATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
)
WHERE current_val <> prev_val
OR (
audit_action = 'Deleted'
AND prev_val IS NOT NULL
)
)
WHERE (
person_number IN (:pn)
OR (:pn) IS NULL
)
AND last_update_date BETWEEN (:date1)
AND (:date2)
AND audit_action NOT IN ('History')
ORDER BY person_number,
last_update_date,
effective_start_date
Hope this helps when you have to build audit queries for tables.
Trending Content
