• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:April 5, 2021
  • Reading time:28 mins read
You are currently viewing SQL Query to get the modified fields from the Assignment table using Audit functionality
SQL Query to get the modified fields from the Assignment table using Audit functionality
- SQL Query to get the modified fields from the Assignment table using Audit functionality
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.

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.

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.