/**
* Looks up the current AR balance of the specified invoice.
*
* Parameters:
* @inInvoiceNo: The invoice number of the invoice to retrieve the balance for.
*/
CREATE procedure [dbo].[GetSalesCommissionInvoiceArBalance]
(
    @inInvoiceNo varchar(31)
)
as
set nocount on

declare @theBalanceTotal decimal(15,2) = 0
declare @theInvoiceNoStr nvarchar(40) = convert( nvarchar(31), replace( @inInvoiceNo, '''', '''''' ) )

declare @theAccountingSystemType varchar(2) = dbo.GetAccountingSystemType()

-- XLTD: Need to do this for XLedger.
if( @theAccountingSystemType = 'GP' )
begin
    select
        @theBalanceTotal = sum( GPTransactions.Balance )
    from (

        -- Open Trax
        select
            Balance = convert( decimal(15,2), case
                    when RM20101.RMDTYPAL in ( 7, 8, 9 ) then -RM20101.CURTRXAM
                    else RM20101.CURTRXAM
                end )
        from MSS10000_Synonym as MSS10000
        inner join RM20101_Synonym as RM20101 on
            RM20101.DOCNUMBR = MSS10000.DOCNUMBR and
            RM20101.RMDTYPAL = MSS10000.DOCTYPE and
            MSS10000.SERIES = 3
        inner join RM00101_Synonym as RM00101 on
            RM00101.CUSTNMBR = RM20101.CUSTNMBR
        left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
        where
            MSS10000.MSS_InvoiceNumber = @theInvoiceNoStr and
            RM00101.INACTIVE = 0

        -- Unposted Trax
        union all
        select
            Balance = convert( decimal(15,2), case
                when RM10301.RMDTYPAL in ( 7, 8, 9 ) then -RM10301.ACCTAMNT
                else RM10301.ACCTAMNT
            end )
        from MSS10000_Synonym as MSS10000
        inner join RM10301_Synonym as RM10301 on
            RM10301.DOCNUMBR = MSS10000.DOCNUMBR and
            RM10301.RMDTYPAL = MSS10000.DOCTYPE and
            MSS10000.SERIES = 3
        where
            MSS10000.MSS_InvoiceNumber = @theInvoiceNoStr

        -- Unposted Cash Receipts
        union all
        select
            Balance = convert( decimal(15,2), case
                when RM10201.RMDTYPAL in ( 7, 8, 9 ) then -RM10201.CURTRXAM
                else RM10201.CURTRXAM
            end )
        from RM10201_Synonym as RM10201
        inner join MSS10000_Synonym as MSS10000 on
            RM10201.DOCNUMBR = MSS10000.DOCNUMBR and
            RM10201.RMDTYPAL = MSS10000.DOCTYPE and
            MSS10000.SERIES = 3
        inner join RM00101_Synonym as RM00101 on RM10201.CUSTNMBR = RM00101.CUSTNMBR
        left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
        where
            MSS10000.MSS_InvoiceNumber = @theInvoiceNoStr
        ) as GPTransactions
end

select
    BalanceTotal = @theBalanceTotal
GO
GRANT EXECUTE ON  [dbo].[GetSalesCommissionInvoiceArBalance] TO [MssExec]
GO