USE [befri_Goldhoo]
GO

/****** Object:  View [Promo].[V_PromoSku]    Script Date: 2020/11/20 11:20:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [Promo].[V_PromoSku]
AS
SELECT  pa.Quantity AS AccQuantity, pops.Quantity AS BuyQuantity, ISNULL(ps.MaxQty, 999) - ISNULL(pops.Quantity, 0) 
                   AS AllowQuantity, member.Price, rr.OriginalUrl, rr.HandledUrl, rr.ThumbnailUrl, ps.PromoSkuId, ps.PromoActivityId, 
                   ps.SkuName, ps.Spec, ps.SalesPrice, ps.SalesDiscount, ps.MaxQty, ps.MinQty, ps.ResourceId, ps.ModifiedBy, 
                   ps.ModifiedOn, ps.CreatedBy, ps.CreatedOn, ps.Description, pay.ActivityName
FROM      Promo.PromoSku AS ps LEFT OUTER JOIN
                   Promo.PromoAcc AS pa ON pa.PromoSkuId = ps.PromoSkuId LEFT OUTER JOIN
                       (SELECT  PromoSkuId, SUM(ISNULL(Quantity, 0)) AS Quantity
                        FROM       shop_order.PurchaseOrderPromoSkus
                        GROUP BY PromoSkuId) AS pops ON pops.PromoSkuId = ps.PromoSkuId LEFT OUTER JOIN
                       (SELECT  PromoSkuId, SUM(Quantity * Price) AS Price
                        FROM       Promo.PromoSkuMember
                        GROUP BY PromoSkuId) AS member ON member.PromoSkuId = ps.PromoSkuId LEFT OUTER JOIN
                   dbo.ResourceRepository AS rr ON ps.ResourceId = rr.ResourceId
				   LEFT OUTER JOIN
  Promo.PromoActivity AS pay ON ps.PromoActivityId = pay.PromoActivityId                 

GO