USE [befri_Goldhoo]
GO

/****** Object:  View [shop_order].[V_CombtItemMember]    Script Date: 2021/9/16 10:11:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [shop_order].[V_CombtItemMember]
AS
SELECT   cim.CombtItemId AS InventoryId, i.InventoryTypeId AS ItemType, i.InventoryId AS ItemId, i.InventoryCategoryId, 
                i.InventoryNumber AS ItemNumber, i.InventoryName AS ItemName, i.Spec, i.SearchCode, i.IsSalable, i.IsCustomizable, 
                i.CostPrice, i.SalesPrice, cim.Quantity, i.Added, i.Status, i.Description, i.PriceType, i.DeliveryCycle, 
                i.DeliveryCycleStr, i.ProductionProperty, vtm.Text AS ProductionPropertyText, i.Price
FROM      basedata.Inventory i INNER JOIN
                basedata.CombtItemMember cim ON i.InventoryId = cim.SuitId LEFT OUTER JOIN
                dbo.ValueTextMap AS vtm ON vtm.TableName = 'basedata.Inventory' AND vtm.FieldName = 'ProductionProperty' AND 
                i.ProductionProperty = vtm.Value
UNION ALL
SELECT   cim.CustomItemId AS InventoryId, i.InventoryTypeId AS ItemType, i.InventoryId AS ItemId, i.InventoryCategoryId, 
                i.InventoryNumber AS ItemNumber, i.InventoryName AS ItemName, i.Spec, i.SearchCode, i.IsSalable, i.IsCustomizable, 
                i.CostPrice, i.SalesPrice, cim.Quantity, i.Added, i.Status, i.Description, i.PriceType, i.DeliveryCycle, 
                i.DeliveryCycleStr, i.ProductionProperty, vtm.Text AS ProductionPropertyText, i.Price
FROM      basedata.Inventory i INNER JOIN
                basedata.CustomItemMember cim ON i.InventoryId = cim.MemberItemId LEFT OUTER JOIN
                dbo.ValueTextMap AS vtm ON vtm.TableName = 'basedata.Inventory' AND vtm.FieldName = 'ProductionProperty' AND 
                i.ProductionProperty = vtm.Value


GO