Skipping the Copyright queue 2.0: Rapid and Shared Server
Please make sure to thoroughly test all new Routing Rules. The following are meant to serve as examples and may not take into account local customization or policies.
My favorite, but most complicated, routing rule is one that I've deemed "my precious." This rule identifies the inverse list of PhotoJournalTitles from the default copyright query and moves all copy requests with a PhotoJournalYear older than five years OR requests that have a PhotoJournalYear within the the last five years, but don't have more than five active requests for the last five years of publications with the same PhotoJournalTitle, within the current calendar year.
I was recently charged with updating the rule to work for shared server sites and for sites wanting to move fair-use requests directly to the Awaiting RAPID Request Sending queue.
This requires the creation of two rules; one will move fair use requests with unique identifiers to the Awaiting RAPID Request Sending queue and the other will move fair use requests without unique identifiers to the Awaiting Request Processing queue. I'll provide plain-text versions of the match strings further down, but here's a capture of the query used to test out each piece, with explanatory comments.
You may notice a reference to REPRINTS as a LenderString. This is to avoid counting transactions used to purchase copies through Reprints Desk. You can remove, alter, or add similar references for the CCC's Get It Now program or any other LenderAddress records.
Here's the visual breakdown.
Awaiting RAPID Request Sending rule:
Awaiting Request Processing rule:
In both of these examples, my NVTGC is DEV. If you're a shared server, you'll change DEV to your own symbol. If you're NOT a shared server, you can use a version that removes all references to the UsersALL table.
If you choose to try out these rules, please be cautious when copying and pasting. Many browsers will change single quotation marks from "straight" marks to "curly" marks. To be certain, we recommend copying any matchstring and pasting it into Notepad (or another text editor that will strip formatting) and then re-copying it from that interface, to make sure no hidden formatting has affected the text.
Also, remember that routing rules work in sequential order. These rules should be numbered to make them effective before most other Borrowing routing rules.
SHARED SERVER VERSIONS:
Rule No: (custom)
Rule Active: Yes
ProcessType: Borrowing
TransactionStatus: Awaiting Copyright Clearance
MatchString:
ISNULL(t.LendingString,'')='' AND (ISNULL(t.ESPNumber,'')!='' OR ISNULL(t.ISSN,'')!='') AND ISNULL(t.PhotoJournalYear,'')!='' AND u.NVTGC = 'DEV' AND t.RequestType = 'Article' AND (t.PhotoJournalYear < CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 4)) OR (t.PhotoJournalTitle NOT IN (SELECT DISTINCT ti.PhotoJournalTitle FROM Transactions ti INNER JOIN UsersALL ui on ti.username=ui.username WHERE ui.NVTGC = 'DEV' AND ti.TransactionStatus != 'Cancelled by ILL Staff' AND ti.TransactionStatus != 'Cancelled by Customer' AND ti.TransactionStatus != 'Awaiting Copyright Clearance' AND ISNULL(ti.CopyrightAlreadyPaid, 'No') != 'Yes' AND ISNULL(ti.LendingLibrary,'') != 'REPRINTS' AND DATEPART(year, ti.CreationDate) = DATEPART(year, GETDATE()) AND ti.PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5)) AND ti.ProcessType = 'Borrowing' AND ti.RequestType = 'Article' GROUP BY ti.PhotoJournalTitle HAVING COUNT(ti.PhotoJournalTitle) > 4)))
NewProcessType: Borrowing
NewTransactionStatus: Awaiting RAPID Request Sending
Rule Description: moves fair use copy requests with an ESPNumber or ISSN, without a LendingString, to Awaiting RAPID Request Sending
-------------------------------------------------------------
Rule No: (custom)
Rule Active: Yes
ProcessType: Borrowing
TransactionStatus: Awaiting Copyright Clearance
MatchString:
ISNULL(t.LendingString,'')='' AND ISNULL(t.PhotoJournalYear,'')!='' AND u.NVTGC = 'DEV' AND ISNULL(t.ESPNumber,'')='' AND ISNULL(t.ISSN,'')='' AND t.RequestType = 'Article' AND (t.PhotoJournalYear < CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 4)) OR (t.PhotoJournalTitle NOT IN (SELECT DISTINCT ti.PhotoJournalTitle FROM Transactions ti INNER JOIN UsersALL ui on ti.username=ui.username WHERE ui.nvtgc = 'DEV' AND ti.TransactionStatus != 'Cancelled by ILL Staff' AND ti.TransactionStatus != 'Cancelled by Customer' AND ti.TransactionStatus != 'Awaiting Copyright Clearance' AND ISNULL(ti.CopyrightAlreadyPaid, 'No') != 'Yes' AND ISNULL(ti.LendingLibrary,'') != 'REPRINTS' AND DATEPART(year, ti.CreationDate) = DATEPART(year, GETDATE()) AND ti.PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5)) AND ti.ProcessType = 'Borrowing' AND ti.RequestType = 'Article' GROUP BY ti.PhotoJournalTitle HAVING COUNT(ti.PhotoJournalTitle) > 4)))
NewProcessType: Borrowing
NewTransactionStatus: Awaiting Request Processing
Rule Description: moves fair use copy requests without a ESPNumber or ISSN to Awaiting Request Processing
SINGLE SERVER VERSIONS:
Rule No: (custom)
Rule Active: Yes
ProcessType: Borrowing
TransactionStatus: Awaiting Copyright Clearance
MatchString:
ISNULL(t.LendingString,'')='' AND (ISNULL(t.ESPNumber,'')!='' OR ISNULL(t.ISSN,'')!='') AND ISNULL(t.PhotoJournalYear,'')!='' AND t.RequestType = 'Article' AND (t.PhotoJournalYear < CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 4)) OR (t.PhotoJournalTitle NOT IN (SELECT DISTINCT PhotoJournalTitle FROM Transactions WHERE TransactionStatus != 'Cancelled by ILL Staff' AND TransactionStatus != 'Cancelled by Customer' AND TransactionStatus != 'Awaiting Copyright Clearance' AND ISNULL(CopyrightAlreadyPaid, 'No') != 'Yes' AND ISNULL(LendingLibrary,'') != 'REPRINTS' AND DATEPART(year, CreationDate) = DATEPART(year, GETDATE()) AND PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5)) AND ProcessType = 'Borrowing' AND RequestType = 'Article' GROUP BY PhotoJournalTitle HAVING COUNT(PhotoJournalTitle) > 4)))
NewProcessType: Borrowing
NewTransactionStatus: Awaiting RAPID Request Sending
Rule Description: moves fair use copy requests with an ESPNumber or ISSN, without a LendingString, to Awaiting RAPID Request Sending
-------------------------------------------------------------
Rule No: (custom)
Rule Active: Yes
ProcessType: Borrowing
TransactionStatus: Awaiting Copyright Clearance
MatchString:
ISNULL(t.LendingString,'')='' AND ISNULL(t.PhotoJournalYear,'')!='' AND ISNULL(t.ESPNumber,'')='' AND ISNULL(t.ISSN,'')='' AND t.RequestType = 'Article' AND (t.PhotoJournalYear < CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 4)) OR (t.PhotoJournalTitle NOT IN (SELECT DISTINCT PhotoJournalTitle FROM Transactions WHERE TransactionStatus != 'Cancelled by ILL Staff' AND TransactionStatus != 'Cancelled by Customer' AND TransactionStatus != 'Awaiting Copyright Clearance' AND ISNULL(CopyrightAlreadyPaid, 'No') != 'Yes' AND ISNULL(LendingLibrary,'') != 'REPRINTS' AND DATEPART(year, CreationDate) = DATEPART(year, GETDATE()) AND PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5)) AND ProcessType = 'Borrowing' AND RequestType = 'Article' GROUP BY PhotoJournalTitle HAVING COUNT(PhotoJournalTitle) > 4)))
NewProcessType: Borrowing
NewTransactionStatus: Awaiting Request Processing
Rule Description: moves fair use copy requests without a ESPNumber or ISSN to Awaiting Request Processing
Comments
Please sign in to leave a comment.