USE [befri_Goldhoo]
GO

/****** Object:  View [basedata].[V_Inventory]    Script Date: 2020/12/3 17:12:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [basedata].[V_Inventory]
AS
SELECT DISTINCT 
                   CASE WHEN Cu.CustomItemId IS NOT NULL THEN 2 WHEN Co.CombtItemId IS NOT NULL 
                   THEN 1 ELSE 0 END AS CombType, CASE WHEN Cu.CustomItemId IS NOT NULL 
                   THEN '定制组合' WHEN Co.CombtItemId IS NOT NULL THEN '板式组合' ELSE '否' END AS CombTypeName, i.InventoryId, 
                   i.InventoryTypeId, it.TypeName AS InventoryTypeName, i.InventoryCategoryId, 
                   ic.CategoryNumber AS InventoryCategoryNumber, ic.CategoryName AS InventoryCategoryName, i.UomId, u.UomName, 
                   i.InventorySourceId, i.UsedSpaceId, i.DecorationStyleId, i.MaterialId, i.ForPeopleId, i.InventoryStateId, i.InventoryColorId, 
                   i.FactoryId, i.VendorId, i.InventoryNumber, i.InventoryName, i.Spec, i.AliasName, i.SearchCode, i.Abc, i.Weight, 
                   i.GrossWeight, i.WeightUom, i.Volume, i.PackagedValume, i.VolumeUom, i.Length, i.Width, i.Height, i.LengthUom, i.Light, 
                   i.Crafts, i.PackageQty, i.ShelfLife, i.SafetyInvtQty, i.SafetyPercentage, i.MaxInvtQty, i.MinInvtQty, i.AcquisitionCycle, 
                   i.DeliveryCycle, i.AcquisitionCost, i.IsMaintainable, i.IsSalable, i.IsHot, i.IsPromo, i.IsGift, i.IsSpecialOffer, i.IsNew, 
                   i.IsRecommend, i.IsClearance, i.IsCustomizable, i.IsDiscount, i.Dynamic, i.CapacityConsume, i.Barcode, i.CostPrice, i.SalesPrice, i.Added, 
                   i.AddedBy, i.AddedOn, i.ServiceStoped, i.ServiceStopedOn, i.Closed, i.ClosedBy, i.ClosedOn, i.Status, i.VersionNumber, 
                   i.CreatedBy, i.CreatedOn, i.ModifiedBy, i.ModifiedOn, i.Description, i.PriceType, i.U8CategoryName, i.U8Category, 
                   vtm.Text AS PriceTypeText, vimi.ThumbnailUrl, vimi.HandledUrl, i.DeliveryCycleStr
FROM      basedata.Inventory AS i LEFT OUTER JOIN
                   basedata.InventoryCategory AS ic ON ic.InventoryCategoryId = i.InventoryCategoryId LEFT OUTER JOIN
                   basedata.Uom AS u ON u.UomId = i.UomId INNER JOIN
                   basedata.InventoryType AS it ON it.InventoryTypeId = i.InventoryTypeId INNER JOIN
                   dbo.ValueTextMap AS vtm ON vtm.Value = i.PriceType AND vtm.TableName = 'basedata.Inventory' AND 
                   vtm.FieldName = 'PriceType' LEFT OUTER JOIN
                   basedata.CombtItemMember AS Co ON i.InventoryId = Co.SuitId LEFT OUTER JOIN
                   basedata.CustomItemMember AS Cu ON i.InventoryId = Cu.MemberItemId LEFT OUTER JOIN
                   basedata.V_InventoryMasterImage AS vimi ON i.InventoryId = vimi.InventoryId

GO