/* 这里写整个系统的视图脚本 */


--物料锁定库存（物料、跟踪码分组）
--查询条件：IsDelete =1 表示是已取消的锁定库存  LockType!=3 不是预配锁定库存【add by liuwq, 2024/06/14】
--查询条件：IsDelete =1 表示是已取消的锁定库存  PDRecordStatus=2 是[已分配]锁定库存【add by shaocx, 2024/07/15】

IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan_lock') 
   DROP VIEW v_wms_stock_quan_lock
GO
CREATE VIEW [dbo].[v_wms_stock_quan_lock]
AS
  
  select MaterialCode,SNCode,ISNULL(sum(Quantity),0) AS LockedQty from wms_record_predetermine_dispense with(nolock) where IsDelete=0 and PDRecordStatus=2   group by MaterialCode,SNCode

GO

--物料预配锁定库存(物料分组)
--查询条件：IsDelete =1 表示是已取消的锁定库存 OrderStatus in(0,1) 表示新建、处理中【add by shaocx, 2024/07/15】

IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_predetermine_lock') 
   DROP VIEW v_wms_stock_predetermine_lock
GO
CREATE VIEW [dbo].[v_wms_stock_predetermine_lock]
AS
  
  select MaterialCode,ISNULL(sum(PredetermineQuantity),0) AS LockedQty from wms_order_movement_details with(nolock) where IsDelete=0 and OrderStatus in(0,1) and PredetermineQuantity>0  group by MaterialCode

GO

-- 按照单号和单行号分组查询物料分配库存
--查询条件：IsDelete =1 表示是已取消的锁定库存  PDRecordStatus=2 是[已分配]锁定库存【add by shaocx, 2024/07/15】

IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_dispense') 
   DROP VIEW v_wms_stock_dispense
GO
CREATE VIEW [dbo].[v_wms_stock_dispense]
AS
 
  select MovementNo,MovementLineNumber,ISNULL(sum(Quantity),0) AS DispenseQty from wms_record_predetermine_dispense with(nolock) where IsDelete=0 and PDRecordStatus=2   group by MovementNo,MovementLineNumber

GO


--未收货完成的ASN单和明细视图-PDA收货用
--查询条件：需求数大于已收货数
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_ware_PurchaseOrderDetailsForPDA') 
   DROP VIEW v_ware_PurchaseOrderDetailsForPDA
GO
CREATE VIEW [dbo].[v_ware_PurchaseOrderDetailsForPDA]
AS
  SELECT 
	 A.Id,
			A.MaterialCode,
			A.MaterialName,
			A.AsnNo,
			A.AsnStatus,
			A.SupplierBatch,
			A.Batch,
			A.Quantity,	A.POQuantity,
			A.GoodsQuantity,
			A.AsnLineNumber,
			A.AsnStatusName,
			A.PoNo,A.IsFreeze,
			A.PoUnit,A.MaterialUnit,B.TranslateRate,R.MaxImumqty --最高库存
			,A.SN_1d,
			A.SN_2d,
			S.StockQty,
			A.IsDelete
		   
	                           FROM dbo.wms_order_asn_details A 
	                           LEFT JOIN dbo.wms_base_material B 
	                           ON A.MaterialCode = B.MaterialCode
                               LEFT JOIN dbo.wms_order_asn E
							   ON A.AsnId = E.Id
							   LEFT JOIN dbo.wms_base_control_rule R
							      ON R.Id = B.ControlRuleId

							   left join (
    select MaterialCode,ISNULL(sum(Quantity),0) AS StockQty from wms_stock_quan as lock group by MaterialCode 
) as S    ON S.MaterialCode = A.MaterialCode
							
	                           WHERE A.IsDelete=0 
							   and (A.Quantity-ISNULL(A.GoodsQuantity,0)) > 0
							   --and S.StockQty is not null
GO



-- 托盘视图
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_base_container') 
   DROP VIEW v_wms_base_container
