/****** Object: StoredProcedure [dbo].[Gp_AddDataToAuditingBill] Script Date: 2025-07-07 15:08:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
作者:谢吉康
日期:2005-8-4
描述:增加新的单据到AuditingBill中
新Carpa平台后重新调整 卢春 2009-06
添加询价单,请购单的修改状态 周华 2013-11-27
*/
ALTER PROCEDURE [dbo].[Gp_AddDataToAuditingBill]
(
@BillNumberId varchar(8000), --单据编号
@BillIndexType int, --单据类型 0.订单 1.借欠 2.一般物流单据
@OperatorETypeID VARCHAR(100) --操作员ID号
)
--@encode$--
AS
SET NOCOUNT ON
DECLARE @OperatorId varchar(100),
@BillType int,
@ETypeId varchar(100),
@STypeID VARCHAR(100),
@BillDate datetime,
@BillCode varchar(256),
@AuditingId int,
@AuditingLevel int,
@AuditingLevel1 int,
@AuditingType int,
@InOrOut varchar(10),
@Checke VARCHAR(50) --制单人
BEGIN TRAN
-- 判断状态是否待审核或审核通过,是则退出
IF EXISTS(SELECT TOP 1 BillNumberId FROM AuditingBill WHERE BillNumberId = @BillNumberId AND BillIndexType = @BillIndexType AND IfAudit <> -1)
BEGIN
ROLLBACK TRAN
RETURN -2
END
-- 审核不通过,则删除掉原记录,重新写记录
IF EXISTS(SELECT TOP 1 BillNumberId FROM AuditingBill WHERE BillNumberId = @BillNumberId AND BillIndexType = @BillIndexType AND IfAudit = -1)
BEGIN
DELETE AuditingBillDetail WHERE BillNumberId=@BillNumberId AND BillIndexType=@BillIndexType
DELETE AuditingBill WHERE BillNumberId = @BillNumberId AND BillIndexType = @BillIndexType
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -6
END
END
IF @BillIndexType = 0 -- 订单
BEGIN
--修改订单状态
IF EXISTS(SELECT TOP 1 BillNumberId FROM OrderIndex WHERE BillNumberId = @BillNumberId)
BEGIN
UPDATE OrderIndex
SET IfAudit = 0
WHERE BillNumberId = @BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM OrderIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType = 1 -- 借欠
BEGIN
--修改订单状态
IF EXISTS(SELECT TOP 1 BillNumberId FROM dbo.LendIndex WHERE BillNumberId = @BillNumberId)
BEGIN
UPDATE LendIndex
SET IfAudit = 0
WHERE BillNumberId = @BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM LendIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType = 2 -- 一般物流单据
BEGIN
--修改184报价单状态
IF EXISTS(SELECT TOP 1 BillNumberId FROM BillIndex WHERE BillNumberId = @BillNumberId AND BillType=184)
BEGIN
UPDATE BillIndex
SET IfAudit = 0
WHERE BillNumberId = @BillNumberId AND BillType=184
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
-- 修改询价单状态
IF EXISTS(SELECT TOP 1 BillNumberId FROM BillIndex WHERE BillNumberId = @BillNumberId AND BillType =185)
BEGIN
UPDATE BillIndex
SET IfAudit = 0
WHERE BillNumberId = @BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM BillIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType=3 --请购单
BEGIN
-- 修改请购单状态
IF EXISTS(SELECT TOP 1 BillNumberId FROM RequestIndex WHERE BillNumberId = @BillNumberId AND BillType=204)
BEGIN
UPDATE dbo.RequestIndex
SET IfAudit = 0
WHERE BillNumberId = @BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM dbo.RequestIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType=4 --提成单
BEGIN
---- 修改请购单状
IF EXISTS(SELECT TOP 1 BillNumberId FROM CommissionBillNew WHERE BillNumberId = @BillNumberId)
BEGIN
UPDATE dbo.CommissionBillNew
SET BillState = 3
WHERE BillNumberId = @BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = 311,
@ETypeId = Checke,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM dbo.CommissionBillNew
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType=5--发票单据
BEGIN
IF EXISTS(SELECT TOP 1 BillNumberid FROM ReceiptBillIndex WHERE BillNumberID=@BillNumberId)
BEGIN
UPDATE dbo.ReceiptBillIndex
SET IfAudit=0
WHERE BillNumberID=@BillNumberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM ReceiptBillIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType = 7 --实物库存
BEGIN
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ETypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM FactIndex
WHERE BillNumberId = @BillNumberId
END
IF @BillIndexType = 9--门店类
BEGIN
--获取单据的信息
SELECT @BillType = BillType,
@ETypeId = ApplyEtypeId,
@STypeID = STypeID,
@BillDate = BillDate,
@BillCode = BillCode,
@Checke=Checke
FROM PosAllotApplyBillIndex
WHERE BillNumberId = @BillNumberId
END
--判断单据是否有审核流程
SELECT @AuditingId = AuditingId,
@AuditingLevel = AuditingLevel,
@AuditingLevel1 = AuditingLevel1,
@AuditingType = AuditingType
FROM AuditingFlowSet
WHERE BillType = @BillType
AND STypeID=@STypeID
AND IfUse = 1
--写入审核表(AuditingBill)中
INSERT INTO AuditingBill
(
BillNumberId,
AuditingId,
AuditingSteps,
IfAudit,
ETypeId,
BillDate,
BillCode,
BillDetail,
BillIndexType,
PassSteps,
Checke
)VALUES(
@BillNumberId,
@AuditingId,
REPLACE(REPLICATE(@AuditingLevel,@AuditingLevel),@AuditingLevel,'2'),
0,
@ETypeId,
@BillDate,
@BillCode,
'',
@BillIndexType,
REPLACE(REPLICATE(@AuditingLevel,@AuditingLevel),@AuditingLevel,'2'),
@Checke
)
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -3
END
----------------------------------------------------------------------------------------------
--【定制】销售订单制单人与审核人相同时校验余额是否大于订单金额
IF EXISTS(select TOP 1 1 FROM orderindex WHERE BillNumberId = @BillNumberId AND SaleWay = 1)
BEGIN
DECLARE @DealBTypeID VARCHAR(50)
DECLARE @OrderTotal numeric(24, 10)
DECLARE @BillTotal numeric(24, 10)
-- 获取订单的DealBTypeID
SELECT @DealBTypeID = dealbtypeid FROM orderindex WHERE BillNumberId = @BillNumberId
SELECT @BillTotal = ISNULL(SUM(totalmoney),0) - ISNULL(SUM(preferencemoney),0) - ISNULL(( SELECT SUM(totalmoney) FROM orderindex WHERE billtype = 300 AND dealbtypeid=@DealBTypeID AND IfAudit = 1 ),0)
FROM billindex WITH ( NOLOCK )
WHERE billtype IN ( 4, 189 ) AND dealbtypeid=@DealBTypeID AND ifcheck = 't' AND redword = '0'
SELECT @OrderTotal = totalmoney FROM orderindex WHERE billnumberid=@BillNumberId
IF @BillTotal < @OrderTotal
BEGIN
ROLLBACK TRAN
RETURN -9
END
END
---------------------------------------------------------------------------------------------
-- PassSteps代表整单审核通过需要经历的步骤,与原因相关
DECLARE @PassSteps VARCHAR(50)
SET @PassSteps = REPLACE(REPLICATE(@AuditingLevel,@AuditingLevel),@AuditingLevel,'2')
CREATE TABLE #temp(
BillNumberID INT,
BillType NUMERIC(4, 0),
PassFlag bit,
Reason VARCHAR(500),
value VARCHAR(500),
AuditingStep int
)
INSERT #temp EXEC GP_BillAudtingCheckReason @BillNumberId,@OperatorETypeID,@BillIndexType,'1',-1,0
DECLARE @i INT,@j INT,@stepReason VARCHAR(500),@stepValue VARCHAR(500)
-- 处理特审原因
DECLARE CURSOR_str CURSOR FOR SELECT DISTINCT Reason,Value,AuditingStep FROM #temp WHERE PassFlag = 0 ORDER BY AuditingStep
OPEN CURSOR_str
FETCH NEXT FROM CURSOR_str INTO @stepReason,@stepValue,@i
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE AuditingBill SET StepReasons = ISNULL(StepReasons,'') + '0ж' + RTRIM(@stepReason + ' ' + ISNULL(@stepValue,'')) + 'жи'
WHERE BillNumberID = @BillNumberId AND BillIndexType = @BillIndexType
FETCH NEXT FROM CURSOR_str INTO @stepReason,@stepValue,@i
END
CLOSE CURSOR_str
DEALLOCATE CURSOR_str
-- 写入每级相关原因
SET @j = 0
DECLARE CURSOR_str CURSOR FOR SELECT AuditingStep,Reason,value FROM #temp WHERE PassFlag = 0 ORDER BY AuditingStep
OPEN CURSOR_str
FETCH NEXT FROM CURSOR_str INTO @i,@stepReason,@stepValue
WHILE @@FETCH_STATUS = 0
BEGIN
IF @stepReason<>'...'
BEGIN
UPDATE AuditingBill SET StepReasons = ISNULL(StepReasons,'') + CAST(@i AS VARCHAR(10)) + 'ж' + RTRIM(@stepReason + ' ' + ISNULL(@stepValue,'')) + 'жи'
WHERE BillNumberID=@BillNumberId AND BillIndexType=@BillIndexType
IF @i <> @j
BEGIN
SET @PassSteps = LEFT(@PassSteps,@i-1) + '1' + RIGHT(@PassSteps,LEN(@PassSteps)-@i)
END
SET @j = @i
END
FETCH NEXT FROM CURSOR_str INTO @i,@stepReason,@stepValue
END
CLOSE CURSOR_str
DEALLOCATE CURSOR_str
UPDATE AuditingBill SET PassSteps=@PassSteps WHERE BillNumberID=@BillNumberId AND BillIndexType=@BillIndexType
-- 设置灯泡提醒
IF @AuditingType = 1
DECLARE AuditingCursor CURSOR FOR
SELECT ETypeId FROM AuditingFlowDetail
WHERE AuditingId = @AuditingId AND AuditingStep IN (SELECT TOP 1 AuditingStep FROM #temp WHERE PassFlag=0 ORDER BY AuditingStep)
IF @AuditingType = 3
DECLARE AuditingCursor CURSOR FOR
SELECT ETypeId FROM AuditingFlowDetail
WHERE AuditingId = @AuditingId AND AuditingStep IN (SELECT AuditingStep FROM #temp WHERE PassFlag=0 GROUP BY AuditingStep)
OPEN AuditingCursor
FETCH NEXT FROM AuditingCursor INTO @OperatorId
WHILE @@FETCH_STATUS = 0
BEGIN
-- 是否有修改他人草稿的权限 0.有 1.无
DECLARE @ifCanEditOtherDraft INT
EXEC @ifCanEditOtherDraft = GP_CheckLimit @OperatorId, 135
-- 是否有审核他人草稿的权限 0.有 1.无
DECLARE @AuditingOtherDraft int
EXEC @AuditingOtherDraft = GP_CheckLimit @OperatorID,467
DECLARE @checkeWhere VARCHAR(50)
IF @ifCanEditOtherDraft = 0 OR @AuditingOtherDraft = 0
SET @checkeWhere = '%%'
ELSE
SET @checkeWhere = @OperatorId
DECLARE @ifAlerm INT
SET @ifAlerm = 0
IF @BillType IN (17,21)-- AND @AuditingType = 1
BEGIN
-- 检查仓库权限是否有效 0.有效 1.无效
DECLARE @StockLimit INT
EXEC @StockLimit = GP_CheckLimit @OperatorID,454
-- 调拨单中,仓库的权限无效
IF EXISTS(SELECT TOP 1 1 FROM Userconfig WHERE ConfigName = 'MoveInOutKLimit' AND Value = '0') OR @StockLimit = 0
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex
WHERE BillNumberId = @BillNumberId
AND ETypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND (KTypeID IN (SELECT * FROM F_GetManDetailKType(@OperatorId)) OR KTypeID2 IN (SELECT * FROM F_GetManDetailKType(@OperatorId)))
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
ELSE
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex
WHERE BillNumberId = @BillNumberId
AND ETypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND (KTypeID IN (SELECT * FROM F_GetManDetailKType(@OperatorId)) AND KTypeID2 IN (SELECT * FROM F_GetManDetailKType(@OperatorId)))
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
END
ELSE IF @BillType IN (300,301) -- 订单
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM OrderIndex
WHERE BillNumberId = @BillNumberId
AND ETypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND BTypeId IN (SELECT * FROM F_GetManDetailBType(@OperatorId))
AND KTypeId IN (SELECT * FROM F_GetManDetailKType(@OperatorId))
)
SET @ifAlerm = 1
END
ELSE IF @BillType IN (57)
BEGIN
IF EXISTS(
SELECT BillNumberId
FROM AdPriceBill
WHERE BillNumberId=@BillNumberId AND ChangeAllStock=1
)
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex
WHERE BillNumberId = @BillNumberId
AND ETypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND (BTypeId IN (SELECT * FROM F_GetManDetailBType(@OperatorId)) OR ISNULL(BTypeId,'')='')
AND (KTypeId='00000')
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
ELSE
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex
WHERE BillNumberId = @BillNumberId
AND ETypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND (BTypeId IN (SELECT * FROM F_GetManDetailBType(@OperatorId)) OR ISNULL(BTypeId,'')='')
AND (KTypeId IN (SELECT * FROM F_GetManDetailKType(@OperatorId)) OR ISNULL(KTypeId,'')='')
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
END
ELSE IF @BillType IN (328,327) --门店单据
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM PosAllotApplyBillIndex
WHERE BillNumberId = @BillNumberId
AND ApplyEtypeId IN (SELECT * FROM F_GetManDetailEType(@OperatorId))
AND (KTypeID IN (SELECT * FROM F_GetManDetailKType(@OperatorId)) OR InKtypeId IN (SELECT * FROM F_GetManDetailKType(@OperatorId)))
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
ELSE
BEGIN
--2018-08-15 谢吉康 以前的算法不合理,优化
DECLARE @BTypeId VARCHAR(500)
SELECT @BTypeId = BTypeID FROM BillIndex WHERE BillNumberid = @BillNumberId
IF @BTypeId <> ''
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex B
INNER JOIN (SELECT * FROM F_GetManDetailEType(@OperatorId)) E ON B.ETypeId = E.TypeId
INNER JOIN (SELECT * FROM F_GetManDetailBTypeForAudit(@OperatorId,@BTypeId)) MB ON MB.TypeId = B.btypeid
INNER JOIN (SELECT * FROM F_GetManDetailKType(@OperatorId)) MK ON (MK.TypeId = B.KTypeId OR ISNULL(KTypeId,'')='')
WHERE BillNumberId = @BillNumberId
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
ELSE
BEGIN
IF EXISTS(
SELECT TOP 1 1
FROM BillIndex B
INNER JOIN (SELECT * FROM F_GetManDetailEType(@OperatorId)) E ON B.ETypeId = E.TypeId
INNER JOIN (SELECT * FROM F_GetManDetailKType(@OperatorId)) MK ON (MK.TypeId = B.KTypeId OR ISNULL(KTypeId,'')='')
WHERE BillNumberId = @BillNumberId
AND checke LIKE @checkeWhere
)
SET @ifAlerm = 1
END
END
IF @ifAlerm = 1
BEGIN
IF @AuditingType = 1
BEGIN
UPDATE AuditingFlowDetail
SET AuditingAlerm = 1
WHERE AuditingId = @AuditingId
AND ETypeId = @OperatorId
AND AuditingStep IN (SELECT TOP 1 AuditingStep FROM #temp WHERE PassFlag=0 ORDER BY AuditingStep)
END
IF @AuditingType = 3
BEGIN
UPDATE AuditingFlowDetail
SET AuditingAlerm = 1
WHERE AuditingId = @AuditingId
AND ETypeId = @OperatorId
AND AuditingStep IN (SELECT AuditingStep FROM #temp WHERE PassFlag=0 GROUP BY AuditingStep)
END
END
FETCH NEXT FROM AuditingCursor INTO @OperatorId
END
CLOSE AuditingCursor
DEALLOCATE AuditingCursor
--处理同一个人审核人的情况
IF EXISTS(SELECT TOP 1 1 FROM dbo.Userconfig WHERE ConfigName = 'samepersonchecked' AND Value = '1')
BEGIN
IF EXISTS(SELECT TOP 1 1 FROM AuditingFlowSet WHERE BillType = @BillType AND AuditingType = 3) AND EXISTS(SELECT TOP 1 1 FROM AuditingFlowDetail WHERE BillType = @BillType AND AuditingStep <> 0 AND ETypeID = @OperatorETypeID) --会签审核
BEGIN
EXEC GP_BillAuditingDeal @OperatorETypeID,@BillNumberId,'1','自动审核通过',@BillIndexType,'',''
END
ELSE
BEGIN
--处理审核消息
DECLARE @nextStep INT
IF EXISTS(SELECT TOP 1 AuditingStep FROM AuditingBillDetail WHERE BillNumberID = @BillNumberId AND BillIndexType = @BillIndexType)
SELECT TOP 1 @nextStep = AuditingStep + 1 FROM AuditingBillDetail WHERE BillNumberID = @BillNumberId AND BillIndexType = @BillIndexType ORDER BY AuditingStep DESC
ELSE
SET @nextStep = 1
SELECT * INTO #AuditingFlowDetail FROM AuditingFlowDetail WHERE BillType = @BillType AND AuditingStep <> 0 AND ETypeID = @OperatorETypeID
WHILE EXISTS(SELECT TOP 1 1 FROM #AuditingFlowDetail WHERE AuditingStep = @nextStep)
BEGIN
EXEC GP_BillAuditingDeal @OperatorETypeID,@BillNumberId,'1','自动审核通过',@BillIndexType,'',''
SET @nextStep += 1
END
DROP TABLE #AuditingFlowDetail
END
END
--单据审核流程完成后自动过账
DECLARE @RE INT
EXEC @RE = GP_CheckLimit @OperatorETypeID,2659
--处理价格跟踪
IF @BillType IN (184,185,300,301)
BEGIN
EXEC dbo.GP_BatchPassPT @billnumberid=@BillNumberId,@billtype=@BillType
END
--自动过账
IF @billtype NOT IN(184,185,300,313,204,185,301,22,326,162,163) AND EXISTS(SELECT TOP 1 1 FROM dbo.AuditingBill WHERE BillNumberID = @BillNumberId AND BillIndexType = @BillIndexType AND IfAudit = 1)
AND @RE = 0
BEGIN
EXEC dbo.Gp_Auditing @nBillID = @BillNumberId, -- numeric
@szCheckMan = @Checke, -- varchar(50)
@szPID = '', -- varchar(1000)
@chvOpID = @OperatorETypeID, -- varchar(50)
@KTypeID = '', -- varchar(50)
@billtype = @BillType, -- int
@voucherMsg = '', -- varchar(50)
@vipcentinfo='',
@AssetMsg='',
@VipCardCode = '' ,--VARCHAR(200)
@CommissionConfirm = 0 ,--INT
@strReturnBillCode = '' --VARCHAR(200)
IF EXISTS(SELECT TOP 1 1 FROM dbo.Userconfig WHERE ConfigName = 'BillCodeCreateTime' AND Value = 2)
EXEC dbo.GP_SaveBillCode_Seed @intBillNumberID=@BillNumberId,@intBillType=@BillType,@dBillDate=@billdate,@strBillCode=@BillCode
IF(@BillType IN(17,21,404,327,328)) --没有btypeid 的不要插入,否则报错
begin
INSERT INTO BillOperateLog(billnumberid,operatetype,etypeid,OperateTime,ip,billcode,billdate,ifcheck,ntotalmoney,billtype)
VALUES(@billnumberid,'自动审核过账',@OperatorETypeID,GETDATE(),'',@billcode,@billdate,'t',0,@billtype)
END
ELSE
BEGIN
INSERT INTO BillOperateLog(billnumberid,operatetype,etypeid,OperateTime,ip,billcode,billdate,ifcheck,btypeid,ntotalmoney,billtype)
VALUES(@billnumberid,'自动审核过账',@OperatorETypeID,GETDATE(),'',@billcode,@billdate,'t',ISNULL(@btypeid,''),0,@billtype)
END
--自动审核过账,反写一大堆数量,状态等.正常过账反写以前是写在代码里面的,没有在存储过程里,所以要单独写出来
EXEC Gp_Auditing_UpdateQtyorState @BillNumberId, @BillType
END
DROP TABLE #temp
--成功,退出
COMMIT TRAN
RETURN 0
最新发布