SQL - Allocations - Recalculate outstandings PostAP

--select * from postap p where p.AccountLink = 868


declare @vendorId bigint = 868
;with AllocValues as (
SELECT 
		p.AccountLink,p.AutoIdx, CmD = p.Credit - p.Debit,
		p.Outstanding, p.fForeignOutstanding	
		,SUM(a.fAmount) as AllocAmount
		,SUM(a.fForeignAmount) as AllocAmountForeign
		,AllocOutstanding = ROUND((p.Credit - p.Debit)-
			COALESCE(CASE WHEN p.Credit > 0 THEN  1 ELSE -1 END * SUM(a.fAmount),0),2)
		,AllocOutstandingForeign = CASE WHEN V.bForCurAcc = 1 THEN ROUND((p.fForeignCredit - p.fForeignDebit)-
			COALESCE(CASE WHEN p.fForeignCredit > 0 THEN  1 ELSE -1 END * SUM(a.fForeignAmount),0),2)
				ELSE 0 END
	from PostAP p 
	LEFT JOIN Vendor V ON p.AccountLink = V.DCLink
	OUTER APPLY _as_GL_AllocsSplit_NTEXT(p.cAllocs) a
	--where p.AccountLink = 868
	GROUP BY 
		p.AccountLink,p.AutoIdx, p.Outstanding, p.fForeignOutstanding,p.Credit, p.Debit,p.fForeignCredit, p.fForeignDebit
		,V.bForCurAcc
		)
	,Diff as (select *
		,DiffHC = Outstanding - AllocOutstanding
		,DiffFC = fForeignOutstanding - AllocOutstandingForeign
		from AllocValues A
		WHERE 
			ABS(Outstanding - AllocOutstanding) > 0.01
			OR 
			ABS(fForeignOutstanding - AllocOutstandingForeign) > 0.01)
	--select * from Diff
	UPDATE p 
		SET p.Outstanding = Diff.AllocOutstanding
			, p.fForeignOutstanding = Diff.AllocOutstandingForeign
		FROM PostAP p
		INNER JOIN Diff	ON p.AutoIdx = Diff.AutoIdx

CREATE OR ALTER PROCEDURE dbo._as_RecalcPostAPOutstanding
    @PostAPAutoIdx INT,
    @Preview       BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    ------------------------------------------------------------------
    -- USAGE:
    -- -- Preview the recalculated outstanding amounts without updating:
    -- EXEC dbo._as_RecalcPostAPOutstanding
    --     @PostAPAutoIdx = 12345,
    --     @Preview       = 1;
    --
    -- -- Recalculate and update outstanding amounts:
    -- EXEC dbo._as_RecalcPostAPOutstanding
    --     @PostAPAutoIdx = 12345;
    ------------------------------------------------------------------

    -- 1) Fetch stored & source values
    DECLARE
        @StoredOutstanding        FLOAT,
        @StoredForeignOutstanding FLOAT,
        @Credit                   FLOAT,
        @Debit                    FLOAT,
        @ForeignCredit            FLOAT,
        @ForeignDebit             FLOAT
        --@cAllocs                  NTEXT;

    SELECT
        @StoredOutstanding        = outstanding,
        @StoredForeignOutstanding = fForeignOutstanding,
        @Credit                   = Credit,
        @Debit                    = Debit,
        @ForeignCredit            = fForeignCredit,
        @ForeignDebit             = fForeignDebit
     --   @cAllocs                  = cAllocs
    FROM dbo.PostAP
    WHERE PostAP.AutoIdx = @PostAPAutoIdx;

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('No PostAP record found for PostAPAutoIdx = %d',
                  16, 1, @PostAPAutoIdx);
        RETURN;
    END

    -- 2) Calculate base outstanding from Credit–Debit
    DECLARE
        @BaseOutstanding        FLOAT = @Credit - @Debit,
        @BaseForeignOutstanding FLOAT = @ForeignCredit - @ForeignDebit;

    -- 3) Sum allocations from cAllocs
    DECLARE
        @AllocatedAmount  FLOAT = 0,
        @AllocatedForeign FLOAT = 0;

    SELECT
        @AllocatedAmount  = ISNULL(SUM(t.fAmount), 0),
        @AllocatedForeign = ISNULL(SUM(t.fForeignAmount), 0)
    FROM PostAP p 
		cross apply dbo._as_GL_AllocsSplit_NTEXT(p.cAllocs) t
		where p.AutoIdx = @PostAPAutoIdx

    -- 4) Compute new outstanding values
    DECLARE
        @NewOutstanding        FLOAT,
        @NewForeignOutstanding FLOAT;

    SELECT
        @NewOutstanding        = CASE WHEN @BaseOutstanding > 0 THEN @BaseOutstanding - @AllocatedAmount ELSE -(ABS(@BaseOutstanding) - @AllocatedAmount) END ,
        @NewForeignOutstanding = CASE WHEN @BaseForeignOutstanding  > 0 THEN @BaseForeignOutstanding  - @AllocatedForeign ELSE -(ABS(@BaseForeignOutstanding ) - @AllocatedForeign) END 

    -- 5) If preview, show stored vs base vs new
    IF @Preview = 1
    BEGIN
        SELECT
            p.AutoIdx             AS PostAPAutoIdx,
            p.Outstanding AS StoredOutstanding,
            @BaseOutstanding           AS CalculatedBaseOutstanding,
			@AllocatedAmount as AllocatedAmount,
			p.Credit - p.Debit as CmD,
            @NewOutstanding            AS NewOutstanding,
            @StoredOutstanding - @NewOutstanding AS DifferenceFromStored,
            @StoredForeignOutstanding  AS StoredForeignOutstanding,
            @BaseForeignOutstanding    AS CalculatedBaseForeignOutstanding,
            @NewForeignOutstanding     AS NewForeignOutstanding,
            @StoredForeignOutstanding - @NewForeignOutstanding AS ForeignDifferenceFromStored
			FROM PostAP p where p.AutoIdx = @PostAPAutoIdx
        RETURN;
    END

    -- 6) Otherwise, apply update in a transaction
    BEGIN TRAN;
    BEGIN TRY
        UPDATE dbo.PostAP
        SET
            outstanding         = @NewOutstanding,
            fForeignOutstanding = @NewForeignOutstanding
        WHERE PostAP.AutoIdx = @PostAPAutoIdx;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        THROW;
    END CATCH
END
GO