The NTS_NOTIFICATION_QUEUE table defines the available queues for servicing notification events. There can be 1 or more queues defined for the Personify system. These queues can further be segregated by organization/organization unit. When a notification service queue service starts up it first checks the NTS_NOTIFICATION_QUEUE table to ensure that the queue is an active valid queue for the organization/organization unit.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| ORG_ID | ORG | Yes | Organization ID
for this queue.
Selected from APP_ORGANIZATION |
| ORG_UNIT_ID | ORG | Yes | Organization Unit
for this queue.
Selected from APP_ORG_UNIT |
| SERVICE_QUEUE_NAME | VARCHAR
(24) |
Yes | Unique name for this service queue. The combination of Org ID/Org Unit ID and Service Queue Name form the primary key for this table. |
| SERVICE_QUEUE_DESCRIPTION | VARCHAR
(24) |
Yes | A description of this service queue. |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_HEADER table contains text that is used as a common header among email messages. At runtime the contents of the header, email body, and footer text columns from the respective tables combine to create a single email body template that is used to create the outgoing email notifications.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| HEADER_ID | BIGINT | Yes | Read-only: SQL server identity column. Unique ID for this header record. |
| ORG_ID | ORG | Yes | Organization ID for this queue. |
| ORG_UNIT_ID | ORG | Yes | Organization Unit for this queue. |
| HEADER_NAME | VARCHAR
(225) |
Yes | Name of this header. |
| HEADER_DESCRIPTION | VARCHAR
(MAX) |
Yes | Description of this header. |
| HEADER_TEXT | VARCHAR
(MAX) |
Yes | The text of the header for the email message. This text should contain HTML formatting. |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_FOOTER table contains text that is used as a common footer among email messages. At runtime the contents of the header, email body, and footer text columns from the respective tables combine to create a single email body template that is used to create the outgoing email notifications.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| FOOTER_ID | BIGINT | Yes | Read-only: SQL server identity column. Unique ID for this header record. |
| ORG_ID | ORG | Yes | Organization ID for this queue. |
| ORG_UNIT_ID | ORG | Yes | Organization Unit for this queue. |
| FOOTER_NAME | VARCHAR
(225) |
Yes | Name of this footer. |
| FOOTER_DESCRIPTION | VARCHAR
(MAX) |
Yes | Description of this footer. |
| FOOTER_TEXT | VARCHAR
(MAX) |
Yes | The text of the footer for the email message. This text should contain HTML formatting. |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_TEST_RECIPIENT table contains the data that is used to send test messages. When a Personify Administrator sets up a notification event he/she will want to view the output of the mail message before allowing the notification event to deliver the email message to the customer of the association. The values contained in this table are used for delivery of the test mail messages.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| TEST_RECIPIENT_ID | NUMERIC_ID | Yes | Unique identifier
for the data row.
Generated from the APP_NEXT_NUMBER table. |
| ORG_ID | ORG | Yes | Organization ID for this queue. |
| ORG_UNIT_ID | ORG | Yes | Organization Unit for this queue. |
| TEST_TO | dbo.EMAIL_ADDRESS | Yes | Email address to use as a TO recipient when sending test messages. |
| TEST_CC | dbo.EMAIL_ADDRESS | No | Email address to use as a CC recipient when sending test messages. |
| TEST_BCC | dbo.EMAIL_ADDRESS | No | Email address to use as a BCC recipient when sending test messages. |
| TEST_REPLY_TO | dbo.EMAIL_ADDRESS | Email address to use as the REPLY TO address when sending test messages. | |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION table is the primary table used within the notification system. All notification events are defined within this table. When a notification queue service is started it checks for all notification events that it is required to process by executing the stored procedure usp_ NTS_NOTIFICATION_INITIALIZE_QUEUE followed by a call to the stored procedure usp_ NTS_NOTIFICATION_GET_JOB_DETAILS for each notification event the queue is responsible for processing.
Keep
in mind, if you set an event as “Inactive,” the stored procedure continues
to execute. Therefore, if you decide to inactivate a notification event,
make sure to inactivate the stored procedure as well, as it may cause
unnecessary performance issues.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| NOTIFICATION_ID | BIGINT | Yes | Read-only. SQL server identity column. Unique ID that identifies a particular notification event. |
| ORG_ID | ORG | Yes | Organization ID for this queue. |
| ORG_UNIT_ID | ORG | Yes | Organization Unit for this queue. |
| SUBSYSTEM | SUBSYSTEM_CODE | Yes | Subsystem that this notification event is related to. Populated from the APP_SUBSYSTEM table. |
| NOTIFICATION_NAME | VARCHAR
(255) |
Yes | Name of the notification event. |
| NOTIFICATION_DESCRIPTION | VARCHAR
(MAX) |
Yes | Description of the notification event. |
| SP_NAME | VARCHAR
(255) |
Yes | Name of the stored
procedure that is executed for this notification event.
If the SP_NAME is not set the Active Flag cannot be equal to “Y”. |
| INTERVAL_DAYS | INT | Yes | The polling interval in days for this notification event. |
| INTERVAL_HOURS | INT | Yes | The polling interval in hours for this notification event. |
| INTERVAL_MINUTES | INT | Yes | The polling interval in minutes for this notification event. |
| INTERVAL_SECONDS | INT | Yes | The polling interval in seconds for this notification event. |
| POSTPROCESS_SPNAME | VARCHAR
(255) |
No | Name of the stored procedure that is executed when this notification event is complete. |
| CLEANUP_SPNAME | VARCHAR
(255) |
No | Name of the stored procedure that is executed for cleaning up chronicles and other information created by this notification event. |
| CLEANUP_INTERVAL_DAYS | INT | Yes | The polling interval in days for this notification cleanup event. |
| CLEANUP_INTERVAL_HOURS | INT | Yes | The polling interval in hours for this notification cleanup event. |
| CLEANUP_INTERVAL_MINUTES | INT | Yes | The polling interval in minutes for this notification cleanup event. |
| CLEANUP_INTERVAL_SECONDS | INT | Yes | The polling interval in seconds for this notification cleanup event. |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| TEST_MODE_FLAG | YES_NO | No | Indicates if this notification event is to be operated in Test Mode. When the event is operated in Test Mode, then email messages are only sent to the TO recipients, CC recipients, and BCC recipients listed in the Notification Server Service configuration file. |
| PROCESS_RUNNING_FLAG | YES_NO | Yes | This column is only used by the notification service itself. It does not need to be bound to any UI elements. |
| EMAIL_SUBJECT | VARCHAR
(255) |
Yes | The subject used for the outgoing message. |
| EMAIL_HEADER_ID | BIGINT | No | The ID of the header from NTS_NOTIFICATION_HEADER to include with this email message. |
| EMAIL_FOOTER_ID | BIGINT | No | The ID of the footer from NTS_NOTIFICATION_FOOTER to include with this email message. |
| EMAIL_TEMPLATE_BODY | VARCHAR
(MAX) |
Yes | The mail message body template. The body of the mail message. |
| EMAIL_FROM_ADDRESS | EMAIL_ADDRESS | Yes | The FROM address used for outgoing email messages. |
| EMAIL_CC_ADDRESS | EMAIL_ADDRESS | No | Optional CC address used for outgoing email messages. |
| EMAIL_BCC_ADDRESS | EMAIL_ADDRESS | No | Optional BCC address used for outgoing email messages. |
| EMAIL_REPLY_TO_ADDRESS | EMAIL_ADDRESS | No | Optional Reply To address used for outgoing email messages. |
| LASTRUN | DATETIME | No | Read-only. This column is used by the notification service to indicate the last time the notification event ran. |
| SERVICE_QUEUE | VARCHAR
(24) |
Yes | Service queue that services this notification event. |
| SEND_HISTORY_STORAGE_DAYS | INT | No | Used for cleanup stored procedures to determine the length of time notification history data should be kept. |
| SINGLE_ORG_UNIT_FLAG | YES_NO | No | Allows a Personify Administrator to identify those notification events where they have designed their queries to use the org ID/org unit ID parameters as part of the selection criterion for the data of the notification event. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
Several stored procedures exist for obtaining a list of notification event stored procedures that exist within the Personify database. They require that a specific naming convention be followed when creating new notification event stored procedures. The naming conventions for each type of notification event stored procedure are described below.
To populate the UI drop-down, execute the stored procedure named usp_NTS_NOTIFICATION_GET_NOTIFICATION_PROCS to return a list of stored procedures that can be used for notifications.
The naming convention to be followed for notification event stored procedures is as follows:
usp_NOTIFY_<any additional text to describe the procedure>
Execute the stored procedure named usp_NTS_NOTIFICATION_GET_POST_PROCESS_PROCS to return a list of stored procedures that can be used for notifications.
The naming convention to be followed for notification event post process stored procedures is as follows:
usp_NOTIFY_<Any Additional Text to describe the procedure>_POST_PROCESS
Execute the stored procedure named usp_NTS_NOTIFICATION_GET_CLEANUP_PROCS to return a list of stored procedures that can be used for notifications.
The naming convention to be followed for notification event cleanup stored procedures is as follows:
Usp_NOTIFY_<Any Additional Text to describe the procedure>_CLEANUP
The NTS_NOTIFICATION_DETAIL table stores the friendly names of data tables that will be returned by the stored procedure for the notification event. By default when a stored procedure is written to return data it names the result sets as Table(0), Table(1), Table(2), etc. This is not very intuitive for end users attempting to write HTML templates. This table will rename the tables in the result set to more user friendly names such as Customer, Addresses, Demographics, ORDER_SUMMARY, ORDER_DETAIL, etc.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| NOTIFICATION_ID | BIGINT | Yes | Parent ID from NTS_NOTIFICATION table. |
| NOTIFICATION | BIGINT | Yes | Read-only. SQL server identity column. Unique ID for this detail record. |
| TABLE_NAME | TABLE_VIEW_NAME | Yes | Name of the table as it is referenced in the HTML template for this notification. |
| TABLE_ORDER | INT | Yes | Table Sequence Order. Values must be incrementing starting at “1” for each NOTIFICATION_ID. |
| ACTIVE_FLAG | YES_NO | Yes | Indicates whether
this service queue is active or inactive.
Defaults to checked. |
| ATTACHMENT_TABLE_FLAG | BOOLEAN | Yes | Transient property.
Defaults to “N”.
Only one table per NOTIFICATION_ID can have this flag set to true. When this flag is true, the table name is set to the constant value “ATTACHMENTS”. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_PROCESS table is used to record information about each notification event that occurs. Every time a notification event finds data that needs to be processed it creates an entry in this table. When the notification event is complete, the service will update this table and set the end time and the total number of messages processed for the notification. This table is read-only to the Personify application.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| NOTIFICATION_ID | BIGINT | Yes | Read-only. Parent ID from NTS_NOTIFICATION table. Populated by the Notification Service |
| NOTIFICATION_PROCESS_ID | BIGINT | Yes | Read-only. SQL server identity column. Unique record ID. Populated by the Notification Service. |
| SESSIONID | UNIQUEIDENTIFIER | Yes | Read-only. GUID that identifies a unique notification event session. Populated by the Notification Service. |
| PROCESS_START_DATE | DATETIME | Yes | Read-only. Date/time the notification event began. Populated by the Notification Service. |
| PROCESS_END_DATE | DATETIME | Yes | Read-only. Date/time the notification event completed. Populated by the Notification Service. |
| TOTAL_PROCESSING_SECONDS | Calculated Column | Yes | Read-only. The difference in seconds between the Processing End Date and the Processing Start Date. Populated by the Notification Service. |
| MESSAGE_TOTAL | BIGINT | Read-only. Total number of messages sent by this notification event. Populated by the Notification Service. | |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_PROCESS_DETAIL table records information about each individual notification message being delivered through the notification service. When the notification service starts processing each message it records a record in this table. At various stages of the composition and delivery of the message it will add additional records to this table for the message. When the message completes (either successfully or unsuccessfully) a row is added to indicate the message completion status.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| NOTIFICATION_ID | BIGINT | Yes | Read-only. Parent ID from NTS_NOTIFICATION table. Populated by the Notification Service |
| NOTIFICATION_PROCESS_ID | BIGINT | Yes | Read-only. SQL server identity column. Unique record ID. Populated by the Notification Service. |
| NOTIFICATION_PROCESS_DETAIL_ID | BIGIT | Yes | Read-only. SQL sever identity column. Unique record ID. Populated by the Notification Server. |
| UNIQUEID | UNIQUEIDENTIFIER | Yes | Read-only. GUID that represents this single email message delivery within the notification event. Populated by the Notification Service. |
| SEND_STATUS_CODE | [CODE_CODE] | Yes | Read-only. Code that indicates the success/failure of the action performed on the mail message. |
| SEND_STATUS_MESSAGE | VARCHAR
(2048) |
Yes | Read-only. Status message that provides additional information about the action performed on the mail message during this notification event. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |
The NTS_NOTIFICATION_SENT_MESSAGE table contains copies of all messages sent by the notification system where a notification event has its save copies of sent messages flag set to true (Yes).
A relationship will exist to allow linking of customers to sent email notifications based on a customer's primary email address.
Column Name |
Data Type |
Required |
Description |
|---|---|---|---|
| MESSAGE_ID | BIGINT | Yes | Read-only. SQL sever identity column. Unique record ID. Populated by the Notification Service. |
| NOTIFICATION_ID | BIGINT | Yes | Read-only. Parent ID from NTS_NOTIFICATION table. Populated by the Notification Service |
| SESSIONID | UNIQUEIDENTIFIER | Yes | Read-only. GUID that represents the notification event session that initiated the composition and delivery of this message. Populated by the Notification Service. |
| UNIQUEID | UNIQUEIDENTIFIER | Yes | Read-only. GUID that represents this single email message delivery within the notification event. Populated by the Notification Service. |
| EMAIL_ADDRESS | EMAIL_ADDRESS | Yes | Read-only. The email address that this message was delivered to. |
| MESSAGE_SUBJECT | VARCHAR
(255) |
Yes | Read-only. The subject of the message delivered. |
| MESSAGE_TEXT | VARCHAR
(MAX) |
Yes | Read-only. The actual email message delivered. This message does not contain any attachments, only the message body. |
| ADDOPER | USER_ID | Standard Personify Field | |
| ADDDATE | DATETIME | Standard Personify Field | |
| MODOPER | USER_ID | Standard Personify Field | |
| MODDATE | DATE_TIME | Standard Personify Field | |
| CONCURRENCY_ID | CONCURRENCY_ID | Standard Personify Field |