
/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 10/12/2022 2:20:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ViwAccWarehouseRem]
AS
	select coalesce(Ware.OrderTotalID,INPUT.OrderTotalID,Delivery.OrderTotalID,0) AS OrderTotalID, 
			coalesce(Ware.OrderControlID,INPUT.OrderControlID,Delivery.OrderControlID,0) AS OrderControlID,
			coalesce(Ware.OrderDetailID1,INPUT.OrderDetailID1,Delivery.OrderDetailID1,0) AS OrderDetailID1,
			coalesce(Ware.OrderDetailID2,INPUT.OrderDetailID2,Delivery.OrderDetailID2,0) AS OrderDetailID2,
			coalesce(Ware.WarehouseNo,INPUT.WarehouseNo,Delivery.WarehouseNo,0) AS WarehouseNo, 
			coalesce(Sum(Input.Request),0) as Request, coalesce(Sum(Delivery.DeliveryValue),0) as DeliveryValue, coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0) as RequestRem,
			coalesce(Sum(Ware.RequestIn),0) as RequestIn, coalesce(Sum(Ware.RequestOut),0) as RequestOut, coalesce(Sum(Ware.RequestIn),0)-coalesce(Sum(Ware.RequestOut),0) as WareRem,
			coalesce(Sum(Ware.RequestIn),0)-coalesce(Sum(Ware.RequestOut),0)-(coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0)) as TotalDiff,
			coalesce(Sum(Delivery.DeliveryRem),0) as DeliveryRem, coalesce(Sum(Ware.RequestIn),0)-coalesce(Sum(Ware.RequestOut),0)-(coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0))+coalesce(Sum(Delivery.DeliveryRem),0) as Diff

	from 
	(	
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(Request),0) as Request
		From ViwAccWarehouseDetails  with(nolock)
		where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (0,2,4,6,7,10,13,121)) and Len(SerialFrom)>5 and Len(SerialTo)>5
		Group By OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo 
		--HAVING WarehouseNo =2
		--having orderdetailid2=2163
	) as Input FULL OUTER join 
	(	
		Select detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, WarehouseNo, coalesce(Sum(Delivery.VRequest),0) as DeliveryValue, 
				coalesce(Sum(Detail.Request),0)-coalesce(Sum(Detail.VRequest),0) as DeliveryRem, coalesce(Sum(Detail.VRequest),0) as DetailVRequest
		From (SELECT * FROM AccWarehouseDelivery with(nolock) where Flags=0) as Delivery
		right outer join (Select ID, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, Request, VRequest, WarehouseNo from ViwAccWarehouseDetails with(nolock) where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (1,3,5,8,9,11,120) )) as Detail on Delivery.WareDetailsID = Detail.id
		Group By detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, Detail.WarehouseNo
		--HAVING WarehouseNo =2
		--SELECT * FROM AccDetail WHERE ID=2291
	) as Delivery on input.OrderTotalID=Delivery.OrderTotalID and Input.OrderControlID = Delivery.OrderControlID and Input.OrderDetailID1 = Delivery.OrderDetailID1 and Input.OrderDetailID2 = Delivery.OrderDetailID2 and Input.WarehouseNo = Delivery.WarehouseNo 
	left outer join 
	(
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(RequestIn),0) as RequestIn, coalesce(Sum(RequestOut),0) as RequestOut, coalesce(Sum(RequestIn),0) - coalesce(Sum(RequestOut),0) as RequestRem 
		from ViwAccWareHouse with(nolock)
		group by OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo 
		--HAVING WarehouseNo =2
	) as Ware on Ware.OrderTotalID=input.OrderTotalID and Ware.OrderControlID = input.OrderControlID and Ware.OrderDetailID1 = input.OrderDetailID1 and Ware.OrderDetailID2 = input.OrderDetailID2 and ware.WarehouseNo=input.WarehouseNo
	Group By Ware.OrderTotalID, Ware.OrderControlID, Ware.OrderDetailID1, Ware.OrderDetailID2, 
			input.OrderTotalID, input.OrderControlID, input.OrderDetailID1, input.OrderDetailID2, 
			Delivery.OrderTotalID, Delivery.OrderControlID, Delivery.OrderDetailID1, Delivery.OrderDetailID2, 
			Ware.WarehouseNo,INPUT.WarehouseNo,Delivery.WarehouseNo
GO


