IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[inventory].[ListCountableItems]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [inventory].[ListCountableItems]
GO

/****** Object:  StoredProcedure [inventory].[ListCountableItems]    Script Date: 09/25/2020 18:52:45 ******/  
  
CREATE PROCEDURE [inventory].[ListCountableItems]  
	@EntityID bigint 
AS 

BEGIN   

SELECT   
   tbEntityLocation.Location   AS Location,   
   tbEntityLocation.LocationType  AS LocationType,   
   tbEntityLocation.EntityLocationID AS EntityLocationID,   
   tbEntityLocation.SortOrder   AS SortOrder,   
   tbEntityLocationItem.TravelPath  AS TravelPath,   
   CONVERT(VARCHAR(50), tbItem.ItemID) AS ItemID,   
   ISNULL(UOM1.UOMName, '')   AS [Outer],     
   ISNULL(UOM2.UOMName, '')            AS [Inner],   
   ISNULL(tbItem.InventoryUnit, '')    AS [Unit],     
   ISNULL(UOM4.UOMName, '')            AS [Weight],   
   tbItem.Description     AS [Description],   
   tbItem.ItemCode      AS [Code],   
   REVERSE(RIGHT('00000000' + (dbo.ConvertToBinary((((COALESCE(tbEntityItem.CountTypeDefault, POWER(2, 30) - 1) & COALESCE(tbItem.CountType, POWER(2, 2))))   
    | (( ~COALESCE(tbEntityItem.CountTypeDefault, POWER(2, 30) - 1) & COALESCE(tbEntityItem.CountTypeSpecific, 0)) ) ))), 8))    
            AS Frequency,   
   'R'         AS [Type]      
  FROM    
   tbEntityLocationItem WITH (NOLOCK)    
    JOIN tbEntityLocation WITH (NOLOCK) ON   
     (tbEntityLocationItem.EntityLocationID = tbEntityLocation.EntityLocationID)   
    JOIN tbItem WITH (NOLOCK) ON   
     (tbEntityLocationItem.ItemID = tbItem.ItemID)   
    JOIN tbEntityItem WITH (NOLOCK) ON   
     (tbEntityLocation.EntityID = tbEntityItem.EntityID)    
      AND (tbEntityLocationItem.ItemID = tbEntityItem.ItemID)   
    LEFT OUTER JOIN tbUnitOfMeasure UOM1 WITH (NOLOCK) ON    
     tbEntityItem.StockTakeUnit1ID = UOM1.UnitOfMeasureID   
    LEFT OUTER JOIN tbUnitOfMeasure UOM2 WITH (NOLOCK) ON    
     tbEntityItem.StockTakeUnit2ID = UOM2.UnitOfMeasureID   
    LEFT OUTER JOIN tbUnitOfMeasure UOM4 WITH (NOLOCK) ON    
     tbEntityItem.StockTakeUnit4ID = UOM4.UnitOfMeasureID   
  Where    
    tbEntityLocation.EntityID  = @EntityID   
   AND tbEntityItem.PerformStockCount = 1   
   AND tbEntityItem.InUse    = 1   
   AND tbEntityItem.AutoStockCount  = 0   
   AND tbEntityLocation.IsActive  = 1   
   
  UNION ALL   
     
  SELECT   
   tbEntityLocation.Location   AS Location,   
   tbEntityLocation.LocationType  AS LocationType,   
   tbEntityLocation.EntityLocationID AS EntityLocationID,   
   tbEntityLocation.SortOrder   AS SortOrder,   
   tbEntityLocationItem.TravelPath  AS TravelPath,   
   CONVERT(VARCHAR(50), tbItem.ItemID) + '-' + CONVERT(VARCHAR(50),tbVendorEntityItem.VendorEntityItemId)   
            AS ItemID,   
   ISNULL(tbVendorEntityItem.PurchaseUnit,'')    
            AS [Outer],     
   ISNULL(tbUnitOfMeasure.UOMName, '') AS [Inner],   
   ISNULL(tbItem.InventoryUnit, '')    AS [Unit],     
   ''                                  AS [Weight],     
   tbVendorEntityItem.VendorDescription AS [Description],   
   tbVendorEntityItem.VendorCode  AS [Code],   
   REVERSE(RIGHT('00000000' + (dbo.ConvertToBinary((  ((COALESCE(tbEntityItem.CountTypeDefault, POWER(2, 30) - 1) & COALESCE(tbItem.CountType, POWER(2, 2))) )   
    | (( ~COALESCE(tbEntityItem.CountTypeDefault, POWER(2, 30) - 1) & COALESCE(tbEntityItem.CountTypeSpecific, 0)) ) ))), 8))    
            AS Frequency,   
   'C'         AS [Type]   
  FROM    
   tbEntityLocationItem WITH (NOLOCK)    
    JOIN tbEntityLocation WITH (NOLOCK) ON   
     tbEntityLocationItem.EntityLocationID = tbEntityLocation.EntityLocationID   
    JOIN tbItem WITH (NOLOCK) ON   
     tbEntityLocationItem.ItemID = tbItem.ItemID   
    JOIN tbEntityItem WITH (NOLOCK) ON   
     tbEntityLocation.EntityID = tbEntityItem.EntityID   
      AND tbEntityLocationItem.ItemID = tbEntityItem.ItemID   
    JOIN tbVendorEntityItem WITH (NOLOCK) ON   
     tbEntityLocation.EntityID = tbVendorEntityItem.EntityID   
      AND tbEntityLocationItem.ItemID = tbVendorEntityItem.ItemID   
    LEFT JOIN tbUnitOfMeasure WITH (NOLOCK)  ON   
     tbVendorEntityItem.StockCountUnit2ID = tbUnitOfMeasure.UnitOfMeasureID   
        
   WHERE    
     tbEntityLocation.EntityID    = @EntityID   
    AND tbVendorEntityItem.PerformStockCount = 1   
    AND tbEntityItem.InUse      = 1   
    AND tbEntityItem.AutoStockCount    = 0   
    AND tbEntityLocation.IsActive    = 1   
  ORDER BY [TravelPath], [Description]
   
END   
   
GO
