1 / 21

Automatic Status Progression and Update Procedures for Item Management**

This document outlines the SQL procedures and processes for automatic status progression of library items based on time elapsed in their current status. It provides a method to update various notice headers with predefined email notifications and details procedures for handling item status updates, including handling table dependencies. This is particularly useful for managing items in transit, on hold, or missing based on specific criteria. The update procedures utilize stored procedures to streamline item status management, enhancing the efficiency of library operations.

lani-wade
Télécharger la présentation

Automatic Status Progression and Update Procedures for Item Management**

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Line breaks at mid-word

  2. Desired – Line breaks after word

  3. Notice text set in Block table

  4. “BUG” – spaces stripped

  5. Use SQL to UPDATE notice headers in block table update block set email_notice_language = "REQUESTED ITEMS: The following [ remainder of formatted text]" where block = "hnmail"

  6. UPDATE exceptions to default notice language update block_notice_language set email_notice_language = "Listed below are [ remainder of formatted text]" where block = "bn"

  7. Must run SQL after block edit

  8. Header text improved

  9. Automatic Status Progression

  10. Created Stored Procedure /* Can be called to change item status based on length of time in previous status. */ create proc spl_progress_item_status (@current_status code_type, @new_status code_type, @duration smallint) as declare @todays_date smallint select @todays_date = datediff(dd, '1 jan 1970', getdate()) update item set item_status = @new_status, last_status_update_date = @todays_date where item_status = @current_status and last_status_update_date < @todays_date - @duration return

  11. spl_progress_item_status • Uses Parameters that set variables in sp create proc spl_progress_item_status (@current_status code_type, @new_status code_type, @duration smallint) • Call Stored Procedure with parameters: spl_progress_item_status "trace","m",30

  12. weekly_script.sql SELECT "Doing automatic status progressions ..." GO SELECT "Setting from trace to missing..." execute spl_progress_item_status "trace","m",30 GO SELECT "Setting from Trace from Hold Shelf to missing..." execute spl_progress_item_status "tracehd","m",30 GO SELECT "Setting from transit to trace..." execute spl_progress_item_status "tr","trace",60 GO

  13. Status Progressions Trace to MissingOlder than 30 days "trace"  "m“ Trace from Hold Shelf to MissingOlder than 30 days "tracehd"  "m" Transit to TraceOlder than 60 days "tr"  "trace" Held for Pickup to Trace Older than 30 days "ph"  "trace"

  14. Cannot use for some progressions • Works fine when only changes occur in the item table. • Needs to be expanded to handle changes affecting several tables, like Transit Hold  Trace Requires change to request and borrower tables.

  15. RFID in Action • CODI 2004 presentation • PowerPoint and videos • URL:http://extranet.spl.org/rfid/

More Related