/** ** 库存明细查询 视图（注意：这是视图vBase_ProductPosition，不是vvBase_ProductPosition）【EditBy shaocx,2022-10-16】 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='vBase_ProductPosition') DROP VIEW vBase_ProductPosition GO CREATE VIEW vBase_ProductPosition AS SELECT PP.ProductPosition_Id, PP.Class_Id, PP.ClassName, PP.MainID, PP.DetailID, PP.Storage_Id, PP.StorageName, PP.PositionName, PP.Product_Id, PP.Consignor_Id, PP.ConsignorCode, PP.ConsignorName, PP.BatchNumber, PP.ProduceDate, PP.ProductModel, PP.ProductSpec, PP.InStorageDate, PP.Provider_Id, PP.ProviderCode, PP.ProviderShortName, PP.ProductStorage, PP.OrignStorage, PP.PurchasePrice, PP.PurchaseMoney, SKU.Rate, PP.RatePrice, PP.RateMoney, PP.AvgPrice, PP.Remark, PP.CreateID, PP.Creator, PP.CreateDate, ISNULL ((SELECT SUM(PlaceholderStorage) AS Expr2 FROM dbo.Base_ProductPlaceHolder WHERE (ProductPosition_Id = PP.ProductPosition_Id)), 0) AS PlaceholderStorage, P.PositionType, P.IsLocked, P.Enable,-- 增加是否可用字段 【EditBy shaocx,2022-10-16】 PP.ExtendField01, PP.ExtendField02, PP.ExtendField03, PP.ExtendField04, PP.ExtendField05, PP.ExtendField06, PP.ExtendField07, PP.ExtendField08, PP.ExtendField09, PP.ExtendField10, P.IsFreeze, PP.SingleSignCode, PP.PlateCode, PP.RelationCode, ISNULL(PP.ProductStorage, 0) - ISNULL ((SELECT SUM(PlaceholderStorage) AS Expr2 FROM dbo.Base_ProductPlaceHolder AS Base_ProductPlaceHolder_1 WHERE (ProductPosition_Id = PP.ProductPosition_Id)), 0) AS ValidQty, PP.StorageStatus, PP.ProductAttribute, PP.DeclareNo, SKU.Brand_Id, SKU.BrandName, SKU.Type_Id, SKU.TypeName, SKU.BigType_Id, SKU.BigTypeName, SKU.FullType_Id, SKU.FullTypeName, PP.ShelfLifeDay, PP.ShelfLifeDate, PP.ValidShelfLifeDay, PP.BillCode, PP.EnterCode, PP.OrderCode, PP.ModifyID, PP.Modifier, PP.ModifyDate, PP.DeleteBy, SKU.NetWeight, SKU.SmallUnit, SKU.MiddleUnit, SKU.BigUnit, PP.LimitDate, PP.AreaCode, PP.Weight, (SELECT TOP (1) OrderNo FROM dbo.Base_StorageArea AS SA WHERE (Storage_Id = P.Storage_Id) AND (AreaCode = P.AreaCode) ORDER BY StorageArea_Id) AS AreaOrderNo, P.ShelveCode, P.ColumnCode, P.RowCode, SKU.StorageLower, CASE WHEN P.MinCapacity > 0 THEN P.MinCapacity ELSE SKU.StorageLower END AS MinCapacity, SKU.MiddleBarcode, SKU.BigBarcode, SKU.RelationCode2, SKU.RelationCode3, SKU.RelationCode4, SKU.RelationCode5, PP.UserProduct_Id, PP.UserProductCode, PP.UserProductAlias, PP.PlatUser_Id, PP.PlatUserCode, PP.PlatUserName, PP.PlatCorpName, PP.TotalWeight, PP.TotalWeightOrign, SKU.UnitConvert, SKU.UnitConvertText, SKU.SalePrice, PP.OriginPlace, PP.ContainerNo, SKU.UnitPackage, PP.ExpandFields, PP.DetailExpandFields, PP.PartStatus, PP.PlateType, P.ChannelCode, PP.DynamicWeight, PP.PlateTypeCode, PP.TrackingNumber, PP.SaleCode, PP.PoCode, PP.IsBoosterArm, PP.ItemNumber, P.PositionLength, SKU.ProductName, SKU.ProductCode, PP.QrCode_guid,PP.BarCode,PP.IsQrCode -- 增加 二维码GUIDI [EditBy shaocx,2023-11-5] FROM dbo.Base_ProductPosition AS PP LEFT OUTER JOIN dbo.Base_Position AS P ON PP.PositionName = P.PositionName AND PP.Storage_Id = P.Storage_Id INNER JOIN dbo.vBase_ProductInfo_SKU AS SKU ON PP.Product_Id = SKU.Product_Id GO /** ** 库存明细查询 视图 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='vvBase_ProductPosition') DROP VIEW vvBase_ProductPosition GO CREATE VIEW vvBase_ProductPosition AS SELECT dbo.Base_ProductPosition.ProductPosition_Id, dbo.Base_Position.IsLocked, dbo.Base_ProductPosition.BillCode, dbo.Base_ProductPosition.OrignStorage, dbo.Base_ProductPosition.ProductStorage, dbo.Base_ProductPosition.IsBoosterArm, dbo.Base_ProductPosition.PlateCode, dbo.Base_ProductPosition.PlateType, dbo.Base_ProductPosition.AreaCode, dbo.Base_ProductPosition.ExtendField01, dbo.Base_ProductPosition.ClassName, dbo.Base_ProductPosition.PositionName, dbo.Base_ProductPosition.InStorageDate, dbo.Base_ProductPosition.Product_Id, dbo.Base_ProductPosition.PoCode, dbo.Base_ProductPosition.TrackingNumber, dbo.Base_ProductPosition.SaleCode, dbo.Base_ProductPosition.ExtendField04, dbo.Base_ProductPosition.ExtendField03, dbo.Base_Position.PositionType, dbo.Base_ProductPosition.ExtendField05, dbo.Base_ProductPosition.ExtendField08, dbo.Base_ProductPosition.ExtendField09, dbo.Base_ProductInfo.SmallUnit, dbo.Base_ProductPosition.ContainerNo, dbo.Base_Position.IsFreeze, dbo.Base_Position.PositionLength, dbo.Base_ProductPosition.ItemNumber, dbo.Base_ProductInfo.ProductCode, dbo.Base_ProductInfo.ProductName, dbo.Base_ProductPosition.LimitDate, dbo.Base_ProductInfo.Brand_Id, dbo.Base_ProductInfo.Enable,-- 增加是否可用字段 【EditBy shaocx,2022-10-16】 0 as InStoreDays, -- 在库天数【EditBy shaocx,2022-06-08】 PhysicsHeight,LogicHeight, dbo.Base_ProductPosition.IsInMoreHighPosition, -- 是否入的是匹配自己高度的更高库位，这些库位需要移库的【EditBy shaocx,2022-09-19】 dbo.Base_Position.ShelveCode, -- 排数【EditBy shaocx,2022-09-19】 dbo.Base_Position.Remark as PositionRemark , -- 库位说明【EditBy shaocx,2022-09-19】 dbo.Base_ProductPosition.OpRemark,dbo.Base_ProductPosition.DoContainerNoTime, -- 增加 操作物料锁定的时间 [EditBy shaocx,2023-07-26] dbo.Base_ProductPosition.qrCode_guid,dbo.Base_ProductPosition.BarCode,dbo.Base_ProductPosition.IsQrCode FROM dbo.Base_Position INNER JOIN dbo.Base_ProductPosition ON dbo.Base_Position.PositionName = dbo.Base_ProductPosition.PositionName INNER JOIN dbo.Base_ProductInfo ON dbo.Base_ProductPosition.Product_Id = dbo.Base_ProductInfo.Product_Id GO /** ** 全部库存明细查询 视图（注意：是全部库存） 【Editby shaocx,2024-03-23】 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='vvBase_ProductPosition_all') DROP VIEW vvBase_ProductPosition_all GO CREATE VIEW vvBase_ProductPosition_all AS SELECT dbo.Base_ProductPosition.ProductPosition_Id, dbo.Base_Position.IsLocked, dbo.Base_ProductPosition.BillCode, dbo.Base_ProductPosition.OrignStorage, dbo.Base_ProductPosition.ProductStorage, dbo.Base_ProductPosition.IsBoosterArm, dbo.Base_ProductPosition.PlateCode, dbo.Base_ProductPosition.PlateType, dbo.Base_ProductPosition.AreaCode, dbo.Base_ProductPosition.ExtendField01, dbo.Base_ProductPosition.ClassName, dbo.Base_ProductPosition.PositionName, dbo.Base_ProductPosition.InStorageDate, dbo.Base_ProductPosition.Product_Id, dbo.Base_ProductPosition.PoCode, dbo.Base_ProductPosition.TrackingNumber, dbo.Base_ProductPosition.SaleCode, dbo.Base_ProductPosition.ExtendField04, dbo.Base_ProductPosition.ExtendField03, dbo.Base_Position.PositionType, dbo.Base_ProductPosition.ExtendField05, dbo.Base_ProductPosition.ExtendField08, dbo.Base_ProductPosition.ExtendField09, dbo.Base_ProductInfo.SmallUnit, dbo.Base_ProductPosition.ContainerNo, dbo.Base_Position.IsFreeze, dbo.Base_Position.PositionLength, dbo.Base_ProductPosition.ItemNumber, dbo.Base_ProductInfo.ProductCode, dbo.Base_ProductInfo.ProductName, dbo.Base_ProductPosition.LimitDate, dbo.Base_ProductInfo.Brand_Id, dbo.Base_ProductInfo.Enable,-- 增加是否可用字段 【EditBy shaocx,2022-10-16】 0 as InStoreDays, -- 在库天数【EditBy shaocx,2022-06-08】 PhysicsHeight,LogicHeight, dbo.Base_ProductPosition.IsInMoreHighPosition, -- 是否入的是匹配自己高度的更高库位，这些库位需要移库的【EditBy shaocx,2022-09-19】 dbo.Base_Position.ShelveCode, -- 排数【EditBy shaocx,2022-09-19】 dbo.Base_Position.Remark as PositionRemark , -- 库位说明【EditBy shaocx,2022-09-19】 dbo.Base_ProductPosition.OpRemark,dbo.Base_ProductPosition.DoContainerNoTime, -- 增加 操作物料锁定的时间 [EditBy shaocx,2023-07-26] dbo.Base_ProductPosition.qrCode_guid,dbo.Base_ProductPosition.BarCode,dbo.Base_ProductPosition.IsQrCode FROM dbo.Base_ProductPosition LEFT JOIN dbo.Base_Position ON dbo.Base_Position.PositionName = dbo.Base_ProductPosition.PositionName INNER JOIN dbo.Base_ProductInfo ON dbo.Base_ProductPosition.Product_Id = dbo.Base_ProductInfo.Product_Id GO /** ** 过期库存查询 视图 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='vOldLibraryEarlyWarning') DROP VIEW vOldLibraryEarlyWarning GO CREATE VIEW vOldLibraryEarlyWarning AS SELECT ProductPosition_Id, Storage_Id, StorageName, PositionName, Product_Id, ProductCode, ProductName, BatchNumber, qrCode_guid,BarCode,IsQrCode, PlateCode, (SELECT AreaCode FROM dbo.Base_Position WHERE (PositionName = L.PositionName) AND (Storage_Id = L.Storage_Id)) AS AreaCode, Consignor_Id, ConsignorCode, ConsignorName, InStorageDate, ProduceDate, (SELECT Type_Id FROM dbo.Base_ProductInfo WHERE (Product_Id = L.Product_Id)) AS Type_Id, (SELECT TypeName FROM dbo.Base_ProductInfo WHERE (Product_Id = L.Product_Id)) AS TypeName, (CASE WHEN ISNULL(ProduceDate, '') = '' THEN 0 ELSE DATEDIFF(DAY, ProduceDate, GETDATE()) END) AS ProduceDay, (CASE WHEN ISNULL(InStorageDate, '') = '' THEN 0 ELSE DATEDIFF(DAY, InStorageDate, GETDATE()) END) AS InDay, ProductStorage, SmallUnit, ProductSpec, UserProduct_Id, PlatUser_Id, PlatUserCode, PlatUserName, PlatCorpName, UserProductCode, UserProductAlias, LimitDate, ExtendField04, (CASE WHEN ISNULL(LimitDate, '') = '' THEN 0 ELSE DATEDIFF(DAY, LimitDate, GETDATE()) END) AS LimitDay,PositionType FROM dbo.vBase_ProductPosition AS L WHERE (ProductStorage > 0) AND (LimitDate <= DATEADD(dd,(select CAST(BasicDataValue as int) from Base_BasicDataSet where [BasicDataType]='1'), GETDATE())) -- 配置为动态的 --AND (LimitDate <= DATEADD(dd, 30, GETDATE())) GO /** ** 闲时移库任务明细 视图 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='V_FreeTimeMoveLocationTask') DROP VIEW V_FreeTimeMoveLocationTask GO CREATE VIEW V_FreeTimeMoveLocationTask AS SELECT b.*, main.TaskName as Wms_FreeTimeMoveLocationTask_TaskName, a.MoveRemark,a.MainFreeMoveId FROM dbo.Wms_FreeTimeMoveLocationTaskDetails a LEFT JOIN Wms_FreeTimeMoveLocationTask main on a.MainFreeMoveId=main.ID LEFT OUTER JOIN dbo.Task_Queue b ON a.Task_Id =b.Task_Id Go /** ** 闲时移库任务分页 视图 **/ IF EXISTS(SELECT 1 FROM sys.views WHERE name='V_FreeTimeMoveLocationTaskPage') DROP VIEW V_FreeTimeMoveLocationTaskPage GO CREATE VIEW V_FreeTimeMoveLocationTaskPage AS SELECT main.*, null as CurExcuteTime FROM Wms_FreeTimeMoveLocationTask main Go