Mastodon Mastodon Skip to main content

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