| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129 |
- 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
|
