/**
* Description: After the Apply grid is built up from XLedger data, this procedure is
* called to see if other batches have been posted and imported into XLedger that affect
* the original balance when the apply grid was originally built or this stored proc
* subsequently updated.  When the user navigates to a previous payment, then this
* proc is called to see if the original balance has changed or not on any order
* linked to the specified statement details.  Also, if there are new XLedger invoices,
* this will pick them up and add them to the Apply grid, including any other
* applies that are out there for the new payment.  And, if ome of the existing
* invoices were paid in full and then generated and imported into XLedger, then
* we handle those as well by setting the balance and apply amounts all to zero.
*
* Compare and keep in sync with the spAPPG_XLRunApplyGrid stored proc.
*/
CREATE PROCEDURE [dbo].[UpdateXLApplyGridOriginalBalances]
    @inOrderID int,
    @inStatementDetailID int,
    @inStatementDetailDistID int
as
set nocount on

declare @theOrderID int
declare @thePostingStatus varchar(10)

-- All of the Apply Grid invoices that SHOULD be on this StatementDetailDist.
declare @theStatementDocument table
(
    TempId int identity( 1, 1 ) not null,
    GPSource smallint,
    DocumentNumber varchar(23),
    DocumentDate datetime,
    JournalDate datetime,
    Balance money,
    ATPriKey int,
    StDetailPriKey int,
    StDetailDistPriKey int,
    OriginalAmount money,
    DistributionAdjustmentAmount money,
    ItemCode int,
    CustNumber varchar(15),
    CustomerName varchar(65),
    AccountingCustomerFid int,
    InvoiceNumber varchar(30),
    InvoiceDate datetime,
    ServiceCodePriKey int,
    BillingMinorPriKey int,
    BMinDistPriKey int
)

-- Used to track the StatementDocuments that get updated.
declare @theStatementDocsUpdated table
(
    StatementDocumentID int,
    theStatementDocumentTempId int
)

-- Used to track the StatementDocuments that get inserted.
declare @theStatementDocsInserted table
(
    StatementDocumentID int
)

-- Matches table generated by the select in GetOtherAppliedStatementServiceCodeInvoices_XLedger sp.
declare @theOtherStatementDocumentsSummarized table
(
    DocumentNumber varchar(23),
    OtherApplySum money
)

-- Prevent using isnull repeatedly in this sp in joins and where clauses to improve speed.
set @inStatementDetailID = isnull( @inStatementDetailID, 0 )
set @inStatementDetailDistID = isnull( @inStatementDetailDistID, 0 )

declare @theOkToContinue bit = 1

select
    @thePostingStatus = StatementDetail.[Status]
from StatementDetail
left outer join StatementDetailDist on StatementDetail.SDPriKey = StatementDetailDist.SDPriKey
where
    StatementDetail.SDPriKey = @inStatementDetailID and
    isnull( StatementDetailDist.SDDPriKey, 0 ) = @inStatementDetailDistID

-- If the StatementDetail is posted or voided, there is definitely nothing for us to do.
if( isnull( @thePostingStatus, '' ) in ( 'Posted', 'Voided' ) )
begin
    -- Even though we are looking at @thePostingStatus right now, all of the subsequent queries
    -- will all also look at the StatementDetail's Status to make sure we don't modify something
    -- should the detail become posted at some point while we are running.
    set @theOkToContinue = 0
end

create table #OpenArItem
(
    AcctTransactionFid int not null,
    DocumentNumber varchar(21) not null,
    TransactionType varchar(30) not null,
    AppliedAmt money not null,
    AccountingCustomerFid int not null,
    constraint OpenArItems_ATPrikey unique (AcctTransactionFid)
)

