/**
|
** 库存明细查询 视图（注意：这是视图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
|
