USE [befri_Goldhoo]
GO

/****** Object:  View [replenish].[V_ReplenishOrder]    Script Date: 2021/6/8 16:32:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE
--ALTER
VIEW [replenish].[V_ReplenishOrder_OrderList]
AS
SELECT    Re.ReplenishOrderId, Re.OrderNumber, Re.OrderedOn, Re.RequiredOn, Re.ShopId, Sh.ShopName, Re.CreditType, 
                 Cr.Text AS CreditTypeName, Re.ShippingMode, Shm.Text AS ShippingModeName, Re.ReceiptedAt, Re.PreparedBy, Re.PreparedOn, 
                 Re.OrderStatus, Re.VersionNumber, Rel.ApprovedBy, Rel.ApprovedOn, Rel.ApprovedIdea, Re.CreatedBy, Re.CreatedOn, 
                 Re.ModifiedBy, Re.ModifiedOn, Re.Description, Rel.ReplenishOrderLineId, Rel.CusRepNumber, Rel.InventoryId, Rel.InventoryNumber, 
                 Rel.InventoryName, Rel.Spec, Rel.ReplenishReasonId, RrN.Text AS ReasonTypeName, Rr.ReasonName, Rel.ReplenishReasonMemo, 
                 Rel.RepPosition, Rel.RepMemo, Rel.Quantity, Rel.BearPrice, Rel.IsFree, Rel.ProduceReceivingBy, Rel.ProduceReceivingOn, 
                 Rel.ExpFinishOn, Rel.FinishOnBy, Rel.FinishOn, Rel.LineStatus, Ls.Text AS LineStatusName, Res.ResourceQty, Rel.ReplenishName, 
                 Rel.ReplenishCycle, Rel.ReplenishType, Retype.Text AS ReplenishTypeName, Ls.Text AS OrderStatusName, Rel.ReplenishId, 
                 Rel.RetNumber, Rel.VendorId, vd.VendorName, Il.LogCount, Rel.DeliveryBy, Rel.DeliveryOn, Rel.LogisticsNo
FROM       replenish.ReplenishOrder AS Re INNER JOIN
                 replenish.ReplenishOrderLine AS Rel ON Rel.ReplenishOrderId = Re.ReplenishOrderId LEFT OUTER JOIN
                 basedata.Shop AS Sh ON Sh.ShopId = Re.ShopId LEFT OUTER JOIN
                 dbo.ValueTextMap AS Cr ON Re.CreditType = Cr.Value AND Cr.TableName = 'sales.SalesOrder' AND 
                 Cr.FieldName = 'CreType' LEFT OUTER JOIN
                 dbo.ValueTextMap AS Shm ON Re.ShippingMode = Shm.Value AND Shm.TableName = 'replenish.ReplenishOrder' AND 
                 Shm.FieldName = 'ShippingMode' LEFT OUTER JOIN
                 basedata.ReplenishReason AS Rr ON Rel.ReplenishReasonId = Rr.ReplenishReasonId LEFT OUTER JOIN
                 dbo.ValueTextMap AS RrN ON Rr.ReasonType = RrN.Value AND RrN.TableName = 'basedata.ReplenishReason' AND 
                 RrN.FieldName = 'ReasonType' LEFT OUTER JOIN
                 dbo.ValueTextMap AS Ls ON Rel.LineStatus = Ls.Value AND Ls.TableName = 'replenish.ReplenishOrderLine' AND 
                 Ls.FieldName = 'LineStatus' LEFT OUTER JOIN
				 basedata.Vendor AS vd ON Rel.VendorId = vd.VendorId LEFT OUTER JOIN
                 dbo.ValueTextMap AS Retype ON Rel.ReplenishType = Retype.Value AND Retype.TableName = 'replenish.ReplenishOrderLine' AND 
                 Retype.FieldName = 'ReplenishType' LEFT OUTER JOIN
                     (SELECT    CusRepNumber, COUNT(CusReplenishResourceId) AS ResourceQty
                      FROM       replenish.CusReplenishResource AS Crr
                      GROUP BY CusRepNumber) AS Res ON Res.CusRepNumber = Rel.CusRepNumber
				LEFT OUTER JOIN (SELECT SourceId, COUNT(*) AS LogCount FROM basedata.InfoLogs GROUP BY SourceId) AS IL ON Rel.ReplenishOrderLineId = Il.SourceId

GO