USE [Polaris]
GO

/****** Object:  StoredProcedure [Polaris].[SILS_RPT_In-Transit_Shelf-Check]    Script Date: 2023-11-23 3:13:56 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [Polaris].[SILS_RPT_In-Transit_Shelf-Check]

	@BeginDate datetime, --BeginDate -- time defaults to 00:00:00
	
	@OrganizationList varchar(MAX) -- generate a selectable list of agencies in the dropdown.
	
AS

BEGIN 

SET NOCOUNT ON

--create list of agencies for selection

CREATE TABLE #Branches (OrganizationID INT)  
	IF LEN(@OrganizationList) = 1 AND @OrganizationList= '0' -- ALL SELECTED
	BEGIN
		INSERT INTO #Branches 
			SELECT OrganizationID FROM Polaris.Organizations(NOLOCK) WHERE OrganizationCodeID in (3)
	END
	ELSE
	BEGIN
		EXEC ('INSERT INTO #Branches SELECT OrganizationID FROM Polaris.Organizations (NOLOCK) WHERE OrganizationID in(' + @OrganizationList + ')')  
	END 

select o2.Name as SentFromBranch, o3.name as SentToBranch, it.[Description] as Status, 
cir.ItemStatusDate, ird.CallNumber, br.BrowseTitle, br.BrowseAuthor,
cir.Barcode, o.Name as AssignedBranch, sl.[Description]
from polaris.CircItemRecords cir 
inner join polaris.ItemRecordDetails ird 
on cir.ItemRecordID = ird.ItemRecordID
inner join polaris.BibliographicRecords br 
on br.BibliographicRecordID = cir.AssociatedBibRecordID
inner join polaris.ItemStatuses it 
on it.ItemStatusID = cir.ItemStatusID
inner JOIN polaris.Organizations o 
on o.OrganizationID = cir.AssignedBranchID
inner join polaris.Organizations o2
on cir.InTransitSentBranchID = o2.OrganizationID
inner join polaris.Organizations o3
on cir.InTransitRecvdBranchID = o3.OrganizationID
LEFT join polaris.ShelfLocations sl 
on cir.ShelfLocationID = sl.ShelfLocationID
    and sl.OrganizationID = cir.AssignedBranchID
left join #Branches
on cir.InTransitSentBranchID = #Branches.OrganizationID
left join #Branches b2
on cir.InTransitRecvdBranchID = b2.OrganizationID
where  
cir.ItemStatusID in (4,5,6)
and cir.RecordStatusID = 1
and cir.ItemStatusDate < @BeginDate
and (#Branches.OrganizationID is not null
or b2.OrganizationID is not null)

group by o2.Name, o3.name, it.[Description], 
cir.ItemStatusDate, ird.CallNumber, br.BrowseTitle,  br.BrowseAuthor,
cir.Barcode, o.Name, sl.[Description]


END

GO