GO
CREATE VIEW [dbo].[v_wms_base_container]
AS

	
 select c.*,
			p.PlaceCode,p.PlaceName,p.PlaceStatus,area.IsVirtually as IsVirtuallyArea,area.AreaCode,area.AreaName,area.AreaType,area.WarehouseCode,area.WarehouseName
			
     from wms_base_container c

	left join wms_container_place cp on c.ContainerCode=cp.ContainerCode   and cp.IsDelete=0 --容器库位关系
	left join wms_base_place p on p.PlaceCode=cp.PlaceCode and p.IsDelete=0 --库位
	left join wms_base_area area on area.AreaCode=p.AreaCode and area.IsDelete=0 --库区
    where c.IsDelete=0 
GO


 -- 所有状态库存信息-实时库存报表用
 -- 总库存、锁定库存、库位、库区、物料、跟踪码等【add by liuwq, 2024/06/11】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan') 
   DROP VIEW v_wms_stock_quan
GO
CREATE VIEW [dbo].[v_wms_stock_quan]
AS

	
 


select 
 Id,MaterialCode,MaterialName,Quantity
            ,LockedQty, -- 已分配锁定的库存
            ContainerCode,StockStatusName,StockStatus,QCStatus,QCStatusName,
			-- 锁定有关
			LockStatus,LockReason,LockUser,LockTime,
			-- 冻结有关 [Editby shaocx,2024-07-25]
			OperReason,OperUser,OperTime,
			RelationNo,
			POUnit,MaterialUnit,--采购单位、库存单位
			MaterialTypeCode,MaterialTypeName, -- 物料类型名称
	        SupplierCode,SupplierName,Batch,SupplierBatch,SNCode,SourceSNCode,SN_1d,SN_2d,ErpCode,ErpOrderNo,ErpVoucher,ActionTime,ActionRemark,
	        ContainerName,ContainerIsDisabled,Id as ContainerId,
			ContainerTypeCode,ContainerTypeName,-- 容器类型编号
			PlaceCode,PlaceName,PlaceStatus,IsVirtuallyArea,AreaCode,AreaName,AreaType,WarehouseCode,WarehouseName,
		    RecordInsertTime,CreateTime,CreateUserId,CreateUserName,UpdateTime,UpdateUserId,UpdateUserName,IsDelete,
			IsDisabledPlace, -- 禁用库位 
			IsVirtuallyPlace,--虚拟库位
		    IsDisabledArea,-- 禁用库区
			PlaceType,   -- 库位类型
			MaxIndex,
			
			DisabledReason,
			case 
			 when len(DisabledReason)>0 then '不可用'
			 else '可用' end as UsableFlagStr,
			 case 
			 when len(DisabledReason)>0 then 0
			 else 1 end as UsableFlag
			

from

