USE [SXB_SCM]
GO
/****** Object: StoredProcedure [dbo].[sp_InOutBill_AuditUnified] Script Date: 2025/8/31 23:59:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InOutBill_AuditUnified]
@BillId INT, -- 入/出库单主表 Id：WMS_InOutPutBill.Id
@Action NVARCHAR(10), -- 'Audit' / 'UnAudit'
@User NVARCHAR(50) = NULL, -- 操作人
@Remark NVARCHAR(500) = NULL -- 备注
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @Action NOT IN (N'Audit', N'UnAudit')
BEGIN
RAISERROR(N'@Action 仅支持 ''Audit'' 或 ''UnAudit''。',16,1);
RETURN;
END
BEGIN TRY
BEGIN TRAN;
DECLARE
@BillCode NVARCHAR(50),
@BillState INT,
@InorOutType INT, -- 1=入库, 2=出库
@MainWHId INT,
@MainWHName NVARCHAR(150),
@TradeId INT,
@TradeName NVARCHAR(200),
@BeforeState INT,
@AfterState INT,
@ErrMsg NVARCHAR(1000);
-- 锁定主表记录，避免并发
SELECT
@BillCode = BillCode,
@BillState = BillState,
@InorOutType = InorOutType,
@MainWHId = WorkHouseId,
@MainWHName = WorkHouseName,
@TradeId = TradeId,
@TradeName = TradeName
FROM WMS_InOutPutBill WITH (UPDLOCK, HOLDLOCK)
WHERE Id = @BillId;
IF @BillCode IS NULL
BEGIN
SET @ErrMsg = N'单据不存在（Id=' + CAST(@BillId AS NVARCHAR(20)) + N'）！';
RAISERROR(@ErrMsg,16,1);
END
SET @BeforeState = @BillState;
-- 状态校验
IF @Action = N'Audit' AND @BillState <> 0
BEGIN
RAISERROR(N'单据不是待审核状态 (0)，不能审核！',16,1);
END
IF @Action = N'UnAudit' AND @BillState <> 1
BEGIN
RAISERROR(N'单据不是已审核状态 (1)，不能反审核！',16,1);
END
-- 明细是否存在
IF NOT EXISTS(SELECT 1 FROM WMS_InOutPutBillDetail WITH (NOLOCK) WHERE BillCode=@BillCode)
BEGIN
SET @ErrMsg = N'单据[' + @BillCode + N'] 无明细，无法处理！';
RAISERROR(@ErrMsg,16,1);
END
----------------------------------------------------------------------
-- 遍历明细：统一用 InputNum 作为数量字段（入/出库均如此）
-- 明细表没有仓库字段，统一使用主表仓库 @MainWHId/@MainWHName
----------------------------------------------------------------------
DECLARE
@D_MaterialId INT,
@D_Qty DECIMAL(18,6),
@BaseMaterialId INT,
@Rate DECIMAL(18,6),
@BaseQty DECIMAL(18,6),
@InvBefore DECIMAL(18,6),
@InvAfter DECIMAL(18,6),
@UseBefore DECIMAL(18,6),
@InvMatIdStr NVARCHAR(50); -- 库存主表中的 MaterialId（NVARCHAR）
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT
d.MaterialId,
CAST(ISNULL(d.InputNum,0) AS DECIMAL(18,6)) AS Qty
FROM WMS_InOutPutBillDetail d WITH (NOLOCK)
WHERE d.BillCode = @BillCode;
OPEN cur;
FETCH NEXT FROM cur INTO @D_MaterialId, @D_Qty;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 默认无换算：自身即最小单位
SET @BaseMaterialId = @D_MaterialId;
SET @Rate = 1;
-- 最小单位换算：MaterialId -> FatherMaterialId * FatherMaterialNum
SELECT TOP (1)
@BaseMaterialId = ISNULL(FatherMaterialId, @D_MaterialId),
@Rate = ISNULL(FatherMaterialNum, 1)
FROM JXC_MaterialFather WITH (NOLOCK)
WHERE MaterialId = @D_MaterialId;
SET @BaseQty = ISNULL(@D_Qty,0) * ISNULL(@Rate,1);
SET @InvMatIdStr = CAST(@BaseMaterialId AS NVARCHAR(50)); -- 适配库存主表的 NVARCHAR(50)
------------------------------------------------------------------
-- 读取仓库库存期初（并加锁确保一致性）-> 注意 MaterialId 为 NVARCHAR
------------------------------------------------------------------
SELECT @InvBefore = CAST(ISNULL(InventoryNum,0) AS DECIMAL(18,6))
FROM WMS_MaterialInventory WITH (UPDLOCK, HOLDLOCK)
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
IF @Action = N'Audit'
BEGIN
IF @InorOutType = 1
BEGIN
-- 入库审核：加库存，如无仓库记录则创建
IF @InvBefore IS NULL
BEGIN
DECLARE @MatCode NVARCHAR(200), @MatName NVARCHAR(200), @MatModel NVARCHAR(200), @MatUnit NVARCHAR(10);
SELECT @MatCode=MaterialCode,@MatName=MaterialName,@MatModel=MaterialModel,@MatUnit=MaterialUnit
FROM JXC_Material WITH (NOLOCK) WHERE Id=@BaseMaterialId;
INSERT INTO WMS_MaterialInventory(MaterialId,MaterialCode,MaterialName,MaterialModel,MaterialUnit,
InputNum,OutputNum,InventoryNum,
WorkHouseId,WorkHouseName,CreateBy,CreateOn)
VALUES(@InvMatIdStr,@MatCode,@MatName,@MatModel,@MatUnit,
0,0,0,@MainWHId,@MainWHName,@User,GETDATE());
SET @InvBefore = 0;
END
-- 商品 UseNum +
UPDATE JXC_Material
SET UseNum = ISNULL(UseNum,0) + @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BaseMaterialId;
-- 仓库库存（NVARCHAR MaterialId）：入库累计 + ，现存量 +
UPDATE WMS_MaterialInventory
SET InputNum = ISNULL(InputNum,0) + @BaseQty,
InventoryNum= ISNULL(InventoryNum,0) + @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
SELECT @InvAfter = CAST(ISNULL(InventoryNum,0) AS DECIMAL(18,6))
FROM WMS_MaterialInventory
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
-- 明细流水：入库（BillType=1, I/O Type=1）
INSERT INTO WMS_MaterialInventoryDetail
(
BillCode,BillOn,BillType,InputOutPutType,TradeId,TradeName,
MaterialId,WorkHouseId,WorkHouseName,
MaterialCostNum,MaterialCostPrice,MaterialCostAmount,
CreateOn,CreateId,CreateBy,Remark,
InputNum,OutputNum,InventoryNum
)
VALUES
(
@BillCode,GETDATE(),1,1,@TradeId,@TradeName,
@BaseMaterialId,@MainWHId,@MainWHName,
@BaseQty,NULL,NULL,
GETDATE(),NULL,@User,ISNULL(@Remark,N'入库审核'),
@BaseQty,0,@InvAfter
);
END
ELSE
BEGIN
-- 出库审核：减库存，必须有仓库记录且库存充足（NVARCHAR MaterialId）
IF @InvBefore IS NULL
BEGIN
SET @ErrMsg = N'仓库['+CAST(@MainWHId AS NVARCHAR(20))+N'] 物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 无库存记录，无法出库审核！';
RAISERROR(@ErrMsg,16,1);
END
IF @InvBefore < @BaseQty
BEGIN
SET @ErrMsg = N'仓库['+CAST(@MainWHId AS NVARCHAR(20))+N'] 物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 库存不足（现存='
+ CAST(@InvBefore AS NVARCHAR(50)) + N'，出库=' + CAST(@BaseQty AS NVARCHAR(50)) + N'）！';
RAISERROR(@ErrMsg,16,1);
END
-- 商品 UseNum -
SELECT @UseBefore = CAST(ISNULL(UseNum,0) AS DECIMAL(18,6))
FROM JXC_Material WITH (UPDLOCK, HOLDLOCK) WHERE Id=@BaseMaterialId;
IF @UseBefore < @BaseQty
BEGIN
SET @ErrMsg = N'物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 商品库存不足（UseNum='
+ CAST(@UseBefore AS NVARCHAR(50)) + N'，出库=' + CAST(@BaseQty AS NVARCHAR(50)) + N'）！';
RAISERROR(@ErrMsg,16,1);
END
UPDATE JXC_Material
SET UseNum = ISNULL(UseNum,0) - @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BaseMaterialId;
-- 仓库库存（NVARCHAR MaterialId）：出库累计 + ，现存量 -
UPDATE WMS_MaterialInventory
SET OutputNum = ISNULL(OutputNum,0) + @BaseQty,
InventoryNum= ISNULL(InventoryNum,0) - @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
SELECT @InvAfter = CAST(ISNULL(InventoryNum,0) AS DECIMAL(18,6))
FROM WMS_MaterialInventory
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
-- 明细流水：出库（BillType=2, I/O Type=2）
INSERT INTO WMS_MaterialInventoryDetail
(
BillCode,BillOn,BillType,InputOutPutType,TradeId,TradeName,
MaterialId,WorkHouseId,WorkHouseName,
MaterialCostNum,MaterialCostPrice,MaterialCostAmount,
CreateOn,CreateId,CreateBy,Remark,
InputNum,OutputNum,InventoryNum
)
VALUES
(
@BillCode,GETDATE(),2,2,@TradeId,@TradeName,
@BaseMaterialId,@MainWHId,@MainWHName,
@BaseQty,NULL,NULL,
GETDATE(),NULL,@User,ISNULL(@Remark,N'出库审核'),
0,@BaseQty,@InvAfter
);
END
END
ELSE -- @Action = 'UnAudit'
BEGIN
IF @InorOutType = 1
BEGIN
-- 入库反审核：减回库存（必须存在且足够）
IF @InvBefore IS NULL
BEGIN
SET @ErrMsg = N'仓库['+CAST(@MainWHId AS NVARCHAR(20))+N'] 物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 无库存记录，无法入库反审核！';
RAISERROR(@ErrMsg,16,1);
END
IF @InvBefore < @BaseQty
BEGIN
SET @ErrMsg = N'仓库['+CAST(@MainWHId AS NVARCHAR(20))+N'] 物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 库存不足（现存='
+ CAST(@InvBefore AS NVARCHAR(50)) + N'，回退=' + CAST(@BaseQty AS NVARCHAR(50)) + N'）！';
RAISERROR(@ErrMsg,16,1);
END
SELECT @UseBefore = CAST(ISNULL(UseNum,0) AS DECIMAL(18,6))
FROM JXC_Material WITH (UPDLOCK, HOLDLOCK) WHERE Id=@BaseMaterialId;
IF @UseBefore < @BaseQty
BEGIN
SET @ErrMsg = N'物料['+CAST(@BaseMaterialId AS NVARCHAR(20))+N'] 商品库存不足（UseNum='
+ CAST(@UseBefore AS NVARCHAR(50)) + N'，回退=' + CAST(@BaseQty AS NVARCHAR(50)) + N'）！';
RAISERROR(@ErrMsg,16,1);
END
-- 商品 UseNum -
UPDATE JXC_Material
SET UseNum = ISNULL(UseNum,0) - @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BaseMaterialId;
-- 仓库库存（NVARCHAR MaterialId）：入库累计 - ，现存量 -
UPDATE WMS_MaterialInventory
SET InputNum = ISNULL(InputNum,0) - @BaseQty,
InventoryNum= ISNULL(InventoryNum,0) - @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
SELECT @InvAfter = CAST(ISNULL(InventoryNum,0) AS DECIMAL(18,6))
FROM WMS_MaterialInventory
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
-- 明细流水：入库反审核（BillType=1, I/O Type=4）
INSERT INTO WMS_MaterialInventoryDetail
(
BillCode,BillOn,BillType,InputOutPutType,TradeId,TradeName,
MaterialId,WorkHouseId,WorkHouseName,
MaterialCostNum,MaterialCostPrice,MaterialCostAmount,
CreateOn,CreateId,CreateBy,Remark,
InputNum,OutputNum,InventoryNum
)
VALUES
(
@BillCode,GETDATE(),1,4,@TradeId,@TradeName,
@BaseMaterialId,@MainWHId,@MainWHName,
@BaseQty,NULL,NULL,
GETDATE(),NULL,@User,ISNULL(@Remark,N'入库反审核'),
0,@BaseQty,@InvAfter
);
END
ELSE
BEGIN
-- 出库反审核：加回库存（若无仓库记录则创建）
IF @InvBefore IS NULL
BEGIN
DECLARE @MatCode2 NVARCHAR(200), @MatName2 NVARCHAR(200), @MatModel2 NVARCHAR(200), @MatUnit2 NVARCHAR(10);
SELECT @MatCode2=MaterialCode,@MatName2=MaterialName,@MatModel2=MaterialModel,@MatUnit2=MaterialUnit
FROM JXC_Material WITH (NOLOCK) WHERE Id=@BaseMaterialId;
INSERT INTO WMS_MaterialInventory(MaterialId,MaterialCode,MaterialName,MaterialModel,MaterialUnit,
InputNum,OutputNum,InventoryNum,
WorkHouseId,WorkHouseName,CreateBy,CreateOn)
VALUES(@InvMatIdStr,@MatCode2,@MatName2,@MatModel2,@MatUnit2,
0,0,0,@MainWHId,@MainWHName,@User,GETDATE());
SET @InvBefore = 0;
END
-- 商品 UseNum +
UPDATE JXC_Material
SET UseNum = ISNULL(UseNum,0) + @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BaseMaterialId;
-- 仓库库存（NVARCHAR MaterialId）：出库累计 - ，现存量 +
UPDATE WMS_MaterialInventory
SET OutputNum = ISNULL(OutputNum,0) - @BaseQty,
InventoryNum= ISNULL(InventoryNum,0) + @BaseQty,
UpdateBy=@User, UpdateOn=GETDATE()
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
SELECT @InvAfter = CAST(ISNULL(InventoryNum,0) AS DECIMAL(18,6))
FROM WMS_MaterialInventory
WHERE MaterialId=@InvMatIdStr AND WorkHouseId=@MainWHId;
-- 明细流水：出库反审核（BillType=2, I/O Type=3）
INSERT INTO WMS_MaterialInventoryDetail
(
BillCode,BillOn,BillType,InputOutPutType,TradeId,TradeName,
MaterialId,WorkHouseId,WorkHouseName,
MaterialCostNum,MaterialCostPrice,MaterialCostAmount,
CreateOn,CreateId,CreateBy,Remark,
InputNum,OutputNum,InventoryNum
)
VALUES
(
@BillCode,GETDATE(),2,3,@TradeId,@TradeName,
@BaseMaterialId,@MainWHId,@MainWHName,
@BaseQty,NULL,NULL,
GETDATE(),NULL,@User,ISNULL(@Remark,N'出库反审核'),
@BaseQty,0,@InvAfter
);
END
END
FETCH NEXT FROM cur INTO @D_MaterialId, @D_Qty;
END
CLOSE cur;
DEALLOCATE cur;
----------------------------------------------------------------------
-- 更新主表状态
----------------------------------------------------------------------
IF @Action = N'Audit'
UPDATE WMS_InOutPutBill
SET BillState=1, UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BillId;
ELSE
UPDATE WMS_InOutPutBill
SET BillState=0, UpdateBy=@User, UpdateOn=GETDATE()
WHERE Id=@BillId;
SET @AfterState = CASE WHEN @Action=N'Audit' THEN 1 ELSE 0 END;
----------------------------------------------------------------------
-- 可选：写统一审计日志（若存在 Audit_Log）
----------------------------------------------------------------------
IF OBJECT_ID('dbo.Audit_Log','U') IS NOT NULL
BEGIN
INSERT INTO dbo.Audit_Log
(
DocType,DocId,DocCode,Action,
BeforeState,AfterState,Result,Remark,CreateBy,CreateOn
)
VALUES
(
CASE WHEN @InorOutType=1 THEN N'InPut' ELSE N'OutPut' END,
@BillId,@BillCode,
CASE WHEN @InorOutType=1 AND @Action=N'Audit' THEN N'入库审核'
WHEN @InorOutType=1 AND @Action=N'UnAudit' THEN N'入库反审核'
WHEN @InorOutType=2 AND @Action=N'Audit' THEN N'出库审核'
ELSE N'出库反审核' END,
@BeforeState,@AfterState,N'成功',@Remark,@User,GETDATE()
);
END
COMMIT TRAN;
PRINT N'单据 ' + @BillCode + N' ' +
(CASE WHEN @Action=N'Audit' THEN N'审核' ELSE N'反审核' END) +
N' 成功，状态=' + CAST(@AfterState AS NVARCHAR(10)) + N'。';
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRAN;
DECLARE @Err NVARCHAR(4000)=ERROR_MESSAGE();
IF OBJECT_ID('dbo.Audit_Log','U') IS NOT NULL
BEGIN
INSERT INTO dbo.Audit_Log
(
DocType,DocId,DocCode,Action,
BeforeState,AfterState,Result,Remark,CreateBy,CreateOn
)
VALUES
(
N'InOutBill',@BillId,ISNULL(@BillCode,N''),
N'审核/反审核失败',@BeforeState,NULL,@Err,@Remark,@User,GETDATE()
);
END
RAISERROR(@Err,16,1);
END CATCH
END出入库单的表结构
CREATE TABLE [dbo].[WMS_InOutPutBill](
[Id] [int] IDENTITY(1,1) NOT NULL,
[InPutCode] [nvarchar](50) NULL,
[BillCode] [nvarchar](50) NULL,
[InorOutType] [int] NULL,
[BillOn] [datetime] NULL,
[BillInPutOn] [datetime] NULL,
[BillTypeId] [int] NULL,
[BillTypeName] [nvarchar](50) NULL,
[TradeId] [int] NULL,
[TradeName] [nvarchar](200) NULL,
[InPutAmount] [decimal](10, 2) NULL,
[Accepter] [nvarchar](200) NULL,
[AccepterTelphone] [nvarchar](50) NULL,
[AccepterMobile] [nvarchar](50) NULL,
[AccepterAddress] [nvarchar](200) NULL,
[Remark] [nvarchar](500) NULL,
[AdminRemark] [nvarchar](500) NULL,
[CreateBy] [nvarchar](250) NULL,
[CreateId] [int] NULL,
[CreateOn] [datetime] NULL,
[UpdateBy] [nvarchar](250) NULL,
[UpdateId] [int] NULL,
[UpdateOn] [datetime] NULL,
[Enabled] [int] NULL,
[BillState] [int] NULL,
[PrintState] [int] NULL,
[PrintCount] [int] NULL,
[WorkHouseId] [int] NULL,
[WorkHouseName] [nvarchar](150) NULL,
[MerchantId] [nvarchar](50) NULL,
[Oper] [nvarchar](50) NULL,
[OperId] [int] NULL,
CONSTRAINT [PK_WMS_InOutPutBill] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO出入库单明细表的表结构
CREATE TABLE [dbo].[WMS_InOutPutBillDetail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[InPutCode] [nvarchar](50) NULL,
[BillCode] [nvarchar](50) NULL,
[MaterialId] [int] NULL,
[MaterialCode] [nvarchar](200) NULL,
[MaterialName] [nvarchar](200) NULL,
[MaterialModel] [nvarchar](200) NULL,
[SecondMaterialUnit] [nvarchar](10) NULL,
[InputNum] [decimal](18, 2) NOT NULL,
[UnitPrice] [nvarchar](200) NULL,
[InputAmount] [decimal](18, 2) NULL,
[LotNo] [nvarchar](50) NULL,
[Remark] [nvarchar](500) NULL,
[CreateBy] [nvarchar](250) NULL,
[CreateId] [int] NULL,
[CreateOn] [datetime] NULL,
[UpdateBy] [nvarchar](250) NULL,
[UpdateId] [int] NULL,
[UpdateOn] [datetime] NULL,
[Enabled] [int] NULL,
[StoreId] [int] NULL,
[MerchantId] [nvarchar](50) NULL,
[MaterialUnitNum] [decimal](18, 2) NULL,
[CurrentInvNum] [decimal](18, 2) NULL,
[MaterialModelNum] [decimal](18, 2) NULL,
[MaterialUnit] [nvarchar](10) NULL,
[WorkHouseId] [int] NULL,
[WorkHouseName] [nvarchar](150) NULL,
CONSTRAINT [PK_WMS_InOutPutBillDetail] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO出库单日志表
CREATE TABLE [dbo].[WMS_InOutPutBillLog](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BillCode] [nvarchar](30) NULL,
[CreateBy] [nvarchar](50) NULL,
[CreateOn] [datetime] NULL,
[CreateId] [int] NULL,
[Remark] [nvarchar](250) NULL,
[Enabled] [int] NULL,
[MerchantId] [nvarchar](50) NULL,
CONSTRAINT [PK_WMS_InOutPutBillLog] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
调用示例：
1 -- 入库单审核
2 EXEC dbo.sp_InOutBill_AuditUnified
3 @BillId = 101,
4 @Action = N'Audit',
5 @User = N'张三',
6 @Remark = N'收货入库';
7
8 -- 出库单反审核
9 EXEC dbo.sp_InOutBill_AuditUnified
10 @BillId = 202,
11 @Action = N'UnAudit',
12 @User = N'李四',
13 @Remark = N'撤销出库';
