/* 这里写整个系统的视图脚本 */ --物料锁定库存（物料、跟踪码分组） --查询条件：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