(

 select 
 a.Id,a.MaterialCode,a.MaterialName,a.Quantity
            ,isnull((SELECT  TOP (1) LockedQty
                            FROM       dbo.v_wms_stock_quan_lock AS G
                            WHERE    (G.MaterialCode =a.MaterialCode and G.SNCode =a.SNCode)),0) AS LockedQty, -- 已分配锁定的库存
            a.ContainerCode,a.StockStatusName,a.StockStatus,a.QCStatus,a.QCStatusName,
			-- 冻结有关 [Editby shaocx,2024-07-25]
			a.LockStatus,a.LockReason,a.LockUser,a.LockTime,
			a.OperReason,a.OperUser,a.OperTime,
			a.RelationNo,
			wm.POUnit,wm.MaterialUnit,--采购单位、库存单位
			wm.MaterialTypeCode,wm.MaterialTypeName, -- 物料类型名称
	        a.SupplierCode,a.SupplierName,a.Batch,a.SupplierBatch,a.SNCode,a.SourceSNCode,SN_1d,SN_2d,a.ErpCode,a.ErpOrderNo,a.ErpVoucher,a.ActionTime,a.ActionRemark,
	        c.ContainerName,c.IsDisabled as ContainerIsDisabled,c.Id as ContainerId,
			c.ContainerTypeCode,c.ContainerTypeName,-- 容器类型编号
			p.PlaceCode,p.PlaceName,p.PlaceStatus,area.IsVirtually as IsVirtuallyArea,area.AreaCode,area.AreaName,area.AreaType,area.WarehouseCode,area.WarehouseName,
		    a.RecordInsertTime,a.CreateTime,a.CreateUserId,a.CreateUserName,a.UpdateTime,a.UpdateUserId,a.UpdateUserName,a.IsDelete,
			p.IsDisabled as IsDisabledPlace, -- 禁用库位 
			p.IsVirtually as IsVirtuallyPlace,--虚拟库位
		    area.IsDisabled as IsDisabledArea,-- 禁用库区
			p.PlaceType,   -- 库位类型
			a.MaxIndex,
			
			case 
				when StockStatus <> 3 then '库存状态不是已上架'
				when LockStatus <> 0 then '库存被锁定状态'
				when QCStatus <> 2 then '质检状态不是合格'
				when PlaceStatus <> 1 then '库位属性不是正常'
				when c.IsDisabled <> 0 then '容器被禁用'
				when p.IsVirtually <> 0 then '库位是虚拟'
				when PlaceType NOT IN (2,3) then '库位不是基础库位或组合库位'
				when p.IsDisabled <> 0 then '库位被禁用'
				when area.IsDisabled <> 0 then '库区被禁用'
				else '' end  as DisabledReason
			
     from wms_stock_quan a

	 --TODO
	
	-- 必须是 inner join，因为必须是实际可用库存才能下架
	left  join wms_base_container c on a.ContainerCode=c.ContainerCode and c.IsDelete=0  --容器
	left join wms_container_place cp on a.ContainerCode=cp.ContainerCode   and cp.IsDelete=0 --容器库位关系
	left join wms_base_place p on p.PlaceCode=cp.PlaceCode and p.IsDelete=0 --库位
	left join wms_base_area area on area.AreaCode=p.AreaCode and area.IsDelete=0 --库区
	left join wms_base_material wm on a.MaterialCode=wm.MaterialCode
    where a.IsDelete=0 

	) as t 
GO



 
 --可用库存信息-通用
 --可用库存、总库存、锁定库存、库位、库区、物料、跟踪码等
 --查询条件： 库存状态=3（已上架）且  冻结状态 =0(未冻结) 且库存质检状态=2（合格），是否虚拟容器=否，是否虚拟库位=否，库位是否禁用=否，库区是否禁用=否 【add by liuwq, 2024/06/14】
 -- 库位属性 (基础库位和组合库位)
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan_for_use') 
   DROP VIEW v_wms_stock_quan_for_use
GO
CREATE VIEW [dbo].[v_wms_stock_quan_for_use]
AS

	select ISNULL(Quantity-LockedQty,0) as AvailableQty,* from v_wms_stock_quan where StockStatus=3 and LockStatus = 0 and QCStatus=2 and PlaceStatus=1 
	and ContainerIsDisabled=0 and IsVirtuallyPlace=0 and  IsDisabledPlace=0 and IsDisabledArea=0
	and PlaceType IN (2,3)  -- 基础库位和组合库位 才可以是 可用库存

 GO





	




--物料汇总可用库存,这个视图建议不要单独使用了，建议使用 视图 v_wms_stock_quan_group
--查询条件：IsDelete =1 表示是已取消的锁定库存 【add by liuwq, 2024/06/14】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan_use_group') 
   DROP VIEW v_wms_stock_quan_use_group
GO
CREATE VIEW [dbo].[v_wms_stock_quan_use_group]
AS

select t_use.MaterialCode,-- 物料
       ma.MaterialName,-- 物料名称
	   ma.MaterialUnit, --库存单位
	   ma.POUnit,--采购单位
ISNULL( t_use.Quantity,0)  as AllQuantity,--实物总库存
ISNULL(ISNULL(t_use.Quantity,0)-ISNULL( t_predetermine.LockedQty,0) ,0) as RealAvailableQty,--最终可用库存(实物总库存-预配锁定数)
ISNULL( t_use.AvailableQty,0)  as NotPredetermineLockedQtyAvailableQty,--未扣减掉预配锁定数的可用库存
ISNULL( t_predetermine.LockedQty,0)  as  PredetermineLockedQty--预配锁定数
,ISNULL(  t_lock.LockedQty,0) as SNLockedQty-- 跟踪码实物物料锁定数
from
(select MaterialCode, SUM(Quantity) as Quantity , SUM(AvailableQty) as AvailableQty from v_wms_stock_quan_for_use  group by MaterialCode ) AS t_use
LEFT JOIN wms_base_material ma on ma.MaterialCode= t_use.MaterialCode

