GoldHoo/doc/调整记录/已更新正式环境/视图20210322.1046.sql

USE [befri_Goldhoo]
GO

/****** Object:  View [replenish].[V_ReplenishOrder]    Script Date: 2021/3/29 15:27:40 ******/
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, 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
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