Database view containing the GL account creation date

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

• SSRS reports

• SaaS, Tyler-hosted clients

• OnPrem, Self-hosted clients

• Account Inquiry

• Enterprise ERP version 2019 and above

TRS view gl_master does not contain the created on date. 

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'