-
Notifications
You must be signed in to change notification settings - Fork 5
/
ClosedBranch_Restore.sql
162 lines (130 loc) · 5.33 KB
/
ClosedBranch_Restore.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
USE [Polaris]
GO
/****** Object: StoredProcedure [Polaris].[SILS_ClosedBranch_Restore] Script Date: 18/03/2021 2:13:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jason Tenter
-- Create date: 2020-08-24
-- Description: This procedure is for re-opening libraries that were managed by the branch closures process.
-- It reactivates holds, and restores branch hours so that picklists will run properly.
-- It was created during the Covid19 closures, to prevent physical materials being shipped to branches without staff.
-- =============================================
ALTER PROCEDURE [Polaris].[SILS_ClosedBranch_Restore]
AS
BEGIN
SET NOCOUNT ON;
-- List of Branches to include in the update, based on the Polaris.SILS_ClosedBranch_List table
-- That table also includes exception branches, which this process excludes
CREATE TABLE #LibList (OrganizationID int, ParentOrganizationID int, OrganizationCodeID int)
INSERT INTO #LibList
SELECT o.OrganizationID, o.ParentOrganizationID, o.OrganizationCodeID
FROM Polaris.Organizations o (NOLOCK)
LEFT JOIN Polaris.SILS_ClosedBranch_List cb
on o.OrganizationID = cb.OrganizationID and cb.OrganizationCodeID = 3
and cb.Exception != 1 AND cb.Restored = 1
LEFT JOIN Polaris.SILS_ClosedBranch_List cbe -- Exceptions
on o.OrganizationID = cbe.OrganizationID and cbe.OrganizationCodeID = 3
and cbe.Exception = 1
WHERE o.OrganizationCodeID = 3
AND cbe.OrganizationID is NULL
AND (cb.OrganizationID is not null
OR o.ParentOrganizationID IN
(SELECT
cb.OrganizationID
FROM Polaris.SILS_ClosedBranch_List cb
WHERE cb.OrganizationCodeID = 2
AND cb.Restored = 1
))
CREATE TABLE #LibListHolds (OrganizationID int, ParentOrganizationID int, OrganizationCodeID int)
INSERT INTO #LibListHolds
SELECT o.OrganizationID, o.ParentOrganizationID, o.OrganizationCodeID
FROM Polaris.Organizations o (NOLOCK)
LEFT JOIN Polaris.SILS_ClosedBranch_List cb
on o.OrganizationID = cb.OrganizationID and cb.OrganizationCodeID = 3
and cb.Exception != 1 AND cb.Restored = 1 and cb.HoldInactivation = 1
LEFT JOIN Polaris.SILS_ClosedBranch_List cbe -- Exceptions
on o.OrganizationID = cbe.OrganizationID and cbe.OrganizationCodeID = 3
and cbe.Exception = 1
WHERE o.OrganizationCodeID = 3
AND cbe.OrganizationID is NULL
AND (cb.OrganizationID is not null
OR o.ParentOrganizationID IN
(SELECT
cb.OrganizationID
FROM Polaris.SILS_ClosedBranch_List cb
WHERE cb.OrganizationCodeID = 2
AND cb.Restored = 1
and cb.HoldInactivation = 1
))
-- History of hold inactivations
CREATE TABLE #HoldHist (SysHoldRequestID int)
INSERT INTO #HoldHist
SELECT
shr.SysHoldRequestID
FROM Polaris.SysHoldRequests shr (nolock)
INNER JOIN #LibListHolds ll
on shr.PickupBranchID = ll.OrganizationID
INNER JOIN Polaris.SILS_Hold_Inactivations hi (nolock)
on shr.SysHoldRequestID = hi.SysholdrequestID
WHERE shr.SysHoldStatusID = 1
GROUP BY shr.SysHoldRequestID
-- These updates will queue the holds to be reactivated by the job 'Hold Request Process Inactive Requests Nightly'
UPDATE shr
SET shr.ActivationDate = Getdate()
FROM Polaris.SysHoldRequests shr
INNER JOIN #HoldHist hl
on shr.SysholdrequestID = hl.SysholdrequestID
UPDATE hi
SET hi.DateRestored = Getdate()
FROM Polaris.SILS_Hold_Inactivations hi
INNER JOIN #HoldHist hl
on hi.SysholdrequestID = hl.SysholdrequestID
-- Restore branch hours
UPDATE h
SET h.value = oh.BranchHours
FROM polaris.polaris.OrganizationsPPPP h (nolock)
INNER JOIN #LibList ll
on h.OrganizationID = ll.OrganizationID
INNER JOIN Polaris.SILS_ClosedBranch_OriginalHours oh (nolock)
on ll.OrganizationID = oh.OrganizationID
WHERE h.AttrID = 96
and h.Value = 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED'
UPDATE oh
SET oh.RestoredDate = CURRENT_TIMESTAMP
FROM Polaris.SILS_ClosedBranch_OriginalHours oh
INNER JOIN #LibList ll
on ll.OrganizationID = oh.OrganizationID
INNER JOIN polaris.polaris.OrganizationsPPPP h (nolock)
on h.OrganizationID = ll.OrganizationID and h.AttrID = 96
WHERE oh.BranchHours is not null and oh.RestoredDate is null
and h.Value != 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED'
-- Remove branch from updating list
DELETE FROM cb
FROM Polaris.SILS_ClosedBranch_List cb
INNER JOIN #LibList ll
on cb.OrganizationID = ll.OrganizationID
WHERE cb.Restored = 1 and cb.Exception != 1
-- Delete exceptions when the restored org is a library
DELETE FROM cb
FROM Polaris.SILS_ClosedBranch_List cb
INNER JOIN Polaris.Organizations o (nolock)
on o.OrganizationID = cb.OrganizationID
INNER JOIN Polaris.SILS_ClosedBranch_List cbp
on o.ParentOrganizationID = cbp.OrganizationID and cbp.OrganizationCodeID = 2
and cbp.Exception != 1 and cbp.Restored = 1
INNER JOIN #LibList ll
on cbp.OrganizationID = ll.ParentOrganizationID
WHERE cb.Exception = 1 and cb.OrganizationCodeID = 3
-- Delete restored libraries
DELETE FROM cb
FROM Polaris.SILS_ClosedBranch_List cb
INNER JOIN #LibList ll
on cb.OrganizationID = ll.ParentOrganizationID
WHERE cb.OrganizationCodeID = 2 and cb.Exception != 1 and cb.Restored = 1
DROP TABLE #LibList
DROP TABLE #HoldHist
DROP TABLE #LibListHolds
END