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