left join v_wms_stock_predetermine_lock as t_predetermine  on t_use.MaterialCode=t_predetermine.MaterialCode -- 已预配数
left join (
    select MaterialCode,ISNULL(sum(LockedQty),0) AS LockedQty from v_wms_stock_quan_lock as lock group by MaterialCode 
) as t_lock  on t_use.MaterialCode=t_lock.MaterialCode -- 锁定库存（分配 下发 其他）
 
GO


	
--物料汇总库存
-- 计算公式： 总库存=预配数（这其中包含分配数）+其他不可用库存+实际可用库存
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan_group') 
   DROP VIEW v_wms_stock_quan_group
GO
CREATE VIEW [dbo].[v_wms_stock_quan_group]
AS

select 
   tt.MaterialCode,tt.MaterialName,tt.MaterialUnit,
ISNULL(tt.Quantity,0) as Quantity, -- 全部库存
ISNULL(tt.PredetermineLockedQty,0) as PredetermineLockedQty, -- 预配数
ISNULL(tt.TheoryUseQty,0) as TheoryUseQty, -- 理论可用库存（包含了预配数）
(ISNULL(tt.UseQty,0)) as UseQty, -- 实际可用库存
(ISNULL(tt.Quantity,0)-ISNULL(tt.PredetermineLockedQty,0)-ISNULL(tt.UseQty,0)) as OtherLockQty -- 其他不可用库存
 
 from 
(

select 

allStockQun.MaterialCode,ma.MaterialName,ma.MaterialUnit,
ISNULL(allStockQun.Quantity,0) as Quantity, -- 全部库存
ISNULL(ug.PredetermineLockedQty,0) as PredetermineLockedQty, -- 预配数
ISNULL(ug.AllQuantity,0) as TheoryUseQty, -- 理论可用库存（包含了预配数）
case 
	when (ISNULL(ug.AllQuantity,0)-ISNULL(ug.PredetermineLockedQty,0)) <0 then 0
    else (ISNULL(ug.AllQuantity,0)-ISNULL(ug.PredetermineLockedQty,0))
end  as UseQty -- 实际可用库存(加case的原因是可能已分配的数据又被其他人冻结了，比如托盘出库任务啊)

 FROM
( select sum(ISNULL(Quantity,0)) as Quantity,MaterialCode from v_wms_stock_quan  group by MaterialCode ) allStockQun
LEFT JOIN wms_base_material ma on ma.MaterialCode= allStockQun.MaterialCode
LEFT JOIN v_wms_stock_quan_use_group ug on allStockQun.MaterialCode=ug.MaterialCode

) as tt
 
GO


--物料库区汇总库存
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_stock_quan_area_group') 
   DROP VIEW v_wms_stock_quan_area_group
GO
CREATE VIEW [dbo].[v_wms_stock_quan_area_group]
AS

select 

allStockQun.MaterialCode,ma.MaterialName,ma.MaterialUnit,
allStockQun.AreaCode,wba.AreaName,
ISNULL(allStockQun.Quantity,0) as Quantity, -- 全部库存
ISNULL(ug.AvailableQty,0) as UseQty, -- 实际可用库存
(ISNULL(allStockQun.Quantity,0)-ISNULL(ug.AvailableQty,0)) as LockQty -- 不可用库存

 FROM
( select sum(ISNULL(Quantity,0)) as Quantity,MaterialCode,AreaCode from v_wms_stock_quan  group by MaterialCode,AreaCode ) allStockQun
LEFT JOIN wms_base_material ma on ma.MaterialCode= allStockQun.MaterialCode
LEFT JOIN   wms_base_area wba  on wba.AreaCode=allStockQun.AreaCode
LEFT JOIN (
	 select sum(AvailableQty) as AvailableQty,MaterialCode,AreaCode from v_wms_stock_quan_for_use group by MaterialCode,AreaCode
) ug on allStockQun.MaterialCode=ug.MaterialCode and allStockQun.AreaCode=ug.AreaCode
 
