USE [befri_Goldhoo]
GO

/****** Object:  View [replenish].[V_ReplenishOrder]    Script Date: 2021/3/27 23:00:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [replenish].[V_ReplenishOrder]
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, 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.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, 
                Rel.Quantity, Re.OrderStatus, Re.VersionNumber
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 Cr.TableName = 'shop_order.PurchaseOrder' AND 
                Cr.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
                dbo.ValueTextMap AS Retype ON Rel.ReplenishType = Retype.Value AND 
                Ls.TableName = 'replenish.ReplenishOrderLine' AND Ls.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

GO