Overdue Notice Delivery Process

The overdue notice delivery process is described in the steps given here and in the graphic that follows. These steps are listed in the order in which they are automatically carried out:

  1. A task scheduled through the SQL Server Agent executes the fm_admin_overdue_notices stored procedure on a regular basis. The fm_admin_overdue_notices stored procedure runs a query on the database's item table. This query compares the date due for each existing checkout with the current date. The fm_admin_overdue_notices stored procedure uses information from the due_date and last_overdue_notice columns to build a list of overdue items and the borrowers who have them. The fm_admin_overdue_notices stored procedure launches a second stored procedure, called fm_admin_send_notice.
  2. The fm_admin_send_notice stored procedure (not shown in this graphic) generates the message to be sent. Some of the text of the message is preset and some of it is filled in as it is generated, to identify the overdue item. The fm_admin_send_notice stored procedure then launches the extended stored procedure xp_sendmail, passing all the required message parameters.
  3. The xp_sendmail extended stored procedure now sends the actual precomposed messages, in a loop, to the specified recipients. To do this, it logs on with a MAPI account to create a MAPI session. Then it uses Microsoft Exchange Server to send messages to borrowers with overdue materials. For the code of xp_sendmail, see Stored Procedures for Overdue Notice Delivery.