GO



--移动单明细（含可用库存）-分配用 【add by liuwq, 2024/06/14】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_order_movement_details_for_dispense') 
   DROP VIEW v_wms_order_movement_details_for_dispense
GO
CREATE VIEW [dbo].[v_wms_order_movement_details_for_dispense]
AS
     SELECT 
	        A.Id,
			A.SNCode,
			A.ErpCode,
			A.SourcePlaceCode,
			A.SourcePlaceName,
			A.ToPlaceCode,
			A.ToPlaceName,
			A.MovementNo,
			A.MovementId,
		    A.OrderStatus,
			A.OrderStatusName,
			A.LineNumber,
			A.RelationNoLineNumber,
			A.RelationNo,
			A.MaterialCode,
			A.MaterialName,
			A.Unit,
			A.CustCode,
			A.CustName,
		    A.Quantity,
			A.PickQuantity,
			--A.DispenseQuantity,
			ISNULL(mydispense.DispenseQty,0) AS DispenseQuantity,  -- 已分配数量
			A.PredetermineQuantity,
			B.AvailableQty,--实物跟踪码可用库存
	        A.Batch,
			A.SupplierBatch,
			A.ContainerCode,
			A.ContainerName
	   FROM dbo.wms_order_movement_details A 
	   LEFT JOIN v_wms_stock_dispense AS mydispense ON A.MovementNo=mydispense.MovementNo AND A.LineNumber=mydispense.MovementLineNumber
       left join   dbo.v_wms_stock_quan_for_use AS B on A.MaterialCode=b.MaterialCode  and A.SNCode=B.SNCode 
       WHERE A.IsDelete=0 
GO


--移动单明细（含可用库存）-预配用 【add by liuwq, 2024/06/28】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_order_movement_details_for_predetermine') 
   DROP VIEW v_wms_order_movement_details_for_predetermine
GO
CREATE VIEW [dbo].[v_wms_order_movement_details_for_predetermine]
AS
     SELECT 
	        A.Id,
			A.SNCode,
			A.ErpCode,
			A.SourcePlaceCode,
			A.SourcePlaceName,
			A.ToPlaceCode,
			A.ToPlaceName,
			A.MovementNo,
			A.MovementId,
		    A.OrderStatus,
			A.OrderStatusName,
			A.LineNumber,
			A.RelationNoLineNumber,
			A.RelationNo,
			A.MaterialCode,
			A.MaterialName,
			A.Unit,
			A.CustCode,
			A.CustName,
		    A.Quantity,
			A.PickQuantity,
			--A.DispenseQuantity,
			ISNULL(mydispense.DispenseQty,0) AS DispenseQuantity,  -- 已分配数量
			A.PredetermineQuantity,
		   (B.UseQty) as RealAvailableQty,--汇总踪码后同一种物料最终可用库存(已扣减预配锁定数)
	        A.Batch,
			A.SupplierBatch,
			A.ContainerCode,
			A.ContainerName
	   FROM dbo.wms_order_movement_details A 
	   LEFT JOIN v_wms_stock_dispense AS mydispense ON A.MovementNo=mydispense.MovementNo AND A.LineNumber=mydispense.MovementLineNumber
       left join   dbo.v_wms_stock_quan_group AS B on A.MaterialCode=b.MaterialCode  
       WHERE A.IsDelete=0 
GO




---库龄报表
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_ware_age_warm') 
   DROP VIEW v_ware_age_warm
GO
CREATE VIEW [dbo].v_ware_age_warm
AS

