Page History

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