Piolot_RepotForm_PeiHao/重置收款计划的已核销金额字段.sql

-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD>ڣ<EFBFBD><DAA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɾ<EFBFBD><C9BE> P202502080013
IF OBJECT_ID('dbo.[PR_ResetSALORDERPLAN_MBBA_yuyubo]', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo;
    PRINT '<EFBFBD>洢<EFBFBD><EFBFBD><EFBFBD><EFBFBD> dbo.[PR_ResetSALORDERPLAN_MBBA_yuyubo] <20><>ɾ<EFBFBD><C9BE>';
END
GO

-- <20><><EFBFBD><EFBFBD><EFBFBD>µĴ洢<C4B4><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo
@saleOrderId INT=0
AS 
BEGIN 
-- 1. <20><><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
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 [<EFBFBD><EFBFBD><EFBFBD>۵<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ID],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>] from [ZRP_YingShouMingXi_yuyubo] WHERE [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>]>0) cc ON [<EFBFBD><EFBFBD><EFBFBD>۵<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ID]=a.FID
WHERE (ISNULL(a.F_Amount,0) > 0  AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>]-ISNULL(a.F_Amount,0) -[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]>0) AND (a.FID=@saleOrderId OR @saleOrderId=0) 
AND b.F_MBBA_DECIMAL_UKY<a.F_Amount
ORDER BY a.fid,wfid

-- 2. <20><><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
OPEN cursor_name;
DECLARE @rowId INT=0,@amount DECIMAL(18,4)=0
-- 3. ѭ<><D1AD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD>
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
    -- <20><><EFBFBD>ݴ<EFBFBD><DDB4><EFBFBD><EFBFBD>߼<EFBFBD><DFBC><EFBFBD>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    --PRINT 'Column1: ' + CAST(@var1 AS VARCHAR) + ', Column2: ' + @var2;
	-- <20><><EFBFBD><EFBFBD><EFBFBD>տ<EFBFBD><D5BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
	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

    -- <20><>ȡ<EFBFBD><C8A1>һ<EFBFBD><D2BB><EFBFBD><EFBFBD>¼
    FETCH NEXT FROM cursor_name INTO @F_AMOUNT, @F_MBBA_Decimal_uky,@FBILLNO,@FSALEORGID,@F_contractnumber,@fid,@wfid,@FRecAdvanceAmount,@F_EXPENSES;
END;

-- 4. <20>ر<EFBFBD><D8B1>α<EFBFBD>
CLOSE cursor_name;

-- 5. <20>ͷ<EFBFBD><CDB7>α<EFBFBD><CEB1><EFBFBD>Դ
DEALLOCATE cursor_name;


END


--EXEC dbo.PR_ResetSALORDERPLAN_MBBA_yuyubo @saleOrderId = 0 -- int


 --UPDATE T_SAL_ORDERPLAN SET F_MBBA_Decimal_uky=0