SELECT 
aa.SNCode,
m.MaterialCode
,m.MaterialName
, aa.ContainerCode
,aa.CreateTime
,aa.RecordInsertTime
,bb.PlaceCode
,crd.MaxStorageAge  as QualityAssurance -- 预警库龄 = 最大库龄  = 自己设置 规则 --dbo.ware_material.QualityAssurance,
,(DATEDIFF(DD, aa.RecordInsertTime, GETDATE())  +1)  AS WareAge --  库龄 = 现在时间 - 入库时间 + 算当天1天
,(DATEDIFF(DD, aa.RecordInsertTime, GETDATE())- crd.MaxStorageAge + 1) as OverDueDays -- 超期天数 = 库龄 - 最大库龄
				--(DATEDIFF(DD, aa.RecordInsertTime, GETDATE())-ware_material.QualityAssurance) as OverDueDays -- 超期天数
,area.AreaName
FROM         wms_stock_quan AS aa 
INNER JOIN   wms_base_material AS m  ON aa.MaterialCode = m.MaterialCode 
INNER JOIN   wms_base_control_rule AS crd  ON m.MaterialCode = crd.MaterialCode 
INNER JOIN
                    (SELECT   
					Id,PlaceCode,ContainerCode
					--Id, WareStorehouseCode, WareRegionCode, WareLocationCode, WareContainerCode 
                     -- ,WareContainerName, IsEmptyContainer, Status, TenantId, CreatedTime, UpdatedTime, CreatedUserId
                    --  ,  CreatedUserName, UpdatedUserName, UpdatedUserId, IsDeleted, Remarks
                     FROM      wms_container_place
                     WHERE   (IsDelete = 0)) AS bb ON aa.ContainerCode = bb.ContainerCode
					 AND crd.MaxStorageAge IS NOT  NULL --保质期
					AND crd.MaxStorageAge <> 0
					AND DATEDIFF(DD, aa.RecordInsertTime, GETDATE())> crd.MaxStorageAge
				Inner join dbo.wms_base_place AS place on place.PlaceCode = bb.PlaceCode
				inner join dbo.wms_base_area as area on area.AreaCode = place.AreaCode
GO





-- 库位视图 【add by liuwq, 2024/06/21】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_place_container_info') 
   DROP VIEW v_wms_place_container_info
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[v_wms_place_container_info]
AS
SELECT  
ar.AreaCode,
                   b.ContainerCode as WareContainerCode,
                   a.PlaceCode AS WareLocationCode, a.PlaceName AS WareLocationName, a.LaneNo as Lane, a.RowNo as Row, a.ColumnNo as [Column], a.LayerNo as Layer, 
                   a.Height as [High],a.Width,a.[Length]as Long, a.PlaceStatus as [Status], a.PlaceTypeName as LocTypeName,a.PlaceType as LocTypeId, 
				   e.TypeName as ContainerTypeName,e.Id as ContainerTypeId,e.TypeCode as ContainerTypeCode,
				   CASE WHEN B.PlaceCode IS NULL THEN 0 
				        WHEN B.PlaceCode IS NOT NULL AND d.Id is NULL THEN 1 
						ELSE 2 END AS InventoryType, 
						 a.IsDelete,ma.MaterialTypeCode,ma.MaterialCode
FROM      dbo.wms_base_place AS a LEFT OUTER JOIN
                   dbo.wms_container_place AS b ON a.PlaceCode = b.PlaceCode AND b.IsDelete <> 1 LEFT OUTER JOIN
                  
				   dbo.wms_stock_quan AS d on b.ContainerCode = d.ContainerCode and d.IsDelete = 0
				   left outer join dbo.wms_base_material ma on ma.MaterialCode=d.MaterialCode
					 LEFT OUTER JOIN dbo.wms_base_container as f on b.ContainerCode=f.ContainerCode
					  LEFT OUTER JOIN dbo.wms_base_area as ar on ar.AreaCode = a.AreaCode  -- ly0722 添加库区查询
					 LEFT OUTER JOIN dbo.wms_base_container_type as e on f.ContainerTypeId= e.Id
				   where (a.IsVirtually = 0 or  a.IsVirtually is null) and a.IsDelete = 0
GO


 -- 库位视图 查看物料详情 【add by liuying, 2024/08/1】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_location_view_details') 
   DROP VIEW v_wms_location_view_details
