/**
* 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 @theSQL nvarchar(4000)
declare @theParamList nvarchar(128)
declare @theDefaultBranchID int
declare @theGPDB sysname
declare @theBalanceTotal decimal(15,2)
declare @theInvoiceNoStr nvarchar(40) = convert( nvarchar(31), replace( @inInvoiceNo, '''', '''''' ) )

set @theDefaultBranchID =
(
    select
        top (1) Branch.BranchPriKey
    from Branch
    inner join GLControl on Branch.GLCPriKey = GLControl.GLCPriKey
    where isnull( GLControl.ADataBase, ''  ) != ''
)

set @theGPDB = ''
set @theGPDB = dbo.udfGetGPDB( @theDefaultBranchID, @theGPDB )


set @theSQL = N'
    select
        @theBalanceTotal = sum( GPTransactions.Balance )
    from ('

-- Open Trax
set @theSQL = @theSQL + N'
    select
        Balance =
            case
                when RM20101.RMDTYPAL in ( 7, 8, 9 ) then convert( decimal(15,2), -RM20101.CURTRXAM )
                else convert( decimal(15,2), RM20101.CURTRXAM )
            end
    from ' + @theGPDB + 'MSS10000 as MSS10000
    inner join ' + @theGPDB + 'RM20101 as RM20101 on
        RM20101.DOCNUMBR = MSS10000.DOCNUMBR and
        RM20101.RMDTYPAL = MSS10000.DOCTYPE and
        MSS10000.SERIES = 3
    inner join ' + @theGPDB + 'RM00101 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
set @theSQL = @theSQL + N'
    union all
    select
        Balance = case
            when RM10301.RMDTYPAL in ( 7, 8, 9 ) then convert( decimal(15,2), -RM10301.ACCTAMNT )
            else convert( decimal(15,2), RM10301.ACCTAMNT )
        end
    from ' + @theGPDB + 'MSS10000 as MSS10000
    inner join ' + @theGPDB + 'RM10301 as RM10301 on
        RM10301.DOCNUMBR = MSS10000.DOCNUMBR and
        RM10301.RMDTYPAL = MSS10000.DOCTYPE and
        MSS10000.SERIES = 3
    where
        MSS10000.MSS_InvoiceNumber = ''' + @theInvoiceNoStr + ''''

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

set @theParamList = N'@theBalanceTotal decimal(15,2) output'

exec sp_executesql
    @stmt = @theSQL,
    @params = @theParamList,
    @theBalanceTotal = @theBalanceTotal output

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