Alternate views for pr_audit_history in Munis 2018.1 and later versions

Client wants detail payroll audit data by employee number.

• Munis 2018.1 and later versions

• SSRS reports

• Crystal reports

• SaaS, Tyler-hosted clients

• OnPrem, Self-hosted clients

• Payroll Audit

In Munis 2018.1 and later versions, payroll audit synchronization option is no longer available, as a result pr_audit_history may no longer be used to retrieve payroll audit data. 

A new view pr_audit_data is available, however it does not include columns a_employee_number, pa_new_record that are available in pr_audit_history.

The following SQL statement is an example to retrieve employee number from pr_audit_data:

^{SELECT CAST(substring(hh_record,7,11) as int) AS empno FROM pr_audit_data}

Please note that the pr_audit_data view typically contains many records and may take long time to run.

Another option is to use detail views from PayrollAuditReportingServices schema. Following is an example SQL Statement:

select distinct

[Type] as pa_action,

null as pa_addl_code, Null as pa_code,

[Date] as pa_change_date,

prem_emp as a_employee_number, case

when  [Type]='A' then 'RECORD ADDED'

when   [Type]='D' then 'RECORD DELETED'

when [Type]='T' then 'MOVE TO TERMINA'

else [Field]

end as pa_field_changed,

'prempmst' as pa_file,

null as pa_filler,

NewValue as pa_new_record ,

OldValue as pa_old_record ,

prem_proj as a_projection ,

[User] as pa_changed_by 

from [PayrollAuditReportingServices].[prempmstAuditDetail] 

UNION

select distinct [Type] as pa_action ,

null  as pa_addl_code,

prad_addnum   as pa_code,

[Date] as pa_change_date,  

prad_emp as a_employee_number,

case

when  [Type]='A' then 'RECORD ADDED'

when   [Field]='prad_addr1' then 'Address Line 1'

when   [Field]='prad_addr2' then 'Address Line 2'

when   [Field]='prad_city' then 'City'

when   [Field]='prad_state' then 'State'

when   [Field]='prad_zip' then 'Zip' else [Field]

end as pa_field_changed,  

'praddrss' as pa_file, null as pa_filler,

NewValue as pa_new_record,

OldValue as pa_old_record,

prad_proj as a_projection,

[User] as pa_changed_by

from[PayrollAuditReportingServices].[praddrssAuditDetail]