GO
CREATE VIEW [dbo].[v_wms_location_view_details]
AS
 select c.ContainerCode, a.Id,a.MaterialCode,a.MaterialName,a.Quantity,
            a.LockStatus,a.LockReason,a.LockUser,a.LockTime,
			a.OperReason,a.OperUser,a.OperTime,
			a.RelationNo,
			wm.POUnit,wm.MaterialUnit,--采购单位、库存单位
			wm.MaterialTypeCode,wm.MaterialTypeName, -- 物料类型名称
	        a.SupplierCode,a.SupplierName,a.Batch,a.SupplierBatch,a.SNCode,a.SourceSNCode,SN_1d,SN_2d,a.ErpCode,
			a.ErpOrderNo,a.ErpVoucher,a.ActionTime,a.ActionRemark,
	        c.ContainerName,c.IsDisabled as ContainerIsDisabled,c.Id as ContainerId,
			c.ContainerTypeCode,c.ContainerTypeName,-- 容器类型编号
			p.PlaceCode,p.PlaceName,p.PlaceStatus,area.IsVirtually as IsVirtuallyArea,area.AreaCode,
			area.AreaName,area.AreaType,area.WarehouseCode,area.WarehouseName,
		    a.RecordInsertTime,a.CreateTime,a.CreateUserId,a.CreateUserName,a.UpdateTime,a.UpdateUserId,a.UpdateUserName,a.IsDelete,
			p.PlaceType,   -- 库位类型
			a.MaxIndex
     from   wms_container_place cp
	left join  wms_stock_quan a on a.ContainerCode=cp.ContainerCode   and cp.IsDelete=0 --容器库位关系
	left join wms_base_container c on a.ContainerCode=c.ContainerCode and c.IsDelete=0  --容器
	left join wms_base_place p on p.PlaceCode=cp.PlaceCode and p.IsDelete=0 --库位
	left join wms_base_area area on area.AreaCode=p.AreaCode and area.IsDelete=0 --库区
	left join wms_base_material wm on a.MaterialCode=wm.MaterialCode
    where a.IsDelete=0 
GO



---ABC动态看板
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_stock_boardabc') 
   DROP VIEW v_stock_boardabc
GO
CREATE VIEW [dbo].v_stock_boardabc
AS


SELECT 
m.ABCClass as ABCClass
,m.ABCClassName
,m.Id 
,m.MaterialCode
,m.MaterialName
,aa.Quantity,
aa.CreateTime,aa.UpdateTime,aa.CreateUserId,aa.CreateUserName,aa.UpdateUserId,aa.UpdateUserName,aa.IsDelete,aa.RecordInsertTime
--, aa.ContainerCode
--,aa.CreateTime
--,aa.RecordInsertTime
--,bb.PlaceCode

FROM         wms_stock_quan AS aa 
INNER JOIN   wms_base_material AS m  ON aa.MaterialCode = m.MaterialCode 
INNER JOIN
                    (SELECT   
					Id,PlaceCode,ContainerCode
					--Id, WareStorehouseCode, WareRegionCode, WareLocationCode, WareContainerCode 
                     -- ,WareContainerName, IsEmptyContainer, Status, TenantId, CreatedTime, UpdatedTime, CreatedUserId
                    --  ,  CreatedUserName, UpdatedUserName, UpdatedUserId, IsDeleted, Remarks
                     FROM      wms_container_place
                     WHERE   (IsDelete = 0)) AS bb ON aa.ContainerCode = bb.ContainerCode
					AND m.ABCClass IS NOT  NULL 
					--AND crd.MaxImumqty <> 0
					--AND DATEDIFF(DD, aa.RecordInsertTime, GETDATE())>= crd.MaxStorageAge
					--group by m.ABCClass,m.MaterialCode,m.MaterialName,m.ABCClassName,m.Id 


GO



---移动单明细视图 【Editby shaocx,2024-07-15】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='V_WmsOrderMovementDetails') 
   DROP VIEW [V_WmsOrderMovementDetails]
GO
CREATE VIEW [dbo].[V_WmsOrderMovementDetails]
AS


SELECT 
A.*,movement.OrderType,
ISNULL(mydispense.DispenseQty,0) AS DispenseQuantity  -- 已分配数量