if( @theOkToContinue = 1 and @inOrderID > 0 )
begin
    -- There is a lot of data out there where the MSS_OrdPriKey = 0 so if null, we do not want to match to any of that data.
    set @theOrderID = isnull( @inOrderID, -1 )

    insert into #OpenArItem
    (
        AcctTransactionFid,
        DocumentNumber,
        TransactionType,
        AppliedAmt,
        AccountingCustomerFid
    )
    select
        AcctTransactionFid = AcctTransactions.ATPriKey,
        DocumentNumber = AcctTransactions.DocumentNumber,
        TransactionType = AcctTransactions.TransactionType,
        AppliedAmt = 0,
        AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    from XLedgerJournalEntryBatchItem
    inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join AccountingCustomer on AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid
    -- We only want the non-apply to AcctTransactions.
    left outer join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
    where XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
        AcctTransactions.FullyAppliedTo = 0 and
        AcctTransactions.TransactionType in ( 'A/R Sales / Invoice', 'A/R Debit Memo', 'A/R Credit Memo' ) and
        AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoID is null and
        AcctTransactions.OrdPriKey = @theOrderID

    ;with AppliedToAmounts
    (
        ApplyToDocument,
        ApplyToDocumentType,
        ApplyAmount
    ) as
    (
        select
            AcctTransactionsApplyToInfo.ApplyToDocument,
            AcctTransactionsApplyToInfo.ApplyToDocumentType,
            ApplyAmount = sum( AcctTransactionsApplyToInfo.ApplyAmount )
        from AcctTransactionsApplyToInfo
        inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
        inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactions.ATPriKey and
            XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
        where AcctTransactionsApplyToInfo.FullyAppliedTo = 0 and
            AcctTransactions.OrdPriKey = @theOrderID
        group by
            AcctTransactionsApplyToInfo.ApplyToDocument,
            AcctTransactionsApplyToInfo.ApplyToDocumentType
    ), TargetedAcctTransaction
    (
        ATPriKey,
        TransactionType,
        ApplyAmount
    ) as
    (
        select top 1
            AcctTransactions.ATPriKey,
            AcctTransactions.TransactionType,
            AppliedToAmounts.ApplyAmount
        from AcctTransactions
        inner join AppliedToAmounts on AcctTransactions.DocumentNumber = AppliedToAmounts.ApplyToDocument and
            AcctTransactions.TransactionType = AppliedToAmounts.ApplyToDocumentType
        where AcctTransactions.FullyAppliedTo = 0
        order by AcctTransactions.ATPriKey desc
    )
    update OpenArItem set
        AppliedAmt = case TargetedAcctTransaction.TransactionType
            when 'A/R Credit Memo' then -TargetedAcctTransaction.ApplyAmount
            else TargetedAcctTransaction.ApplyAmount
        end
    from #OpenArItem as OpenArItem
    inner join TargetedAcctTransaction on TargetedAcctTransaction.ATPriKey = OpenArItem.AcctTransactionFid

    insert into @theStatementDocument
    (
        GPSource,
        DocumentNumber,
        DocumentDate,
        JournalDate,
        Balance,
        ATPriKey,
        StDetailPriKey,
        StDetailDistPriKey,
        OriginalAmount,
        DistributionAdjustmentAmount,
        ItemCode,
        CustNumber,
        CustomerName,
        AccountingCustomerFid,
        InvoiceNumber,
        InvoiceDate,
        ServiceCodePriKey,
        BillingMinorPriKey,
        BMinDistPriKey
    )
    select
        GPSource = 1,
        DocumentNumber = OpenArItem.DocumentNumber,
        DocumentDate = convert( datetime, convert( char(12), AcctTransactions.DocumentDate, 101 ), 101 ),
        JournalDate = convert( datetime, convert( char(12), AcctTransactions.JournalDate, 101 ), 101 ),
        Balance = case AcctTransactions.TransactionType
            when 'A/R Credit Memo' then -AcctTransactions.TransactionAmount
            else AcctTransactions.TransactionAmount
        end - OpenArItem.AppliedAmt,
        ATPriKey = OpenArItem.AcctTransactionFid,
        StDetailPriKey = @inStatementDetailID,
        StDetailDistPriKey = case
            when @inStatementDetailDistID = 0 then null
            else @inStatementDetailDistID
        end,
        OriginalAmount = case AcctTransactions.TransactionType
            when 'A/R Credit Memo' then -AcctTransactions.TransactionAmount
            else AcctTransactions.TransactionAmount
        end,
        DistributionAdjustmentAmount = OpenArItem.AppliedAmt,
        ItemCode = AcctTransactions.ICPriKey,
        CustNumber = AccountingCustomer.CustomerNumber,
        CustomerName = AccountingCustomer.[Name],
        AccountingCustomerFid = AccountingCustomer.AccountingCustomerId,
        InvoiceNumber =
            case AcctTransactions.Source
                when 'BillingMinorItem' then
                    case
                        when bmin.IHPriKey is null then bmajIH.InvoiceNumber
                        else bminIH.InvoiceNumber
                    end
                when 'BillingMinorDist' then
                    case
                        when bmind.IHPriKey is null then bmajdIH.InvoiceNumber
                        else bmindIH.InvoiceNumber
                    end
                else null
            end,
        InvoiceDate =
            case AcctTransactions.Source
                when 'BillingMinorItem' then
                    case
                        when bmin.IHPriKey is null then bmajIH.InvoiceDate
                        else bminIH.InvoiceDate
                    end
                when 'BillingMinorDist' then
                    case
                        when bmind.IHPriKey is null then bmajdIH.InvoiceDate
                        else bmindIH.InvoiceDate
                    end
                else null
            end,
        ServiceCodePriKey = ItemCode.ServiceCodePriKey,
        BillingMinorPriKey =
            case AcctTransactions.Source
                when 'BillingMinorItem' then AcctTransactions.SourceRecord
                else null
            end,
        BMinDistPriKey =
            case AcctTransactions.Source
                when 'BillingMinorDist' then AcctTransactions.SourceRecord
                else null
            end
    from #OpenArItem as OpenArItem
    inner join AcctTransactions on AcctTransactions.ATPriKey = OpenArItem.AcctTransactionFid
    inner join AccountingCustomer on AccountingCustomer.AccountingCustomerId = OpenArItem.AccountingCustomerFid
    left outer join ItemCode on ItemCode.ICPriKey = AcctTransactions.ICPriKey
    left outer join BillingMinorItem bmin on bmin.BMinPriKey = AcctTransactions.SourceRecord and
        AcctTransactions.Source = 'BillingMinorItem'
    left outer join InvoicedHeader bminIH on bminIH.IHPriKey = bmin.IHPriKey
    left outer join BillingMajorItem bmaj on bmaj.BMajPriKey = bmin.BMajPriKey
    left outer join InvoicedHeader bmajIH on bmajIH.IHPriKey = bmaj.IHPriKey
    left outer join BillingMinorDist bmindist on bmindist.BMinDistPriKey = AcctTransactions.SourceRecord and
        AcctTransactions.Source = 'BillingMinorDist'
    left outer join BillingMinorItem bmind on bmind.BMinPriKey = bmindist.BMinPriKey
    left outer join InvoicedHeader bmindIH on bmindIH.IHPriKey = bmind.IHPriKey
    left outer join BillingMajorItem bmajd on bmajd.BMajPriKey = bmind.BMajPriKey
    left outer join InvoicedHeader bmajdIH on bmajdIH.IHPriKey = bmajd.IHPriKey

    -- @inOrderID cannot be null if we get here.  Get the totals of
    -- apply amounts sitting on other unposted StatementDocuments attached
    -- to other StatementDetailDists.  @theAcctTrxnBatchMustBePosted tells
    -- the GetOtherAppliedStatementServiceCodeInvoices SP to look at the
    -- AcctTransactions' associated XLedgerJournalBatchItem and make sure
    -- that the AcctTransaction is "posted in XLedger".
    declare @theAcctTrxnBatchMustBePosted bit = 0    -- Use XLedger logic.
    insert into @theOtherStatementDocumentsSummarized
    (
        DocumentNumber,
        OtherApplySum
    )
    exec GetOtherAppliedStatementServiceCodeInvoices
        @inStatementDetailID = @inStatementDetailID,
        @inStatementDetailDistID = @inStatementDetailDistID,
        @inOrderId = @inOrderID,
        @inAcctTrxnBatchMustBePosted = @theAcctTrxnBatchMustBePosted

    update StatementDocument set
        Balance = theStatementDocument.Balance,
        DistributionAdjustmentAmount = theStatementDocument.DistributionAdjustmentAmount,
        OtherApplyAmount = isnull( theOtherStatementDocumentsSummarized.OtherApplySum, 0 ),
        ApplyAmount = case
            when StatementDocument.ApplyAmount = 0 then 0
            when theStatementDocument.Balance <= 0 then 0
            when theOtherStatementDocumentsSummarized.OtherApplySum is null and StatementDocument.AdjustAmount = 0 then case
                when theStatementDocument.Balance < StatementDocument.ApplyAmount then theStatementDocument.Balance
                else StatementDocument.ApplyAmount
                end
            when theStatementDocument.Balance - isnull( theOtherStatementDocumentsSummarized.OtherApplySum, 0 ) < StatementDocument.ApplyAmount then
                theStatementDocument.Balance - isnull( theOtherStatementDocumentsSummarized.OtherApplySum, 0 )
            else StatementDocument.ApplyAmount
        end,
        -- You can't upward adjust payments if there are other apply amounts out there.
        AdjustAmount = case
            when StatementDocument.Adjustment = 0 then 0
            when theOtherStatementDocumentsSummarized.OtherApplySum is not null then 0
            else StatementDocument.AdjustAmount
        end,
        Adjustment = case
            when StatementDocument.Adjustment = 0 then 0
            when theOtherStatementDocumentsSummarized.OtherApplySum is not null then 0
            else StatementDocument.Adjustment
        end
    output inserted.SDPriKey, theStatementDocument.TempId
    into @theStatementDocsUpdated( StatementDocumentID, theStatementDocumentTempId )
    from @theStatementDocument as theStatementDocument
    inner join StatementDocument on
        StatementDocument.StDetailPriKey = theStatementDocument.StDetailPriKey and
        StatementDocument.StDetailDistPriKey = theStatementDocument.StDetailDistPriKey and
        isnull( StatementDocument.ATPriKey, 0 ) = isnull( theStatementDocument.ATPriKey, 0 ) and
        StatementDocument.DocumentNumber = theStatementDocument.DocumentNumber and
        StatementDocument.GPSource = theStatementDocument.GPSource
    inner join StatementDetail on StatementDetail.SDPriKey = StatementDocument.StDetailPriKey
    left outer join @theOtherStatementDocumentsSummarized as theOtherStatementDocumentsSummarized on
        theOtherStatementDocumentsSummarized.DocumentNumber = StatementDocument.DocumentNumber
    where
        StatementDetail.[Status] not in ( 'Posted', 'Voided' )

    -- Remove the temp XLedger StatementDocument records we just updated.
    delete from theStatementDocument
    from @theStatementDocument as theStatementDocument
    inner join @theStatementDocsUpdated as theStatementDocsUpdated on theStatementDocsUpdated.theStatementDocumentTempId = theStatementDocument.TempId

    -- Are there any new XLedger invoices that we did not have the last time this payment was visited or was originally built?
    if( exists( select top 1 1 from @theStatementDocument ) )
    begin
        insert into StatementDocument
        (
            GPSource,
            DocumentNumber,
            DocumentDate,
            JournalDate,
            Balance,
            ATPriKey,
            StDetailPriKey,
            StDetailDistPriKey,
            OrdPriKey,
            OriginalAmount,
            DistributionAdjustmentAmount,
            ItemCode,
            CustNumber,
            CustomerName,
            AccountingCustomerFid,
            InvoiceNumber,
            InvoiceDate,
            ServiceCodePriKey,
            BillingMinorPriKey,
            BMinDistPriKey,
            OtherApplyAmount
        )
        output inserted.SDPriKey
        into @theStatementDocsInserted ( StatementDocumentID )
        select
            GPSource = theStatementDocument.GPSource,
            DocumentNumber = theStatementDocument.DocumentNumber,
            DocumentDate = theStatementDocument.DocumentDate,
            JournalDate = theStatementDocument.JournalDate,
            Balance = theStatementDocument.Balance,
            ATPriKey = theStatementDocument.ATPriKey,
            StDetailPriKey = theStatementDocument.StDetailPriKey,
            StDetailDistPriKey = theStatementDocument.StDetailDistPriKey,
            OrdPriKey = @inOrderID,
            OriginalAmount = theStatementDocument.OriginalAmount,
            DistributionAdjustmentAmount = theStatementDocument.DistributionAdjustmentAmount,
            ItemCode = theStatementDocument.ItemCode,
            CustNumber = theStatementDocument.CustNumber,
            CustomerName = theStatementDocument.CustomerName,
            AccountingCustomerFid = theStatementDocument.AccountingCustomerFid,
            InvoiceNumber = theStatementDocument.InvoiceNumber,
            InvoiceDate = theStatementDocument.InvoiceDate,
            ServiceCodePriKey = theStatementDocument.ServiceCodePriKey,
            BillingMinorPriKey = theStatementDocument.BillingMinorPriKey,
            BMinDistPriKey = theStatementDocument.BMinDistPriKey,
            OtherApplyAmount = isnull( theOtherStatementDocumentsSummarized.OtherApplySum, 0 )
        from @theStatementDocument as theStatementDocument
        inner join StatementDetail on StatementDetail.SDPriKey = @inStatementDetailID
        left outer join @theOtherStatementDocumentsSummarized as theOtherStatementDocumentsSummarized on
            theOtherStatementDocumentsSummarized.DocumentNumber = theStatementDocument.DocumentNumber
        where
            StatementDetail.[Status] not in ( 'Posted', 'Voided' )

        -- Determine the Branch for all StatementDocuments we just inserted.
        update StatementDocument set
            BranchFID = case AcctTransactions.Source
                when 'BillingMinorItem' then BillingMinorItem.ARBranchFID
                when 'BillingMinorDist' then BillingMinorDist.BranchFID
                when 'StatementChargeBack' then
                (
                    select isnull( StatementDetailDist.UnappliedBranchFID, StatementDetail.UnappliedBranchFID )
                    from StatementDetail
                    inner join StatementChargeBack on StatementChargeBack.SDPriKey = StatementDetail.SDPriKey
                    left outer join StatementDetailDist on StatementChargeBack.SDDPrikey = StatementDetailDist.SDDPriKey
                    where StatementChargeBack.SCBPriKey = AcctTransactions.SourceRecord
                )
                when 'StatementDetail' then
                (
                    select StatementDetail.BranchPriKey
                    from StatementDetail
                    where StatementDetail.SDPriKey = AcctTransactions.SourceRecord
                )
                when 'StatementDetailDist' then
                (
                    select StatementDetailDist.UnappliedBranchFID
                    from StatementDetailDist
                    where StatementDetailDist.SDDPriKey = AcctTransactions.SourceRecord
                )
                when 'StatementDocument' then
                (
                    select StatementDocument.BranchFID
                    from StatementDocument
                    where StatementDocument.SDPriKey = AcctTransactions.SourceRecord
                )
                when 'StatementGLAllocation' then
                (
                    select StatementGLAllocation.BranchPriKey
                    from StatementGLAllocation
                    where StatementGLAllocation.StatementGLAllocationID = AcctTransactions.SourceRecord
                )
                when 'ClaimHandlingRevenue' then
                (
                    select ClaimHandlingRevenue.BranchFID
                    from ClaimHandlingRevenue
                    where ClaimHandlingRevenue.ClaimHandlingRevenueID = AcctTransactions.SourceRecord
                )
                when 'ClaimItemDueFrom' then
                (
                    select Claim.BranchFID
                    from ClaimItemDueFrom
                    inner join ClaimItem on ClaimItemDueFrom.ClaimItemFID = ClaimItem.ClaimItemID
                    inner join Claim on ClaimItem.ClaimFID = Claim.ClaimID
                    where  AcctTransactions.SourceRecord = ClaimItemDueFrom.ClaimItemDueFromID
                )
                when 'ClaimLiabilityItem' then
                (
                    select isnull( ClaimLiability.BranchFID, Claim.BranchFID )
                    from ClaimLiabilityItem
                    inner join ClaimLiability on ClaimLiabilityItem.ClaimLiabilityFID = ClaimLiability.ClaimLiabilityID
                    inner join Claim on ClaimLiability.ClaimFID = Claim.ClaimID
                    where  AcctTransactions.SourceRecord = ClaimLiabilityItem.ClaimLiabilityItemID
                )
                when 'ClaimSettlement' then
                (
                    select Claim.BranchFID
                    from ClaimSettlement
                    inner join Claim on ClaimSettlement.ClaimFID = Claim.ClaimID
                    where AcctTransactions.SourceRecord = ClaimSettlement.ClaimSettlementID
                )
                when 'CommissionedDetail' then
                (
                    select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                    from CommissionedDetail
                    left outer join BillingMinorDist on CommissionedDetail.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                    left outer join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                    where AcctTransactions.SourceRecord = CommissionedDetail.CDPriKey
                )
                when 'SplitCommissions' then
                (
                    select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                    from SplitCommissions
                    inner join CommissionedDetail on SplitCommissions.CDPriKey = CommissionedDetail.CDPriKey
                    left outer join BillingMinorDist on CommissionedDetail.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                    left outer join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                    where AcctTransactions.SourceRecord = SplitCommissions.SCPriKey
                )
                when 'ThirdParty' then
                (
                    select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                    from ThirdParty
                    left outer join BillingMinorDist on ThirdParty.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                    left outer join BillingMinorItem on ThirdParty.BMinPriKey = BillingMinorItem.BMinPriKey
                    where AcctTransactions.SourceRecord = ThirdParty.TPPriKey
                )
                else StatementDetailDist.BranchPriKey
            end
        from @theStatementDocsInserted as theStatementDocsInserted
        inner join StatementDocument on StatementDocument.SDPriKey = theStatementDocsInserted.StatementDocumentID
        inner join StatementDetail on StatementDetail.SDPriKey = StatementDocument.StDetailPriKey
        left outer join AcctTransactions on StatementDocument.ATPriKey = AcctTransactions.ATPriKey
        left outer join BillingMinorItem on BillingMinorItem.BMinPriKey = StatementDocument.BillingMinorPriKey
        left outer join BillingMinorDist on BillingMinorDist.BMinDistPriKey = StatementDocument.BMinDistPriKey
        left outer join StatementDetailDist on StatementDetailDist.SDDPriKey = StatementDocument.StDetailDistPriKey
        where
            StatementDetail.[Status] not in ( 'Posted', 'Voided' )

    end

    -- OK, we updated all of the StatementDocuments that existed before that still have open balances.
    -- And we inserted any new XLedger invoices that were added after the last time this Apply Grid was updated.
    -- Now, we need to deal with any StatementDocuments on this payment that got fully paid by other
    -- payments and where they were posted and imported into XLedger (so they did not show up in the insert
    -- into @theStatementDocument).  Since we know all of the StatementDocument prikeys that we updated
    -- (@theStatementDocsUpdated) and that we inserted (@theStatementDocsInserted), any other remaining
    -- StatementDocuments on this payment are now "paid in full" so they have a 0.00 current balance.
    ;with StatementDocsHandled( StatementDocumentID ) as
    (
        select StatementDocumentID
        from @theStatementDocsUpdated
        union all
        select StatementDocumentID
        from @theStatementDocsInserted
    )
    update StatementDocument set
        Balance = 0,
        DistributionAdjustmentAmount = 0,
        OtherApplyAmount = 0,
        ApplyAmount = 0,
        AdjustAmount = 0,
        Adjustment = 0
    from StatementDocument
    inner join StatementDetail on StatementDetail.SDPriKey = StatementDocument.StDetailPriKey
    left outer join StatementDocsHandled on StatementDocsHandled.StatementDocumentID = StatementDocument.SDPriKey
    where StatementDocument.StDetailPriKey = @inStatementDetailID and
        StatementDocument.StDetailDistPriKey = @inStatementDetailDistID and
        StatementDetail.[Status] not in ( 'Posted', 'Voided' ) and
        StatementDocsHandled.StatementDocumentID is null

end

drop table #OpenArItem
GO
GRANT EXECUTE ON  [dbo].[UpdateXLApplyGridOriginalBalances] TO [MssExec]
GO