Database view containing pending notifications in service notifications

Need table or view that contains pending notification records as seen in Service Notification program.

• SSRS Reports

• Tyler Reporting Services Views

• Enterprise ERP version 2019.1

• Derived

• Service Notification

• Pending Notifications

View database columns in Service notification shows all fields are derived.

Pending notification records are available in two tables:

1. EnitityNotifications.
2. EntityNotificationBacklog

All records from EntityNotifications will have Status of 'Pending Initial Attempt' and all records from EntityNotificationBacklog will have Status of 'Failed Previously'

The Action is already encoded in the Entity Payload string. The following example query fetches all records as one result set.

SELECT EntityType,
CAST(EntityNotificationBacklog.EntityPayload AS VARCHAR(800)) as EntityPayload,
SubscriberCode,
'Failed Previously' as EntityStatus
FROM dbo.EntityNotificationBacklog

UNION ALL

SELECT EntityType,
CAST(EntityNotifications.EntityPayload AS VARCHAR(800)) as EntityPayload,
' ' as SubscriberCode,
'Pending Initial Attempt' as EntityStatus
FROM dbo.EntityNotifications

Since the query uses table, user(s) should have table access granted through db_datareader role to fetch data.