FROM         wms_order_movement_details AS A 
LEFT JOIN wms_order_movement AS movement ON A.MovementNo=movement.OrderNo
LEFT JOIN v_wms_stock_dispense AS mydispense ON A.MovementNo=mydispense.MovementNo AND A.LineNumber=mydispense.MovementLineNumber



GO


--- 库存预警 视图 【Editby zongzhib,2024-07-19】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='V_WmsStockWarning') 
   DROP VIEW [V_WmsStockWarning]
GO
CREATE VIEW [dbo].[V_WmsStockWarning]
AS


   select
  isnull(a.UseQty,0) as RealAvailableQty, --- 当前可用库存
  crule.SafeImumqty,
    crule.MinImumqty,
	crule.MaxImumqty,
	a.MaterialCode,
	a.MaterialName,
	a.MaterialUnit,
	m.materialTypeName
   from 
  v_wms_stock_quan_group a with(nolock)
   
  left join wms_base_material m  with(nolock) on a.MaterialCode=m.MaterialCode
  left join wms_base_control_rule crule with(nolock) on crule.MaterialCode=m.MaterialCode



GO



-- 空库位视图 【add by shaocx, 2024/06/21】
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_empty_place') 
   DROP VIEW v_empty_place
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[v_empty_place]
AS
SELECT  
  a.*
FROM      dbo.wms_base_place AS a LEFT OUTER JOIN
                   dbo.wms_container_place AS b ON a.PlaceCode = b.PlaceCode AND b.IsDelete <> 1 
				   
				   where (a.IsVirtually = 0 or  a.IsVirtually is null) and a.IsDelete = 0
				   and b.id is null 
GO




---下架策略范围详情视图（范围+下架策略主信息） 【Editby liuwq,2024-08-05】
-- 查询条件：IsDelete=0 and IsDisabled=0
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_config_unshelve_strategy_range') 
   DROP VIEW [v_wms_config_unshelve_strategy_range]
GO
CREATE VIEW [dbo].[v_wms_config_unshelve_strategy_range]
AS


SELECT 
       Strategy.StrategyCode 
      ,Strategy.[StrategyName] 
      ,Strategy.[StartTime]
      ,Strategy.[EndTime]
       ,StrategyRange.MaterialCode
	   ,StrategyRange.MaterialName
	   ,StrategyRange.MaterialUnit

FROM         wms_config_unshelve_strategy_range AS  StrategyRange 
LEFT JOIN wms_config_unshelve_strategy AS Strategy ON StrategyRange.StrategyCode=Strategy.StrategyCode 
where  Strategy.IsDelete=0 and Strategy.IsDisabled=0 and StrategyRange.IsDelete=0
GO


---盘点单详情视图（盘点单+盘点单明细） 【Editby liuwq,2024-08-12】
-- 查询条件：IsDelete=0 
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_wms_inventory_check_order_details_order') 
   DROP VIEW [v_wms_inventory_check_order_details_order]
GO
CREATE VIEW [dbo].[v_wms_inventory_check_order_details_order]
AS


SELECT 
	 d.OrderId,
	 o.OrderNo,
	 o.CheckMode,
	 o.CheckModeName,
	 o.CheckStage,
	 o.CheckStageName,
	 d.AreaCode,
	 d.AreaName,
	 d.PlaceCode,
	 d.PlaceName,
	 o.PlanStartDate,
	 o.PlanEndDate,
	 o.StartCheckTime,
	 o.EndCheckTime,
	 o.Remarks,
	 o.IsAudit,

	 d.SNCode,
	 d.Batch,
	 d.MaterialCode,
	 d.MaterialName,
	 d.ContainerCode,
	 d.Quantity,
	 d.CheckQuantity,
	 d.CheckStatus,
	 d.CheckStatusName,
	 d.CheckResult,
	 d.CheckResultName,
	 d.CheckCount,
	 d.CheckOperatorClassify,
	 d.CheckOperatorClassifyName


FROM        wms_inventory_check_order_details AS  d 
LEFT JOIN wms_inventory_check_order AS o ON d.OrderId=o.Id 
where   d.IsDelete=0 and o.IsDelete=0
GO
