Where Can I Find Job Notification Attributes in the Database?

Document ID : KB000121587
Last Modified Date : 27/11/2018
Show Technical Document Details
Question:
In the WAAE database, what table(s) contain all of the information pertaining to a job's notification attributes if they are set?
Answer:
The various notification attributes for a job are spread out over three tables in the database. The job name and the "send_notification" attribute value are in ujo_job. All of the notification* attributes are in ujo_uninotify. In that table, the "notification_emailaddress" is set to an ID that maps over to the ujo_strings table where the actual email address is stored.
Here is an example of a query that produces a basic list of jobs that have notification enabled along with their respective "notification_msg" and "notification_emailaddress" attributes...

select a.job_name,c.value,b.notification_msg from ujo_job a,ujo_uninotify b,ujo_strings c where a.joid=b.joid and a.job_ver=b.job_ver and a.is_currver=1 and a.has_notification=1 and b.notification_emailaddress=c.str_oid;

It is possible for a job to have multiple rows in the ujo_job table due to job versioning. When you edit a job, a new row is created for it with the updated attributes and it is
assigned a different job_ver value. The original stays in the table for 7 days by default before it is archived. Therefore, your query has to account for that so that it only shows the most current version of the job. That is the purpose of the "a.job_ver=b.job_ver" and "a.is_currver=1" parts of the where clause.