74 lines
2.4 KiB
Transact-SQL
74 lines
2.4 KiB
Transact-SQL
-- 检查存储过程是否存在，存在则删除 P202502080013
|
|
IF OBJECT_ID('dbo.[PR_ResetSALORDERPLAN_MBBA_yuyubo]', 'P') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo;
|
|
PRINT '存储过程 dbo.[PR_ResetSALORDERPLAN_MBBA_yuyubo] 已删除';
|
|
END
|
|
GO
|
|
|
|
-- 创建新的存储过程
|
|
CREATE PROCEDURE dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo
|
|
@saleOrderId INT=0
|
|
AS
|
|
BEGIN
|
|
-- 1. 声明游标
|
|
DECLARE cursor_name CURSOR
|
|
FOR
|
|
SELECT a.F_Amount,b.F_MBBA_Decimal_uky,a.FBILLNO,a.FSALEORGID,a.F_contractnumber,a.fid,b.FENTRYID AS wfid ,b.FRECADVANCEAMOUNT,a.F_EXPENSES
|
|
FROM dbo.T_SAL_ORDER a WITH(NOLOCK) LEFT JOIN dbo.T_SAL_ORDERPLAN b WITH(NOLOCK) ON a.FID=b.FID
|
|
INNER JOIN (SELECT DISTINCT [销售单主键ID],[整单累计退货金额],[整单正数订单总额] from [ZRP_YingShouMingXi_yuyubo] WHERE [整单正数订单总额]>0) cc ON [销售单主键ID]=a.FID
|
|
WHERE (ISNULL(a.F_Amount,0) > 0 AND [整单正数订单总额]-ISNULL(a.F_Amount,0) -[整单累计退货金额]>0) AND (a.FID=@saleOrderId OR @saleOrderId=0)
|
|
AND b.F_MBBA_DECIMAL_UKY<a.F_Amount
|
|
ORDER BY a.fid,wfid
|
|
|
|
-- 2. 打开游标
|
|
OPEN cursor_name;
|
|
DECLARE @rowId INT=0,@amount DECIMAL(18,4)=0
|
|
-- 3. 循环获取数据
|
|
DECLARE @F_AMOUNT DECIMAL(18,2), @F_MBBA_Decimal_uky DECIMAL(18,2),@FBILLNO NVARCHAR(200),@FSALEORGID INT ,@F_contractnumber NVARCHAR(500) ,@fid INT ,@wfid INT,@FRecAdvanceAmount DECIMAL(18,2),@F_EXPENSES DECIMAL(18,6) ;
|
|
FETCH NEXT FROM cursor_name INTO @F_AMOUNT, @F_MBBA_Decimal_uky,@FBILLNO,@FSALEORGID,@F_contractnumber,@fid,@wfid,@FRecAdvanceAmount,@F_EXPENSES;
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
-- 数据处理逻辑（示例输出）
|
|
--PRINT 'Column1: ' + CAST(@var1 AS VARCHAR) + ', Column2: ' + @var2;
|
|
-- 处理收款核销金额
|
|
IF(@rowId !=@fid)
|
|
BEGIN
|
|
SELECT @rowId=@fid,@amount=@F_AMOUNT-@F_EXPENSES;
|
|
END
|
|
|
|
IF(@amount>=@FRecAdvanceAmount)
|
|
BEGIN
|
|
UPDATE T_SAL_ORDERPLAN SET F_MBBA_Decimal_uky=@FRecAdvanceAmount
|
|
WHERE FENTRYID=@wfid;
|
|
SELECT @amount=@amount-@FRecAdvanceAmount;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF(@amount>0)
|
|
BEGIN
|
|
UPDATE T_SAL_ORDERPLAN SET F_MBBA_Decimal_uky=@amount
|
|
WHERE FENTRYID=@wfid;
|
|
SELECT @amount=0;
|
|
END
|
|
END
|
|
|
|
-- 获取下一条记录
|
|
FETCH NEXT FROM cursor_name INTO @F_AMOUNT, @F_MBBA_Decimal_uky,@FBILLNO,@FSALEORGID,@F_contractnumber,@fid,@wfid,@FRecAdvanceAmount,@F_EXPENSES;
|
|
END;
|
|
|
|
-- 4. 关闭游标
|
|
CLOSE cursor_name;
|
|
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE cursor_name;
|
|
|
|
|
|
END
|
|
|
|
|
|
--EXEC dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo @saleOrderId = 0 -- int
|
|
|
|
|
|
--UPDATE T_SAL_ORDERPLAN SET F_MBBA_Decimal_uky=0 |