[dbo].[GetSalesCommissionInvoiceArBalance]
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 ('
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'
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 + ''''
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