USE [befri_Goldhoo]
GO

/****** Object:  View [basedata].[V_Inventory]    Script Date: 2021/9/15 17:46:09 ******/
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 ,
            i.ProductionProperty,
			vtmp.Text AS ProductionPropertyText,
			i.Price
    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
            LEFT OUTER JOIN dbo.ValueTextMap AS vtmp ON i.ProductionProperty = vtmp.Value
                                                        AND vtmp.TableName = 'basedata.Inventory'
                                                        AND vtmp.FieldName = 'ProductionProperty' 




GO