Query Account Records

In the database you can use the activityGUID to locate the record written to AsAccountingDetail.  You can locate the activityGUID by running the query listed below.

Query To Locate the ActivityGUID

SELECT AsActivity.ActivityGUID FROM AsActivity

JOIN AsTransaction ON

AsTransaction.TransactionGUID=AsActivity.TransactionGUID

JOIN AsPolicy ON AsPolicy.PolicyGUID=AsActivity.PolicyGUID

WHERE AsPolicy.PolicyGUID=(SELECT PolicyGUID FROM AsPolicy WHERE PolicyNumber='[PolicyNumber]')

AND AsTransaction.TransactionName='[TransactionName]'

AND AsActivity.EffectiveDate='[EffectiveDate]'

AND AsActivity.TypeCode IN ('01','04')

AND AsActivity.StatusCode = '01'

You can see the accounting details for all transactions and suspense associated with a policy or activity by using the following SQL statements.

Query to Use ActivityGUID to Locate Record Written to AsAccountingDetail

SELECT *

FROM AsAccountingDetail

JOIN AsActivity ON AsActivity.ActivityGUID = AsAccountingDetail.ActivityGUID

JOIN AsPolicy ON AsPolicy.PolicyGUID = AsActivity.PolicyGUID

JOIN AsSuspense ON AsSuspense.PolicyNumber = AsPolicy.PolicyNumber

WHERE AsActivity.PolicyGUID ='6D80B5A3-D130-BF87-09DF-98A722D1B571'

Query to View all CoA Information

SELECT * FROM AsChartOfAccounts

JOIN AsChartOfAccountsEntity ON AsChartOfAccounts.ChartOfAccountsGUID = AsChartOfAccountsEntity.ChartOfAccountsGUID

JOIN AsChartOfAccountsEntry ON AsChartOfAccountsEntity.ChartOfAccountsEntityGUID = AsChartOfAccountsEntry.ChartOfAccountsEntityGUID

LEFT JOIN AsChartOfAccountsMoneyType ON AsChartOfAccountsEntry.ChartOfAccountsEntryGUID = AsChartOfAccountsMoneyType.ChartOfAccountsEntryGUID

LEFT JOIN AsChartOfAccountsResult ON AsChartOfAccountsEntry.ChartOfAccountsEntryGUID = AsChartOfAccountsResult.ChartOfAccountsEntryGUID

LEFT JOIN AsChartOfAccountsCriteria ON AsChartOfAccountsEntry.ChartOfAccountsEntryGUID = AsChartOfAccountsCriteria.ChartOfAccountsEntryGUID