Database view containing the GL account creation date

Owned by Knowledge Assistant
Last updated: Jul 02, 2024 by Moriah Yorkey
1 min read
Description of Issue

Client is looking to report on the date a given GL account was created

Context

  • SSRS reports

  • SaaS, Tyler-hosted clients

  • OnPrem, Self-hosted clients

  • Account Inquiry

  • Enterprise ERP version 2019 and above

Cause

TRS view gl_master does not contain the created on date. 

Resolution

Date created needs to be retrieved from the TRS view sp_audit_history. The following sample SQL query shows how to use sp_audit_history and gl_master to retrieve this date for all general ledger accounts.

SELECT
a_fund_seg1, 
a_org, 
a_object,
sa_change_date as [Date Created]

FROM [sp_audit_history]
LEFT JOIN gl_master
on a_org = SUBSTRING(sa_key_number,1,(CHARINDEX(' ',sa_key_number + ' ')-1))
and a_object=RIGHT(RTRIM(sa_key_number),(CHARINDEX' ',REVERSE(RTRIM(sa_key_number))+' ')-1)

WHERE
sa_file='glmaster'
and sa_action ='A' 

Additional Information

Â