-- 2023R2
SELECT
                       
                           ei.ItemId AS ItemID,
                           ISNULL(PurchaseExclReturnsValue, 0) AS PurchaseExclReturnsValue,
                           ISNULL(PurchaseExclReturnsQty,0) AS PurchaseExclReturnsQty, 
                           ISNULL(PurchaseReturnsValue,0) AS PurchaseReturnsValue,
                           ISNULL(PurchaseReturnsQty,0) AS PurchaseReturnsQty,
                           ISNULL(TransferInValue,0) AS TransferInValue,
                           ISNULL(TransferInQty,0) AS TransferInQty,
                           ISNULL(TransferOutValue,0) AS TransferOutValue,
                           ISNULL(TransferOutQty,0) AS TransferOutQty,
                           ISNULL(BOMMakeValue,0) AS BOMMakeValue,
                           ISNULL(BOMMakeQty,0) AS BOMMakeQty,
                           ISNULL(BOMUseValue,0) AS BOMUseValue,
                           ISNULL(BOMUseQty,0) AS BOMUseQty,
                           ISNULL(SalesQty,0) AS SalesQty,
                           ISNULL(SalesValue,0) AS SalesValue,
                           ISNULL(WasteQty,0) AS WasteQty,
                           ISNULL(WasteValue,0) AS WasteValue,
                           ISNULL(SamplingQty,0) AS SamplingQty,
                           ISNULL(SamplingValue,0) AS SamplingValue,              
                           ISNULL(OtherAdjustQty,0) AS OtherAdjustQty,
                           ISNULL(OtherAdjustValue,0) AS OtherAdjustValue,
                           ISNULL(DispatchQty,0) AS DispatchQty,
                           ISNULL(DispatchValue,0) AS DispatchValue,
                           ISNULL(ClosingStockQty,0) AS ClosingStockQty,
                           ISNULL(ClosingStockValue,0) AS ClosingStockValue
                         FROM
                           tbEntityItem ei with (nolock)
                        inner  JOIN
                           tbDailyStockBalance dsb with (nolock)
                         ON dsb.EntityID=ei.EntityID
                         AND dsb.BusinessDay='2023-02-22'
                         AND dsb.ItemID=ei.ItemID
						 and ei.InUse = 1
						 INNER JOIN tbitem i with (nolock) on i.ItemID = ei.ItemID
						-- and (ei.DiscontinueDate is null or ei.DiscontinueDate > getdate())
						 where ei.EntityID = 271 and i.ItemType <> 'BOM'
						  
                         ORDER BY ei.ItemID


	-- 2023R2

							 			   SELECT 
                         
                          ItemId,
                          0 PurchaseExclReturnsValue,
                          0 PurchaseExclReturnsQty,  
                          0 PurchaseReturnsValue, 
                          0 PurchaseReturnsQty, 
                          0 TransferInValue,
                          0 TransferInQty,
                          0 TransferOutValue,
                          0 TransferOutQty,
                          0 BOMMakeValue,
                          0 BOMMakeQty,
                          0 BOMUseValue,
                          0 BOMUseQty,
                          0 SalesQty,
                          0 SalesValue,
                          0 WasteQty,
                          0 WasteValue,
                          0 SamplingQty,
                          0 SamplingValue,               
                          0 OtherAdjustQty,
                          0 OtherAdjustValue,
                          0 DispatchQty,
                          0 DispatchValue,
                          0 ClosingStockQty,
                          0 ClosingStockValue,
                          0 FinishedWasteQty,
                          0 FinishedWasteValue
                        FROM
                          tbEntityItem ei with (nolock)
						  INNER JOIN tbitem i with (nolock) on i.ItemID = ei.ItemID
						  where ei.InUse = 1
						  and ei.EntityID = 902 and i.ItemType <> 'BOM'
                        --WHERE ClassCode = 'IV'  
                        ORDER BY ItemID


--------------------------------------------------------------------------------------------

-- 2023R1

						SELECT
                     
                           i.ItemId AS ItemID,
                           ISNULL(PurchaseExclReturnsValue, 0) AS PurchaseExclReturnsValue,
                           ISNULL(PurchaseExclReturnsQty,0) AS PurchaseExclReturnsQty, 
                           ISNULL(PurchaseReturnsValue,0) AS PurchaseReturnsValue,
                           ISNULL(PurchaseReturnsQty,0) AS PurchaseReturnsQty,
                           ISNULL(TransferInValue,0) AS TransferInValue,
                           ISNULL(TransferInQty,0) AS TransferInQty,
                           ISNULL(TransferOutValue,0) AS TransferOutValue,
                           ISNULL(TransferOutQty,0) AS TransferOutQty,
                           ISNULL(BOMMakeValue,0) AS BOMMakeValue,
                           ISNULL(BOMMakeQty,0) AS BOMMakeQty,
                           ISNULL(BOMUseValue,0) AS BOMUseValue,
                           ISNULL(BOMUseQty,0) AS BOMUseQty,
                           ISNULL(SalesQty,0) AS SalesQty,
                           ISNULL(SalesValue,0) AS SalesValue,
                           ISNULL(WasteQty,0) AS WasteQty,
                           ISNULL(WasteValue,0) AS WasteValue,
                           ISNULL(SamplingQty,0) AS SamplingQty,
                           ISNULL(SamplingValue,0) AS SamplingValue,              
                           ISNULL(OtherAdjustQty,0) AS OtherAdjustQty,
                           ISNULL(OtherAdjustValue,0) AS OtherAdjustValue,
                           ISNULL(DispatchQty,0) AS DispatchQty,
                           ISNULL(DispatchValue,0) AS DispatchValue,
                           ISNULL(ClosingStockQty,0) AS ClosingStockQty,
                           ISNULL(ClosingStockValue,0) AS ClosingStockValue
                         FROM
                           tbItem i with (nolock)
                         left JOIN
                           tbDailyStockBalance dsb with (nolock)
                         ON dsb.EntityID=902
                         AND dsb.BusinessDay='2023-02-22'
                         AND dsb.ItemID=i.ItemID
                         ORDER BY i.ItemID


 -- 2023R1
						 	 			   SELECT 
                        
                          ItemId,
                          0 PurchaseExclReturnsValue,
                          0 PurchaseExclReturnsQty,  
                          0 PurchaseReturnsValue, 
                          0 PurchaseReturnsQty, 
                          0 TransferInValue,
                          0 TransferInQty,
                          0 TransferOutValue,
                          0 TransferOutQty,
                          0 BOMMakeValue,
                          0 BOMMakeQty,
                          0 BOMUseValue,
                          0 BOMUseQty,
                          0 SalesQty,
                          0 SalesValue,
                          0 WasteQty,
                          0 WasteValue,
                          0 SamplingQty,
                          0 SamplingValue,               
                          0 OtherAdjustQty,
                          0 OtherAdjustValue,
                          0 DispatchQty,
                          0 DispatchValue,
                          0 ClosingStockQty,
                          0 ClosingStockValue,
                          0 FinishedWasteQty,
                          0 FinishedWasteValue
                        FROM
                          tbItem with (nolock)
                        --WHERE ClassCode = 'IV'  
                        ORDER BY ItemID