Update ItemAvailabilityDisplay for Multiple Organizations
This query inserts a new branch into the ItemAvailabilityDisplay table and sets the Availability Level and the Sort Order for the branch within the table. Manual adjustment in Polaris SA may be necessary after running this query, but this query circumvents the tedious entry and set up of the new branch in all of the System, Library, and Branch Item Availability Display tables in PolarisSA.
This query will need to be run twice: First with the @IALevelID set to 1 and again with the @IALevelID set to 2. Sort orders can be changed between queries as well.
-- Declare and set variables
DECLARE @BranchID INT; -- Used by the CURSOR
DECLARE @IALevelID INT = 1; -- 1 = System Level | 2 = Local Level. Run once at 1 and again at 2.
DECLARE @Sort INT = 24; -- Set the sort order you want. This will put it at the same sort order for all current branches/libraries.
DECLARE @BranchLocID INT = 12; -- Set the OrganizationID of the new library/branch you're adding.
-- Create a table to pull only the OrganizationIDs we want to effect with this insert.
CREATE TABLE #TempBranchesIDs (
OrganizationID INT
)
INSERT INTO #TempBranchesIDs
SELECT
OrganizationID
FROM
Polaris.Polaris.Organizations WITH (NOLOCK)
WHERE
OrganizationID NOT IN (1,2,87) -- Exclude branches as needed
-- Set up a CURSOR to work through the above table.
DECLARE IAD_Cursor CURSOR FOR
SELECT OrganizationID FROM #TempBranchesIDs
-- Put the CURSOR to work
OPEN IAD_Cursor
-- Go get the first entry in the #TempBranchesIDs table
FETCH NEXT FROM IAD_Cursor INTO @BranchID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Polaris.Polaris.ItemAvailabilityDisplay (OrganizationID, ItemAvailabilityLevelID, SortOrder, BranchLocationOrgID)
VALUES (@BranchID, @IALevelID, @Sort, @BranchLocID)
FETCH NEXT FROM IAD_Cursor INTO @BranchID
END
-- Tidy up
CLOSE IAD_Cursor
DEALLOCATE IAD_Cursor
DROP TABLE #TempBranchesIDs