Oracle® Insurance Rules Palette 10.1.2.0 E59346-01

Query Account Records

In the database use the ActivityGUID to locate the record written to AsAccountingDetail. 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.PolicyNumber='[PolicyNumber]')

AND AsTransaction.TransactionName='[TransactionName]'

AND AsActivity.EffectiveDate='[EffectiveDate]'

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

AND AsActivity.StatusCode = '01'

View the accounting details for all transactions associated with a policy or activity by using the following SQL statements.

Query to Use ActivityGUID to Locate Record Written to AsAccountingDetail

SELECT AsAccountingDetail.*

FROM AsAccountingDetail

JOIN AsActivity ON AsActivity.ActivityGUID = AsAccountingDetail.ActivityGUID

AND AsActivity.StatusCode = '01'

JOIN AsPolicy ON AsPolicy.PolicyGUID = AsActivity.PolicyGUID

AND AsPolicy.PolicyNumber = '[policy number]'

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