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


