SQL - Relink AR AP Balances

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV, Alex
-- Create date: 13-04-2022
-- Description:	Relink customer and supplier balances
-- =============================================
CREATE PROCEDURE _as_Relink_ARAP 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Relink AR
	UPDATE C
		SET	
			C.DCBalance = COALESCE(t.HomeAmount,0)
			,C.fForeignBalance = COALESCE(t.ForeignAmount,0)
		FROM Client C 
		LEFT JOIN (
	SELECT AccountLink, ROUND(SUM(Debit-Credit),2) as HomeAmount
		, ROUND(SUM(CASE WHEN C.bForCurAcc = 1 THEN fForeignDebit - fForeignCredit ELSE 0 END),2) as ForeignAmount
		FROM PostAR
		LEFT JOIN Client C ON PostAR.AccountLink = C.DCLink
		GROUP BY AccountLink) t on t.AccountLink = C.DCLink

	 -- Relink AP
	UPDATE V
		SET	
			V.DCBalance = -COALESCE(t.HomeAmount,0)
			,V.fForeignBalance = -COALESCE(t.ForeignAmount,0)
		FROM Vendor V 
		LEFT JOIN (
	SELECT AccountLink, ROUND(SUM(Debit-Credit),2) as HomeAmount
		, ROUND(SUM(CASE WHEN C.bForCurAcc = 1 THEN fForeignDebit - fForeignCredit ELSE 0 END),2) as ForeignAmount
		FROM PostAP
		LEFT JOIN Vendor C ON PostAP.AccountLink = C.DCLink
		GROUP BY AccountLink) t on t.AccountLink = V.DCLink

END