/**
*    Do not call this SP directly.  Instead call CustomerStatementGenerate_Synonym and that knows
*    which SP to call based upon the accounting system in use.  This SP is used for XLedger based
*    accounting systems and CustomerStatementGenerate_Legacy is used by legacy (GP) accounting systems.
*
*    @param @inBranchID - Branch ID used to lookup the Great Plains database
*    @param @inBranchList - List of BranchPriKey's to be used in the query filter
*    @param @inCustomerClassList - List of Customer Class descriptions to be used in the query filter
*
*    @param @inCycleList - List of Statement Cycle primary keys to be used in the query filter.
*        SPECIAL NOTE: XLedger doesn't have a StatementCycleID type field at the customer level.  So,
*        for now, we are hiding that field selection in the UI.  We may or may not get something in
*        this parameter, but for now, will just assume it is always empty.  Maybe next year, we'll
*        tackle adding that back in and finding a way to have XLedger or Admin be able to set it up
*        at the customer level.  Search for XLEDGER_HAS_NO_STATEMENT_CYCLES_FOR_CUSTOMERS for changes.
*        However, we'll store the CustomerStatementStatementCycle prikey value for monthly in the
*        CustomerStatement.StatementCycle since it is a not null column.  Zara says she doesn't think
*        any existing customer has ever used this feature.
*
*    @param @inCustomerNumber - Customer Number(if any) to be used in the query filter
*    @param @inCustomerName - Customer Name(if any) to be used in the query filter
*    @param @inStatementDate - Statement Date chosen by the user
*    @param @inSummarizeToDate - Date to summarize the data up to
*    @param @inCutOffDate - End date to be used in the query filter
*    @param @inMessage - Message to show on the customer statement
*    @param @inSuppressZeroBalancesFlag - Don't show any orders with a zero balance
*    @param @inExcludeCreditBalancesFlag - Don't create any statements that have an overall credit
*    @param @inPastDueBalancesFlag - Include orders that have only a past due balance (BBF)
*    @param @inNonInvoicedItemsFlag - Include non-invoiced items on the statement (Payments should always be included)
*    @param @inExcludeUnappliedPaymentsFlag - Exclude any payments that have not been applied to anything (not used for XLedger)
*    @param @inShowDetail - Can be used to suppress items with an InvoicedHeader.IHPriKey and with an AcctTransactions.InvoiceNumber.
*    @param @inGeneratedBySysUserFID - The sysuser primary key of the user generating the statement
*    @param @inGeneratedBySysUserBranchFID - To go away
*    @param @inCustomerStatementProfileID - Customer statement profile primary key
*    @param inCustomerStatementTitleID - Custmer statement title primary key
*    @param @outStatementRunID - The primary key id of the statement run that is generated
*    @param @outErrorCode - An error code indicating a problem with the run if non-zero
*    @inGenerateUnitTestOutput - Set by unit tests to output the temp tables that are generated.  If
*        using this parameter, you do need to un-comment out the queries to have them produce data.
*/

create procedure [dbo].[CustomerStatementGenerate_XLedger]
    @inBranchID int,
    @inBranchList varchar(8000),
    @inClassList varchar(8000),
    @inCycleList varchar(8000),
    @inCustomerNumber varchar(15),
    @inCustomerName varchar(65),
    @inStatementDate datetime,
    @inSummarizeToDate datetime,
    @inCutOffDate datetime,
    @inUseDocDate bit,
    @inMessage varchar(128),
    @inSuppressZeroBalancesFlag bit,
    @inExcludeCreditBalancesFlag bit,
    @inPastDueBalancesFlag bit,
    @inNonInvoicedItemsFlag bit,
    @inExcludeUnappliedPaymentsFlag bit,
    @inShowDetail bit,
    @inGeneratedBySysUserFID int,
    @inGeneratedBySysUserBranchFID int,
    @inCustomerStatementProfileID int,
    @inCustomerStatementTitleID int,
    @inHeaderBranchAddressFID int,
    @inHeaderVanlineAddressFID int,
    @inRemittanceBranchAddressFID int,
    @inRemittanceVanlineAddressFID int,
    @outStatementRunID int output,
    @outErrorCode int output,
    @inGenerateUnitTestOutput bit = null
as

set nocount on

-- Local Variables
declare @theOpenDateString varchar(512)
declare @theOrderBalanceDateString varchar(4096)
declare @theSummarizeDateString varchar(10)
declare @theCutOffDateString varchar(10)
declare @theStatementDateString varchar(64)
declare @theBBFPaymentDocTypeID int
declare @theCustomerClassFilter varchar(4096)
declare @theStatementCycleFilter varchar(4096)
declare @theBranchFilter varchar(4096)
declare @theCustomerNumberFilter varchar(4096)
declare @theTempErrorCode int

-- Initialize the error code to zero
set @outErrorCode = 0
set @theTempErrorCode = 0
set @inGenerateUnitTestOutput = isnull( @inGenerateUnitTestOutput, 0 )

-- Temporary Tables
create table #Detail
(
    DetailID int identity (1, 1) not null,
    CustomerStatementDocumentTypeFID integer not null,
    DocumentNumber varchar(21) not null,
    Description varchar(128) not null,
    Amount money not null,
    DocumentDate datetime not null,
    Visible bit not null default( 0 ),
    CustomerNumber varchar(15) not null,
    OrderNumber varchar(23) not null default(''),
    ShipperName varchar(64) not null default(''),
    InvoiceNumber varchar(30) not null,
    InvoiceDate datetime null,
    Status varchar(32) not null default(''),
    ApplyToDocumentDate datetime null,
    ApplyToDocumentType integer null,
    ApplyToDocument varchar(21) null default(''),
    ApplyToInvoiceNumber varchar(30) null default(''),
    ApplyToInvoiceDate datetime null
)

create table #OrderBalance
(
    Description varchar(128) not null default(''),
    OrderNumber varchar(23) not null default(''),
    ShipperName varchar(64) not null default(''),
    BalanceForwardDate datetime not null,
    CustomerNumber varchar(15) not null,
    CustomerStatementDocumentTypeFID int not null,
    Amount money not null,
    Status varchar(32) not null default(''),
    ApplyToDocumentDate datetime null,
    ApplyToInvoiceDate datetime null,
    ApplyToInvoiceNumber varchar(30) null default('')
)

-- Get the primary key of the document type that refers to a BBF
set @theBBFPaymentDocTypeID =
(
    select CustomerStatementDocumentTypeID
    from CustomerStatementDocumentType
    where DocumentType = 'BBF'
)

-- Create a string that holds the date to be used based on the input parameter
if( 1 = @inUseDocDate )
begin
    set @theOpenDateString = 'AcctTransactions.DocumentDate'
end
else
begin
    set @theOpenDateString = 'case when isnull( AcctTransactions.InvoiceDate, ''1900-01-01'' ) = ''1900-01-01'' then AcctTransactions.DocumentDate else AcctTransactions.InvoiceDate end'
end

-- Get the default localized date format
declare @theDateFormat int
set @theDateFormat = dbo.GetDefaultDateFormat()

-- Convert the incoming dates into strings without a time element
set @theSummarizeDateString = convert( varchar(10), @inSummarizeToDate, @theDateFormat )
set @theCutOffDateString = convert( varchar(10), @inCutOffDate, @theDateFormat )
set @theStatementDateString = convert( varchar(10), @inStatementDate, @theDateFormat )

-- Wrap the entire insertion of records into the customer statement tables in a transaction
set transaction isolation level read uncommitted
begin transaction

-- Create the StatementRun header record
insert into CustomerStatementRun
(
    CustomerStatementProfileFID,
    CustomerStatementTitleFID,
    CustomerNumber,
    CustomerName,
    StatementDate,
    SummarizeToDate,
    CutOffDate,
    Message,
    SuppressZeroBalancesFlag,
    ExcludeCreditBalancesFlag,
    PastDueBalancesFlag,
    NonInvoicedItemsFlag,
    ExcludeUnappliedPaymentsFlag,
    ShowInvoiceDetailsFlag,
    GeneratedDate,
    GeneratedBySysUserFID,
    GeneratedBySysUserBranchFID,
    HeaderBranchAddressFID,
    HeaderVanlineAddressFID,
    RemittanceBranchAddressFID,
    RemittanceVanlineAddressFID
)
select
    CustomerStatementProfileFID = @inCustomerStatementProfileID,
    CustomerStatementTitleFID = @inCustomerStatementTitleID,
    CustomerNumber = @inCustomerNumber,
    CustomerName = @inCustomerName,
    StatementDate = @theStatementDateString,
    SummarizeToDate = @theSummarizeDateString,
    CutOffDate = @theCutOffDateString,
    Message = @inMessage,
    SuppressZeroBalancesFlag = @inSuppressZeroBalancesFlag,
    ExcludeCreditBalancesFlag = @inExcludeCreditBalancesFlag,
    PastDueBalancesFlag = @inPastDueBalancesFlag,
    NonInvoicedItemsFlag = @inNonInvoicedItemsFlag,
    ExcludeUnappliedPaymentsFlag = @inExcludeUnappliedPaymentsFlag,
    ShowInvoiceDetailsFlag = @inShowDetail,
    GeneratedDate = getdate(),
    GeneratedBySysUserFID = @inGeneratedBySysUserFID,
    GeneratedBySysUserBranchFID = @inGeneratedBySysUserBranchFID,
    HeaderBranchAddressFID = @inHeaderBranchAddressFID,
    HeaderVanlineAddressFID = @inHeaderVanlineAddressFID,
    RemittanceBranchAddressFID = @inRemittanceBranchAddressFID,
    RemittanceVanlineAddressFID = @inRemittanceVanlineAddressFID

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Get the newly inserted record id
set @outStatementRunID = scope_identity()

-- Parse the list of branches, and insert into the Customer Statement branches table
exec CustomerStatementInsertRunBranches @outStatementRunID, @inBranchList

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Parse the list of Customer Classes and insert into the customer class table
exec CustomerStatementInsertRunClasses @outStatementRunID, @inClassList

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Parse the list of statement cycles and insert into the Customer Statement cycle table
/* XLEDGER_HAS_NO_STATEMENT_CYCLES_FOR_CUSTOMERS
exec CustomerStatementInsertRunCycles @outStatementRunID, @inCycleList

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end
*/

-- Check the errors, and rollback if an error was encountered, otherwise commit
if( 0 != @outErrorCode )
begin
    rollback transaction
end
else
begin
    commit transaction
end

-- Create a string that will be used to filter the customer class
-- If the Customer number is set, then skip the customer class filter because the statement is for a specific customer.
-- This fixes a bug with the wrong customer class being set with a customer.  A customer has only one customer class.
if( '' != @inClassList and '' = @inCustomerNumber )
begin
    set @theCustomerClassFilter = 'inner join CustomerStatementRunCustomerClass on CustomerStatementRunCustomerClass.AccountingCustomerClassFid = AccountingCustomer.AccountingCustomerClassFid and CustomerStatementRunCustomerClass.CustomerStatementRunFID = ' + rtrim( str( @outStatementRunID ) )
end
else
begin
    set @theCustomerClassFilter = ''
end

-- Create a string that will be used to filter the statement cycle
-- XLEDGER_HAS_NO_STATEMENT_CYCLES_FOR_CUSTOMERS
set @theStatementCycleFilter = ''
/*
if '' != @inCycleList
begin
    set @theStatementCycleFilter = 'inner join CustomerStatementRunStatementCycle on CustomerStatementRunStatementCycle.StatementCycleID = RM00101.STMTCYCL and CustomerStatementRunStatementCycle.CustomerStatementRunFID = ' + rtrim( str( @outStatementRunID ) )
end
else
begin
    set @theStatementCycleFilter = ''
end
*/

-- Create a string that will be used to filter the branches
if( '' != @inBranchList )
begin
    set @theBranchFilter = 'inner join CustomerStatementRunBranches on CustomerStatementRunBranches.BranchID = AcctTransactions.BranchID and CustomerStatementRunBranches.CustomerStatementRunFID = ' + rtrim( str( @outStatementRunID ) )
end
else
begin
    set @theBranchFilter = ''
end

-- Create a string that will be used to filter the customer number
if( '' != @inCustomerNumber )
begin
    set @theCustomerNumberFilter = ' and ' + dbo.QuoteString( @inCustomerNumber ) + ' = AccountingCustomer.CustomerNumber '
end
else
begin
    set @theCustomerNumberFilter = ''
end

-- Current #1A ( Non-Apply To AcctTransactions )
--        Get all Open transactions with a date in the given date range and do not exist as an ApplyFrom document.
--        Use the Original Transaction Amount.
--        Sometimes, AcctTransactions.OrdPriKey is negative when it points to a StatementDetail.SDPriKey
--        non-apply tos
exec
(
    'insert into #Detail
    (
        CustomerStatementDocumentTypeFID,
        DocumentNumber,
        Description,
        Amount,
        DocumentDate,
        Visible,
        CustomerNumber,
        OrderNumber,
        ShipperName,
        InvoiceNumber,
        InvoiceDate,
        Status
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        DocumentNumber = AcctTransactions.DocumentNumber,
        Description = AcctTransactions.ItemDescription,
        Amount = case AcctTransactions.TransactionType
            when ''A/R Credit Memo'' then -AcctTransactions.TransactionAmount
            when ''A/R Cash Receipt'' then -AcctTransactions.TransactionAmount
            else AcctTransactions.TransactionAmount
        end,
        DocumentDate = AcctTransactions.DocumentDate,
        Visible = case
            when isnull( case
                when LastInvoicedHeader.IHPriKey is null then null
                else AcctTransactions.InvoiceNumber
            end, '''' ) = '''' then 1
            else ' + @inShowDetail + '
        end,
        CustomerNumber = AccountingCustomer.CustomerNumber,
        OrderNumber = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when rtrim( AcctTransactions.OrderNo ) = '''' then null
            else rtrim( AcctTransactions.OrderNo )
        end, '''' ),
        ShipperName = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when AcctTransactions.ShipperName = '''' then null
            else AcctTransactions.ShipperName
        end, '''' ),
        InvoiceNumber = isnull( case
            when LastInvoicedHeader.IHPriKey is null then null
            else AcctTransactions.InvoiceNumber
        end, '''' ),
        InvoiceDate = case
            when case
                when LastInvoicedHeader.IHPriKey is null then null
                else AcctTransactions.InvoiceDate
            end is null then AcctTransactions.DocumentDate
            else AcctTransactions.InvoiceDate
        end,
        Status = case AcctTransactions.TransactionType
            when ''A/R Cash Receipt'' then ''Open''        --''ApplyTo''
            else ''Open''
        end
    from XLedgerJournalEntryBatchItem
    inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
    left outer join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join AccountingCustomer on
        AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid' + @theCustomerNumberFilter + ' ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = case AcctTransactions.TransactionType
        ' +
        --when '' then 0                            -- Reserved for balance carried forward records
        'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
        --when '' then 2                            -- Reserved for scheduled payments
        'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
        --when '''' then 4                            -- Finance Charge
        --when '''' then 5                            -- Service / Repair
        --when '''' then 6                            -- Warranty
        'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
        --when '''' then 8                            -- Return
        'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
        'else 0
    end
    outer apply (
        select top 1
            InvoicedHeader.IHPriKey
        from InvoicedHeader
        where InvoicedHeader.InvoiceNumber = AcctTransactions.InvoiceNumber
        order by InvoicedHeader.IHPriKey desc
    ) as LastInvoicedHeader

    where XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
        AcctTransactions.TransactionType in ( ''A/R Sales / Invoice'', ''A/R Debit Memo'', ''A/R Credit Memo'', ''A/R Cash Receipt'' ) and
        AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoId is null and
        AcctTransactions.CustomerNumber != ''ZZZZZ'' and
    ( ' +
        @theOpenDateString + ' > ''' + @theSummarizeDateString + ''' and ' +
        @theOpenDateString + ' <= ''' + @theCutOffDateString + ''' and
        (
            ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
            AcctTransactions.TransactionType != ''A/R Cash Receipt'' --RM20101.RMDTYPAL != 9
        )
    )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    -- Results Table #1
--    select 'Insert #Detail 1A'
--    select * from #Detail
--end

-- Current #1B ( Apply To AcctTransactions )
--        Sometimes, AcctTransactions.OrdPriKey is negative when it points to a StatementDetail.SDPriKey
--        apply tos
exec
(
    'insert into #Detail
    (
        CustomerStatementDocumentTypeFID,
        DocumentNumber,
        Description,
        Amount,
        DocumentDate,
        Visible,
        CustomerNumber,
        OrderNumber,
        ShipperName,
        InvoiceNumber,
        InvoiceDate,
        Status,
        ApplyToDocumentDate,
        ApplyToDocumentType,
        ApplyToDocument,
        ApplyToInvoiceNumber,
        ApplyToInvoiceDate
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        DocumentNumber = AcctTransactions.DocumentNumber,
        Description = AcctTransactions.ItemDescription,
        Amount = -AcctTransactionsApplyToInfo.ApplyAmount,
        DocumentDate= AcctTransactions.DocumentDate,
        Visible = case
            when isnull( case
                when LastInvoicedHeader.IHPriKey is null then null
                else AcctTransactions.InvoiceNumber
            end, '''' ) = '''' then 1
            else ' + @inShowDetail + '
        end,
        CustomerNumber = AccountingCustomer.CustomerNumber,
        OrderNumber = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when rtrim( AcctTransactions.OrderNo ) = '''' then null
            else rtrim( AcctTransactions.OrderNo )
        end, '''' ),
        ShipperName = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when AcctTransactions.ShipperName = '''' then null
            else AcctTransactions.ShipperName
        end, '''' ),
        InvoiceNumber = '''',
        InvoiceDate = AcctTransactions.DocumentDate,
        Status = ''Open ApplyTo'',
        ApplyToDocumentDate = AppliedToDocument.DocumentDate,
        ApplyToDocumentType = case AcctTransactionsApplyToInfo.ApplyToDocumentType ' +
            --when '' then 0                            -- Reserved for balance carried forward records
            'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
            --when '' then 2                            -- Reserved for scheduled payments
            'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
            --when '''' then 4                            -- Finance Charge
            --when '''' then 5                            -- Service / Repair
            --when '''' then 6                            -- Warranty
            'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
            --when '''' then 8                            -- Return
            'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
            'else 0
        end,
        ApplyToDocument = AcctTransactionsApplyToInfo.ApplyToDocument,
        ApplyToInvoiceNumber = isnull( AppliedToDocument.InvoiceNumber, '''' ),
        ApplyToInvoiceDate = case
            when isnull( AppliedToDocument.InvoiceDate, ''1900-01-01'' ) = ''1900-01-01'' then AppliedToDocument.DocumentDate
            else AppliedToDocument.InvoiceDate
        end
    from XLedgerJournalEntryBatchItem
    inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
    inner join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join AccountingCustomer on
        AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid' + @theCustomerNumberFilter + ' ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = case AcctTransactions.TransactionType
        ' +
        --when '' then 0                            -- Reserved for balance carried forward records
        'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
        --when '' then 2                            -- Reserved for scheduled payments
        'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
        --when '''' then 4                            -- Finance Charge
        --when '''' then 5                            -- Service / Repair
        --when '''' then 6                            -- Warranty
        'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
        --when '''' then 8                            -- Return
        'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
        'else 0
    end
    outer apply (
        select top 1
            InvoiceDate = ApplyToTrxn.InvoiceDate,
            InvoiceNumber = ApplyToTrxn.InvoiceNumber,
            DocumentDate = ApplyToTrxn.DocumentDate,
            OrdPriKey = ApplyToTrxn.OrdPriKey
        from AcctTransactions as ApplyToTrxn
        where ApplyToTrxn.DocumentNumber = AcctTransactionsApplyToInfo.ApplyToDocument
        order by ApplyToTrxn.ATPriKey desc
    ) as AppliedToDocument
    outer apply (
        select top 1
            InvoicedHeader.IHPriKey
        from InvoicedHeader
        where InvoicedHeader.InvoiceNumber = AppliedToDocument.InvoiceNumber
        order by InvoicedHeader.IHPriKey desc
    ) as LastInvoicedHeader

    where XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
        AcctTransactions.TransactionType in ( ''A/R Sales / Invoice'', ''A/R Debit Memo'', ''A/R Credit Memo'', ''A/R Cash Receipt'' ) and
        AcctTransactions.CustomerNumber != ''ZZZZZ'' and
    ( ' +
        @theOpenDateString + ' > ''' + @theSummarizeDateString + ''' and ' +
        @theOpenDateString + ' <= ''' + @theCutOffDateString + ''' ' +
        -- These are all applied so none of them can be unapplied.
        --and (
        --    ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
        --    AcctTransactions.TransactionType != ''A/R Cash Receipt'' --RM20101.RMDTYPAL != 9
        --)
    ' )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #Detail 1B'
--    select * from #Detail
--end

-- BBF #1 ( Non-Apply To AcctTransactions )
--        Get all Open transactions with a non-zero Current Transaction Amount prior to the SummarizeTo date
--        Sometimes, AcctTransactions.OrdPriKey is negative when it points to a StatementDetail.SDPriKey
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when rtrim( AcctTransactions.OrderNo ) = '''' then null
            else rtrim( AcctTransactions.OrderNo )
        end, '''' ),
        ShipperName = isnull( case
            when AcctTransactions.OrdPriKey < 0 then null
            when AcctTransactions.ShipperName = '''' then null
            else AcctTransactions.ShipperName
        end, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount = case AcctTransactions.TransactionType
            when ''A/R Credit Memo'' then -AcctTransactions.TransactionAmount
            when ''A/R Cash Receipt'' then -AcctTransactions.TransactionAmount
            else AcctTransactions.TransactionAmount - isnull( ExistingPayments.TotalAmount, 0 )
        end,
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = AccountingCustomer.CustomerNumber,
        Status = case AcctTransactions.TransactionType
            when ''A/R Cash Receipt'' then ''Open''
            else ''Open''
        end,
        ApplyToDocumentDate = AcctTransactions.DocumentDate,
        ApplyToInvoiceDate = isnull( AcctTransactions.InvoiceDate, AcctTransactions.DocumentDate ),
        ApplyToInvoiceNumber = ''''
    from XLedgerJournalEntryBatchItem
    inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
    left outer join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join AccountingCustomer on
        AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid' + @theCustomerNumberFilter + ' ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = case AcctTransactions.TransactionType
        ' +
        --when '' then 0                            -- Reserved for balance carried forward records
        'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
        --when '' then 2                            -- Reserved for scheduled payments
        'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
        --when '''' then 4                            -- Finance Charge
        --when '''' then 5                            -- Service / Repair
        --when '''' then 6                            -- Warranty
        'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
        --when '''' then 8                            -- Return
        'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
        'else 0
    end
    outer apply (
        select
            TotalAmount = sum( AppliedPayments.ApplyAmount )
        from AcctTransactionsApplyToInfo as AppliedPayments
        inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AppliedPayments.AcctTransactionsFID
        where AppliedPayments.ApplyToDocument = AcctTransactions.DocumentNumber and
            XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
    ) as ExistingPayments
    where XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
        AcctTransactions.TransactionType in ( ''A/R Sales / Invoice'', ''A/R Debit Memo'', ''A/R Credit Memo'', ''A/R Cash Receipt'' ) and
        AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoId is null and
        AcctTransactions.CustomerNumber != ''ZZZZZ'' and
    (
        ' + @theOpenDateString + ' <= ''' + @theSummarizeDateString + ''' and
        (
            ' + @inNonInvoicedItemsFlag + ' = 1 or
            isnull( case AcctTransactions.TransactionType
            ' +
            --when '' then 0                                                        -- Reserved for balance carried forward records
            'when ''A/R Sales / Invoice'' then AcctTransactions.InvoiceNumber ' +    -- Sale / Invoice
            --when '' then 2                                                        -- Reserved for scheduled payments
            'when ''A/R Debit Memo'' then AcctTransactions.InvoiceNumber ' +        -- Debit Memo
            --when '''' then 4                                                        -- Finance Charge
            --when '''' then 5                                                        -- Service / Repair
            --when '''' then 6                                                        -- Warranty
            'when ''A/R Credit Memo'' then AcctTransactions.InvoiceNumber ' +        -- Credit Memo
            --when '''' then 8                                                        -- Return
            'when ''A/R Cash Receipt'' then null ' +                                -- Payment   was null
            'else null
                end, '''' ) != ''''
        )
        and (
            ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
            AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoID is not null or
            AcctTransactions.TransactionType != ''A/R Cash Receipt''
        )
    )
    group by
        CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        AcctTransactions.OrderNo,
        AcctTransactions.ShipperName,
        AcctTransactions.TransactionType,
        AcctTransactions.TransactionAmount,
        AcctTransactions.ATPriKey,
        AccountingCustomer.CustomerNumber,
        AcctTransactions.DocumentNumber,
        AcctTransactions.DocumentDate,
        AcctTransactions.InvoiceDate,
        AcctTransactions.OrdPriKey,
        ExistingPayments.TotalAmount'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrderBalance 1'
--    select * from #Detail
--end

-- The order balance requires specialized date strings.  If the doc date is to be used or no MSS10000 record exists,
-- get the ApplyFrom document date from either history or open, depending on where the document is located
if( 1 = @inUseDocDate )
begin
    set @theOrderBalanceDateString = 'AcctTransactions.DocumentDate'        --'RM20201.APFRDCDT'
end
else
begin
    set @theOrderBalanceDateString = 'isnull( AcctTransactions.InvoiceDate, AcctTransactions.DocumentDate )'
end

-- BBF #2 ( Apply To AcctTransactions )
--        Get all Open transactions with a non-zero Current Transaction Amount prior to the SummarizeTo date
--        Sometimes, AcctTransactions.OrdPriKey is negative when it points to a StatementDetail.SDPriKey
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = Detail.OrderNumber,
        ShipperName = isnull( AppliedToDocument.ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount = case
            when CustomerStatementDocumentType.DocumentTypeID in ( 7, 9 ) then -AcctTransactionsApplyToInfo.ApplyAmount
            else AcctTransactionsApplyToInfo.ApplyAmount
        end,
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = Detail.CustomerNumber,
        Status = ''Open ApplyTo'',
        ApplyToDocumentDate = AppliedToDocument.DocumentDate,
        ApplyToInvoiceDate = isnull( AppliedToDocument.InvoiceDate, AcctTransactions.DocumentDate ),
        ApplyToInvoiceNumber = isnull( AppliedToDocument.InvoiceNumber, '''' )
    from #Detail as Detail
    inner join CustomerStatementDocumentType as DetailDocType on DetailDocType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
    inner join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.ApplyToDocument = Detail.DocumentNumber and
        case AcctTransactionsApplyToInfo.ApplyToDocumentType
            ' +
            --when '' then 0                            -- Reserved for balance carried forward records
            'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
            --when '' then 2                            -- Reserved for scheduled payments
            'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
            --when '''' then 4                            -- Finance Charge
            --when '''' then 5                            -- Service / Repair
            --when '''' then 6                            -- Warranty
            'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
            --when '''' then 8                            -- Return
            'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
            'else 0
        end = DetailDocType.DocumentTypeID and
        Detail.Status = ''Open''
    inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactionsApplyToInfo.AcctTransactionsFID
    inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join AccountingCustomer on
        AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid' + @theCustomerNumberFilter + ' ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = case AcctTransactions.TransactionType
        ' +
        --when '' then 0                            -- Reserved for balance carried forward records
        'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
        --when '' then 2                            -- Reserved for scheduled payments
        'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
        --when '''' then 4                            -- Finance Charge
        --when '''' then 5                            -- Service / Repair
        --when '''' then 6                            -- Warranty
        'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
        --when '''' then 8                            -- Return
        'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
        'else 0
    end
    outer apply (
        select top 1
            ShipperName =  ApplyToTrxn.ShipperName,
            DocumentDate = ApplyToTrxn.DocumentDate,
            InvoiceNumber = ApplyToTrxn.InvoiceNumber,
            InvoiceDate = ApplyToTrxn.InvoiceDate
        from AcctTransactions as ApplyToTrxn
        where ApplyToTrxn.DocumentNumber = AcctTransactionsApplyToInfo.ApplyToDocument
        order by ApplyToTrxn.ATPriKey desc
    ) as AppliedToDocument
    where
    (
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
        (
            ' + @inNonInvoicedItemsFlag + ' = 1 or
            Detail.InvoiceNumber != ''''
        ) and ' +
        @theOrderBalanceDateString + ' <= ''' + @theSummarizeDateString + '''
    )
    group by
        CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        Detail.OrderNumber,
        AppliedToDocument.ShipperName,
        CustomerStatementDocumentType.DocumentTypeID,
        AcctTransactionsApplyToInfo.ApplyAmount,
        AcctTransactions.ATPriKey,
        Detail.CustomerNumber,
        AcctTransactions.DocumentDate,
        AppliedToDocument.DocumentDate,
        AppliedToDocument.InvoiceNumber,
        AppliedToDocument.InvoiceDate'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrderBalance 2'
--    select * from #Detail
--end

-- The order balance requires specialized date strings.  If the doc date is to be used or no MSS10000 record exists,
-- get the ApplyFrom document date from either history or open, depending on where the document is located
if( 1 = @inUseDocDate )
begin
    set @theOrderBalanceDateString =
        ' ApplyTable.APFRDCDT > ''' + @theSummarizeDateString + ''' and
        ApplyTable.APFRDCDT <= ''' + @theCutOffDateString + ''' and
        ApplyTable.APTODCDT <= ''' + @theSummarizeDateString + ''' and '
end
else
begin
    set @theOrderBalanceDateString =
        ' isnull( ApplyTable.APFRINVDT, ApplyTable.APFRDCDT ) > ''' + @theSummarizeDateString + ''' and
        isnull( ApplyTable.APFRINVDT, ApplyTable.APFRDCDT ) <= ''' + @theCutOffDateString + ''' and
        isnull( ApplyTable.APTOINVDT, ApplyTable.APTODCDT ) <= ''' + @theSummarizeDateString + ''' and '
end

-- BBF #3 - Get all current ApplyFrom documents (Open and History) within the current period that apply to document's prior to the
-- summarize date.  Use the absolute value of the ApplyAmount to offset the payment amount that will show up in the
-- current period's details.  This is necessary to wrap up into the Balance Forward so that a new payment has an offset
-- and doesn't look like a credit to the account, since the balance forward amount for the ApplyTo doc wouldn't be pulled
-- otherwise in the other BBF calculations.
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = isnull( ApplyTable.OrderNo, '''' ),
        ShipperName = isnull( ApplyTable.ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount = abs( ApplyTable.APPTOAMT ),
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = AccountingCustomer.CustomerNumber,
        Status = ''ApplyTo'',
        ApplyToDocumentDate = ApplyTable.APTODCDT,
        ApplyToInvoiceDate = isnull( ApplyTable.APTOINVDT, ApplyTable.APTODCDT ),
        ApplyToInvoiceNumber = isnull( ApplyTable.APTOINVNO, '''' )
    from
    (
        select
            APPTOAMT = AcctTransactionsApplyToInfo.ApplyAmount,
            AccountingCustomerFid = XLedgerCustomer.AccountingCustomerFid,
            APTODCDT = AppliedToDocument.DocumentDate,
            APTODCNM = AcctTransactionsApplyToInfo.ApplyToDocument,
            APTOINVDT = AppliedToDocument.InvoiceDate,
            OrderNo = AppliedToDocument.OrderNo,
            ShipperName = AppliedToDocument.ShipperName,
            APTOINVNO = AppliedToDocument.InvoiceNumber,
            APFRDCTY = case AcctTransactions.TransactionType ' +
                --when '' then 0                            -- Reserved for balance carried forward records
                'when ''A/R Sales / Invoice'' then 1 ' +    -- Sale / Invoice
                --when '' then 2                            -- Reserved for scheduled payments
                'when ''A/R Debit Memo'' then 3 ' +            -- Debit Memo
                --when '''' then 4                            -- Finance Charge
                --when '''' then 5                            -- Service / Repair
                --when '''' then 6                            -- Warranty
                'when ''A/R Credit Memo'' then 7 ' +        -- Credit Memo
                --when '''' then 8                            -- Return
                'when ''A/R Cash Receipt'' then 9 ' +        -- Payment
                'else 0
            end,
            APFRDCDT = AcctTransactions.DocumentDate,
            APFRDCNM = AcctTransactions.DocumentNumber,
            APFRINVDT = AcctTransactions.InvoiceDate
        from XLedgerJournalEntryBatchItem
        inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
        ' + @theBranchFilter + '
        inner join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
        inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
        outer apply (
            select top 1
                DocumentDate = ApplyToTrxn.DocumentDate,
                InvoiceDate = ApplyToTrxn.InvoiceDate,
                InvoiceNumber = ApplyToTrxn.InvoiceNumber,
                OrderNo = ApplyToTrxn.OrderNo,
                ShipperName = ApplyToTrxn.ShipperName
            from AcctTransactions as ApplyToTrxn
            where ApplyToTrxn.DocumentNumber = AcctTransactionsApplyToInfo.ApplyToDocument
            order by ApplyToTrxn.ATPriKey desc
        ) as AppliedToDocument
    ) ApplyTable
    inner join AccountingCustomer on
        AccountingCustomer.AccountingCustomerId = ApplyTable.AccountingCustomerFid' + @theCustomerNumberFilter + ' ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = ApplyTable.APFRDCTY
    where
    (
        ' + @theOrderBalanceDateString + '
        (
            ( ' + @inNonInvoicedItemsFlag + ' = 1 or
            isnull( ApplyTable.APTOINVNO, '''' ) != '''' )
        )
    )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrderBalance 3'
--    select * from #Detail
--end

-- Wrap the entire insertion of records into the customer statement tables in a transaction
set transaction isolation level read uncommitted
begin transaction

-- Insert the statement records by grouping on customer number
exec
(
    'insert into CustomerStatement
    (
        CustomerStatementRunFID,
        CustomerName,
        StatementName,
        CustomerNumber,
        CustomerAddress1,
        CustomerAddress2,
        CustomerAddress3,
        CustomerCity,
        CustomerState,
        CustomerPostalCode,
        CustomerCountry,
        PaymentTerms,
        CurrentAmount,
        From30To60Amount,
        From61To90Amount,
        From91To120Amount,
        GreaterThan120Amount,
        CustomerClass,
        StatementCycle,
        EmailAddress
    )
    select
        CustomerStatementRunFID = ' + @outStatementRunID + ',
        CustomerName = isnull( AccountingCustomer.[Name], concat( ''Customer '', Detail.CustomerNumber, '' not found'' ) ),
        StatementName = isnull( AccountingCustomer.[Name], concat( ''Customer '', Detail.CustomerNumber, '' not found'' ) ),    --RM00101.STMTNAME
        CustomerNumber = Detail.CustomerNumber,
        CustomerAddress1 = isnull( AccountingCustomerAddress.Address1, '''' ),
        CustomerAddress2 = isnull( AccountingCustomerAddress.Address2, '''' ),
        CustomerAddress3 = isnull( AccountingCustomerAddress.Address3, '''' ),
        CustomerCity = isnull( AccountingCustomerAddress.City, '''' ),
        CustomerState = isnull( AccountingCustomerAddress.[State], '''' ),
        CustomerPostalCode = isnull( AccountingCustomerAddress.PostalCode, '''' ),
        CustomerCountry = case
            when CountryCodeStandard.CountryName is null then ''''
            when len( CountryCodeStandard.CountryName ) > 61 then substring( CountryCodeStandard.CountryName, 1, 61 )
            else CountryCodeStandard.CountryName
        end,
        PaymentTerms = case
            when AccountingCustomerPaymentTerms.TermsDescription is null then ''''
            when len( AccountingCustomerPaymentTerms.TermsDescription ) > 21 then substring( AccountingCustomerPaymentTerms.TermsDescription, 1, 21 )
            else AccountingCustomerPaymentTerms.TermsDescription
        end,
        CurrentAmount = 0,
        From30To60Amount = 0,
        From61To90Amount = 0,
        From91To120Amount = 0,
        GreaterThan120Amount = 0,
        CustomerClass = isnull( AccountingCustomerClass.ClassCode, '''' ),
        StatementCycle = CustomerStatementStatementCycle.StatementCycle,
        EmailAddress = isnull( AccountingCustomer.Email, '''' )
    from
    (
        select
            DocumentDate,
            InvoiceDate,
            Amount,
            CustomerNumber,
            Status
        from #Detail
        union
        select
            ''1900-01-01'' as DocumentDate,
            ''1900-01-01'' as InvoiceDate,
            0 as Amount,
            CustomerNumber,
            Status
        from #OrderBalance
    ) as Detail
    inner join AccountingCustomerAddressType on AccountingCustomerAddressType.TypeName = dbo.GetDefaultAccountingCustomerAddressTypeName()
    left outer join AccountingCustomer on AccountingCustomer.CustomerNumber = Detail.CustomerNumber
    left outer join AccountingCustomerAddress on AccountingCustomerAddress.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId and
        AccountingCustomerAddress.AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId
    left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardId = AccountingCustomerAddress.CountryCodeStandardFid
    left outer join AccountingCustomerClass on
        AccountingCustomerClass.AccountingCustomerClassId = AccountingCustomer.AccountingCustomerClassFid
    left outer join AccountingCustomerPaymentTerms on
        AccountingCustomerPaymentTerms.AccountingCustomerPaymentTermsId = AccountingCustomer.AccountingCustomerPaymentTermsFid
    left outer join CustomerStatementStatementCycle on CustomerStatementStatementCycle.StatementCycleID = 5
    where Detail.Status != ''ApplyTo''
    group by
        AccountingCustomer.[Name],
        --RM00101.STMTNAME,
        Detail.CustomerNumber,
        AccountingCustomerAddress.Address1,
        AccountingCustomerAddress.Address2,
        AccountingCustomerAddress.Address3,
        AccountingCustomerAddress.City,
        AccountingCustomerAddress.[State],
        AccountingCustomerAddress.PostalCode,
        CountryCodeStandard.CountryName,
        AccountingCustomerPaymentTerms.TermsDescription,
        AccountingCustomerClass.ClassCode,
        CustomerStatementStatementCycle.StatementCycle,
        AccountingCustomer.Email'
)

-- Update the CustomerStatement table with the calculated detail aging amounts
update CustomerStatement
set
    CurrentAmount = isnull( AgingTable.CurrentAmount, 0 ),
    From30To60Amount = isnull( AgingTable.From30To60Amount, 0 ),
    From61To90Amount = isnull( AgingTable.From61To90Amount, 0 ),
    From91To120Amount = isnull( AgingTable.From91To120Amount, 0 ),
    GreaterThan120Amount = isnull( AgingTable.GreaterThan120Amount, 0 )
from
(
    select
        CustomerNumber = Detail.CustomerNumber,
        CurrentAmount =
        isnull( sum(
                case
                    when
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end >= dateadd( day, -30, @inStatementDate )
                    then Detail.Amount
                    else 0
                end
        ), 0 ),
        From30To60Amount =
        isnull( sum(
                case
                    when
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end >= dateadd( day, -60, @inStatementDate ) and
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end <= convert( datetime, @theStatementDateString ) - 31
                    then Detail.Amount
                    else 0
                end
        ), 0 ),
        From61To90Amount =
        isnull( sum(
                case
                    when
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end >= dateadd( day, -90, @inStatementDate ) and
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end <= convert( datetime, @theStatementDateString ) - 61
                    then Detail.Amount
                    else 0
                end
        ), 0 ),
        From91To120Amount =
        isnull( sum(
                case
                    when
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end >= dateadd( day, -120, @inStatementDate ) and
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end <= convert( datetime, @theStatementDateString ) - 91
                    then Detail.Amount
                    else 0
                end
        ), 0 ),
        GreaterThan120Amount =
        isnull( sum(
                case
                    when
                        case
                            when Detail.Status not like '%ApplyTo' then
                                case
                                    when @inUseDocDate = 1 or isnull( Detail.InvoiceDate, '1900-01-01' ) = '1900-01-01' then Detail.DocumentDate
                                    else Detail.InvoiceDate
                                end
                            else
                                case
                                    when @inUseDocDate = 1 then Detail.ApplyToDocumentDate
                                    else Detail.ApplyToInvoiceDate
                                end
                        end <= convert( datetime, @theStatementDateString ) - 121
                    then Detail.Amount
                    else 0
                end
        ), 0 )
    from #Detail as Detail
    inner join CustomerStatementDocumentType on Detail.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
    where
    (
        @inNonInvoicedItemsFlag = 1 or
        Detail.InvoiceNumber != '' or
        CustomerStatementDocumentType.DocumentTypeID = 9
    )
    group by Detail.CustomerNumber
) as AgingTable
where
(
    AgingTable.CustomerNumber = CustomerStatement.CustomerNumber and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)

-- Update the aging buckets with the balance forward amounts
update CustomerStatement
set
    CurrentAmount = CustomerStatement.CurrentAmount + isnull( AgingTable.CurrentAmount, 0 ),
    From30To60Amount = CustomerStatement.From30To60Amount + isnull( AgingTable.From30To60Amount, 0 ),
    From61To90Amount = CustomerStatement.From61To90Amount + isnull( AgingTable.From61To90Amount, 0 ),
    From91To120Amount = CustomerStatement.From91To120Amount + isnull( AgingTable.From91To120Amount, 0 ),
    GreaterThan120Amount = CustomerStatement.GreaterThan120Amount + isnull( AgingTable.GreaterThan120Amount, 0 )
from
(
    select
        CustomerNumber = OrderBalance.CustomerNumber,
        CurrentAmount =
        isnull( sum(
                case
                    when
                        case
                            when @inUseDocDate = 1
                            then OrderBalance.ApplyToDocumentDate
                            else OrderBalance.ApplyToInvoiceDate
                        end >= dateadd( day, -30, @inStatementDate )
                    then OrderBalance.Amount
                    else 0
                end
        ), 0 ),
        From30To60Amount =
        isnull( sum(
                case
                    when
                        case
                            when @inUseDocDate = 1
                            then OrderBalance.ApplyToDocumentDate
                            else OrderBalance.ApplyToInvoiceDate
                        end between dateadd( day, -60, @inStatementDate ) and convert( datetime, @theStatementDateString ) - 31
                    then OrderBalance.Amount
                    else 0
                end
        ), 0 ),
        From61To90Amount =
        isnull( sum(
                case
                    when
                        case
                            when @inUseDocDate = 1
                            then OrderBalance.ApplyToDocumentDate
                            else OrderBalance.ApplyToInvoiceDate
                        end between dateadd( day, -90, @inStatementDate ) and convert( datetime, @theStatementDateString ) - 61
                    then OrderBalance.Amount
                    else 0
                end
        ), 0 ),
        From91To120Amount =
        isnull( sum(
                case
                    when
                        case
                            when @inUseDocDate = 1
                            then OrderBalance.ApplyToDocumentDate
                            else OrderBalance.ApplyToInvoiceDate
                        end between dateadd( day, -120, @inStatementDate ) and convert( datetime, @theStatementDateString ) - 91
                    then OrderBalance.Amount
                    else 0
                end
        ), 0 ),
        GreaterThan120Amount =
        isnull( sum(
                case
                    when
                        case
                            when @inUseDocDate = 1
                            then OrderBalance.ApplyToDocumentDate
                            else OrderBalance.ApplyToInvoiceDate
                        end <= convert( datetime, @theStatementDateString ) - 121
                    then OrderBalance.Amount
                    else 0
                end
        ), 0 )
    from #OrderBalance OrderBalance
    group by OrderBalance.CustomerNumber
) as AgingTable
where
(
    AgingTable.CustomerNumber = CustomerStatement.CustomerNumber and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert Order-level records that are needed for the master-detail type of relationship for any details that
-- will be associated with the order.  
insert into CustomerStatementOrderGroup
(
    CustomerStatementFID,
    Amount,
    OrderNumber,
    Shipper,
    Description,
    CustomerStatementDocumentTypeFID,
    BalanceForwardDate,
    Status
)
select
    CustomerStatementFID = CustomerStatement.CustomerStatementID,
    Amount = 0,
    OrderNumber = Detail.OrderNumber,
    Shipper = Detail.ShipperName,
    Description = 'Beginning Balance Forward',
    CustomerStatementDocumentTypeFID = @theBBFPaymentDocTypeID,
    BalanceForwardDate = @theSummarizeDateString,
    Status = 'Order'
from #Detail as Detail
inner join CustomerStatement on CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on Detail.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
where
(
    Detail.OrderNumber != '' and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
    (
        @inNonInvoicedItemsFlag = 1 or
        Detail.InvoiceNumber != '' or
        CustomerStatementDocumentType.DocumentTypeID = 9
    )
)
group by
    CustomerStatement.CustomerStatementID,
    Detail.OrderNumber,
    Detail.ShipperName

-- Get the error code
set @theTempErrorCode = @@error
if 0 != @theTempErrorCode
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert any detail documents that don't have an invoice number, and don't have an order number
-- into the OrderGroup table
insert into CustomerStatementOrderGroup
(
    CustomerStatementFID,
    Amount,
    OrderNumber,
    Description,
    CustomerStatementDocumentTypeFID,
    BalanceForwardDate,
    Status,
    Balance
)
select
    CustomerStatementFID = CustomerStatement.CustomerStatementID,
    Amount = Detail.Amount,
    OrderNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    BalanceForwardDate = Detail.DocumentDate,
    Status = Detail.Status,
    Balance = Detail.Amount
from #Detail as Detail
inner join CustomerStatement on CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on Detail.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
where
(
    Detail.OrderNumber = '' and
    Detail.InvoiceNumber = '' and
    (
        @inNonInvoicedItemsFlag = 1 or
        CustomerStatementDocumentType.DocumentTypeID = 9
    ) and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Update any existing Orders with their beginning balance forward amounts
update CustomerStatementOrderGroup
set
    Amount =
    (
        select isnull( sum( InnerOrderBalance.Amount ), 0 )
        from #OrderBalance InnerOrderBalance
        where
        (
            CustomerStatementOrderGroup.OrderNumber = InnerOrderBalance.OrderNumber and
            CustomerStatementOrderGroup.Shipper = InnerOrderBalance.ShipperName and
            CustomerStatement.CustomerNumber = InnerOrderBalance.CustomerNumber
        )
    )
from #OrderBalance OrderBalance
inner join CustomerStatementOrderGroup on
(
    CustomerStatementOrderGroup.OrderNumber = OrderBalance.OrderNumber and
    CustomerStatementOrderGroup.Shipper = OrderBalance.ShipperName
)
inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID
where
(
    OrderBalance.OrderNumber != '' and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert any beginning balance forward records that are not already in the OrderGroup table
insert into CustomerStatementOrderGroup
(
    CustomerStatementFID,
    Amount,
    OrderNumber,
    Shipper,
    Description,
    CustomerStatementDocumentTypeFID,
    BalanceForwardDate,
    Status,
    Balance
)
select
    CustomerStatementFID = CustomerStatement.CustomerStatementID,
    Amount = sum( OrderBalance.Amount ),
    OrderNumber = rtrim( OrderBalance.OrderNumber ),
    Shipper = rtrim( OrderBalance.ShipperName ),
    Description = OrderBalance.Description,
    CustomerStatementDocumentTypeFID = @theBBFPaymentDocTypeID,
    BalanceForwardDate = OrderBalance.BalanceForwardDate,
    Status = 'Order',
    Balance = sum( OrderBalance.Amount )
from #OrderBalance OrderBalance
inner join CustomerStatement on CustomerStatement.CustomerNumber = OrderBalance.CustomerNumber
left outer join CustomerStatementOrderGroup OrderGroup on
(
    OrderGroup.OrderNumber = OrderBalance.OrderNumber and
    OrderGroup.Shipper = OrderBalance.ShipperName and
    OrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID
)
where
(
    OrderGroup.CustomerStatementOrderGroupID is null and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)
group by
    CustomerStatement.CustomerStatementID,
    OrderBalance.OrderNumber,
    OrderBalance.ShipperName,
    OrderBalance.BalanceForwardDate,
    OrderBalance.Description

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Get the Document type id for Invoice Group types
declare @theInvoiceGroupDocTypeID int
set @theInvoiceGroupDocTypeID =
(
    select CustomerStatementDocumentTypeID
    from CustomerStatementDocumentType
    where DocumentTypeID = 0
)

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert Invoice-level records that are needed for the master-detail type of relationship for any details that
-- will be associated with the invoice and are not a Payment type (payments are always shown in detail).
-- These invoice lines are visible lines, and are a summation of the documents (excluding payments) that make them up.
insert into CustomerStatementInvoiceGroup
(
    CustomerStatementOrderGroupFID,
    Amount,
    InvoiceNumber,
    Description,
    CustomerStatementDocumentTypeFID,
    InvoiceDate,
    Status,
    Visible
)
select
    CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID,
    Amount = sum( Detail.Amount ),
    InvoiceNumber = Detail.InvoiceNumber,
    Description = 'Invoice Number',
    CustomerStatementDocumentTypeFID = @theInvoiceGroupDocTypeID,
    InvoiceDate = Detail.InvoiceDate,
    Status = '1-InvGroup',
    Visible = 1
from #Detail as Detail
inner join CustomerStatementOrderGroup on
(
    CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber and
    CustomerStatementOrderGroup.Shipper = Detail.ShipperName
)
inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementDocumentType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
where
(
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
    Detail.InvoiceNumber != ''
)
group by
    CustomerStatementOrderGroup.CustomerStatementOrderGroupID,
    Detail.InvoiceNumber,
    Detail.InvoiceDate

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert any detail documents that don't have an invoice number, but do have an order number
-- into the InvoiceGroup table
insert into CustomerStatementInvoiceGroup
(
    CustomerStatementOrderGroupFID,
    Amount,
    InvoiceNumber,
    Description,
    CustomerStatementDocumentTypeFID,
    InvoiceDate,
    Status,
    Visible
)
select
    CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID,
    Amount = Detail.Amount,
    InvoiceNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    InvoiceDate = Detail.DocumentDate,
    Status = '3-' + Detail.Status,
    Visible = 1
from #Detail as Detail
inner join CustomerStatementOrderGroup on
(
    CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber and
    CustomerStatementOrderGroup.Shipper = Detail.ShipperName
)
inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementDocumentType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
where
(
    Detail.InvoiceNumber = '' and
    isnull( Detail.ApplyToDocument, '' ) = '' and
    (
        @inNonInvoicedItemsFlag = 1 or
        CustomerStatementDocumentType.DocumentTypeID = 9
    ) and
    Detail.OrderNumber != '' and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID
)

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert Invoice-level records that are needed for any payments/credit memos that do not have an Invoice/ApplyTo that they can
-- appear under.  This is because the Invoice/ApplyTo they are associated to is wrapped up in the beginning balance forward.
insert into CustomerStatementInvoiceGroup
(
    CustomerStatementOrderGroupFID,
    Amount,
    InvoiceNumber,
    Description,
    CustomerStatementDocumentTypeFID,
    InvoiceDate,
    Status,
    Visible
)
select
    CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID,
    Amount = sum( Detail.Amount ),
    InvoiceNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    InvoiceDate = Detail.DocumentDate,
    Status = '4-' + Detail.Status,
    Visible = 1
from #Detail as Detail
inner join CustomerStatementOrderGroup on
(
    CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber and
    CustomerStatementOrderGroup.Shipper = Detail.ShipperName
)
inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementDocumentType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
left outer join CustomerStatementInvoiceGroup on
(
    CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID and
    CustomerStatementInvoiceGroup.InvoiceNumber = Detail.ApplyToInvoiceNumber
)
where
(
    Detail.InvoiceNumber = '' and
    isnull( Detail.ApplyToDocument, '' ) != '' and
    (
        @inNonInvoicedItemsFlag = 1 or
        CustomerStatementDocumentType.DocumentTypeID = 9
    ) and
    Detail.OrderNumber != '' and
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
    CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID is null and
    not exists
    (
        select 1
        from  #Detail as subDetail
        inner join CustomerStatementDocumentType as subType on subType.CustomerStatementDocumentTypeID = subDetail.CustomerStatementDocumentTypeFID
        where
            subDetail.DocumentNumber = Detail.ApplyToDocument and
            subType.DocumentTypeID = Detail.ApplyToDocumentType and
            (
                @inNonInvoicedItemsFlag = 1 or
                Detail.ApplyToInvoiceNumber != ''
            )
    )
)
group by
    CustomerStatementOrderGroup.CustomerStatementOrderGroupID,
    Detail.DocumentNumber,
    Detail.Description,
    Detail.CustomerStatementDocumentTypeFID,
    Detail.DocumentDate,
    Detail.Status

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert documents that make up an invoice
insert into CustomerStatementDetail
(
    CustomerStatementInvoiceGroupFID,
    CustomerStatementDocumentTypeFID,
    DocumentNumber,
    Description,
    Amount,
    DocumentDate,
    Visible,
    Status,
    IncludedOnInvoiceFlag
)
select
    CustomerStatementInvoiceGroupFID = CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    DocumentNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    Amount = Detail.Amount,
    DocumentDate = Detail.DocumentDate,
    Visible = Detail.Visible,
    Status = '1-' + Detail.Status,
    IncludedOnInvoiceFlag = 1
from #Detail as Detail
inner join CustomerStatementInvoiceGroup on CustomerStatementInvoiceGroup.InvoiceNumber = Detail.InvoiceNumber
inner join CustomerStatementOrderGroup on CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID and CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber
inner join CustomerStatement on CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementInvoiceGroup.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
where
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert the apply records that were applied to the documents that make up an invoice
-- but are not already there because they also showed up on the invoice
insert into CustomerStatementDetail
(
    CustomerStatementInvoiceGroupFID,
    CustomerStatementDocumentTypeFID,
    DocumentNumber,
    Description,
    Amount,
    DocumentDate,
    Visible,
    Status
)
select
    CustomerStatementInvoiceGroupFID = CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    DocumentNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    Amount = sum( Detail.Amount ),
    DocumentDate = Detail.DocumentDate,
    Visible = 1,
    Status = '2-' + Detail.Status
from #Detail as Detail
inner join CustomerStatementInvoiceGroup on CustomerStatementInvoiceGroup.InvoiceNumber = Detail.ApplyToInvoiceNumber
inner join CustomerStatementOrderGroup on CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID and CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber
inner join CustomerStatement on CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementInvoiceGroup.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
inner join CustomerStatementDocumentType DocumentType on Detail.CustomerStatementDocumentTypeFID = DocumentType.CustomerStatementDocumentTypeID
where
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
    CustomerStatementDocumentType.DocumentTypeID = 0 and
    Detail.InvoiceNumber = '' and
    (
        @inNonInvoicedItemsFlag = 1 or
        DocumentType.DocumentTypeID = 9
    )
group by
    CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID,
    Detail.CustomerStatementDocumentTypeFID,
    Detail.DocumentNumber,
    Detail.Description,
    Detail.DocumentDate,
    Detail.Status

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Insert the apply records that were applied to documents that do not have an invoice
-- NOTE: CustomerStatementInvoiceGroup.InvoiceNumber at this point is actually a DocumentNumber
insert into CustomerStatementDetail
(
    CustomerStatementInvoiceGroupFID,
    CustomerStatementDocumentTypeFID,
    DocumentNumber,
    Description,
    Amount,
    DocumentDate,
    Visible,
    Status
)
select
    CustomerStatementInvoiceGroupFID = CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID,
    CustomerStatementDocumentTypeFID = Detail.CustomerStatementDocumentTypeFID,
    DocumentNumber = Detail.DocumentNumber,
    Description = Detail.Description,
    Amount = Detail.Amount,
    DocumentDate = Detail.DocumentDate,
    Visible = Detail.Visible,
    Status = '3-' + Detail.Status
from #Detail as Detail
inner join CustomerStatementInvoiceGroup on    CustomerStatementInvoiceGroup.InvoiceNumber = Detail.ApplyToDocument
inner join CustomerStatementOrderGroup on CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID and CustomerStatementOrderGroup.OrderNumber = Detail.OrderNumber
inner join CustomerStatement on CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID and CustomerStatement.CustomerNumber = Detail.CustomerNumber
inner join CustomerStatementDocumentType on CustomerStatementInvoiceGroup.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
where
    CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
    CustomerStatementDocumentType.DocumentTypeID = Detail.ApplyToDocumentType and
    Detail.InvoiceNumber = '' and
    @inNonInvoicedItemsFlag = 1

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Update Order level with the balance.  This is the balance of the invoice levels summed with the details.  The details
-- that need to be summed are only those not summed up into the invoice level, which are payments.

update CustomerStatementOrderGroup
set
    Balance = CustomerStatementOrderGroup.Amount +
    isnull( (
        select sum( CustomerStatementInvoiceGroup.Amount )
        from CustomerStatementInvoiceGroup
        where CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID
    ), 0 ) +
    isnull( (
        select sum( CustomerStatementDetail.Amount )
        from CustomerStatementInvoiceGroup
        inner join CustomerStatementDetail on CustomerStatementDetail.CustomerStatementInvoiceGroupFID = CustomerStatementInvoiceGroup.CustomerStatementInvoiceGroupID
        where
            CustomerStatementInvoiceGroup.CustomerStatementOrderGroupFID = CustomerStatementOrderGroup.CustomerStatementOrderGroupID and
            CustomerStatementDetail.IncludedOnInvoiceFlag = 0
    ), 0 )
from CustomerStatementOrderGroup
inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID
where CustomerStatement.CustomerStatementRunFID = @outStatementRunID

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Update the Customer Statement with the overall balance
update CustomerStatement
    set Balance =
    isnull( (
        select sum( CustomerStatementOrderGroup.Balance )
        from CustomerStatementOrderGroup
        where CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID
    ), 0 )
from CustomerStatement
where CustomerStatement.CustomerStatementRunFID = @outStatementRunID

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Delete any orders, and levels below, that have zero balances if the flag to suppress has been set
if( 1 = @inSuppressZeroBalancesFlag )
begin
    delete from CustomerStatementOrderGroup
    from CustomerStatementOrderGroup
    inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID
    where
    (
        CustomerStatementOrderGroup.Balance = 0 and
        CustomerStatement.CustomerStatementRunFID = @outStatementRunID
    )
end

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Delete any statements that do not have at least one order with a BBF (Past Due Balance) if the flag has been set
if( 1 = @inPastDueBalancesFlag )
begin
    delete
    from CustomerStatement
    where
    (
        CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
        not exists
        (
            select *
            from CustomerStatementOrderGroup
            inner join CustomerStatement on CustomerStatement.CustomerStatementID = CustomerStatementOrderGroup.CustomerStatementFID
            where
            (
                CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
                CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID and
                CustomerStatementOrderGroup.Amount != 0 and
                CustomerStatementOrderGroup.Status = 'Order'
            )
        )
    )
end

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Delete any statements that have a credit balance if the flag has been set
if( 1 = @inExcludeCreditBalancesFlag )
begin
    delete
    from CustomerStatement
    where
    (
        CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
        CustomerStatement.Balance < 0
    )
end

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Remove any statements that have no lines left after all exclusions
delete
    from CustomerStatement
    from CustomerStatement
    left outer join CustomerStatementOrderGroup on CustomerStatementOrderGroup.CustomerStatementFID = CustomerStatement.CustomerStatementID
    where
    (
        CustomerStatement.CustomerStatementRunFID = @outStatementRunID and
        CustomerStatement.Balance = 0 and
        CustomerStatementOrderGroup.CustomerStatementOrderGroupID is null
    )

-- Get the error code
set @theTempErrorCode = @@error
if( 0 != @theTempErrorCode )
begin
    set @outErrorCode = @theTempErrorCode
end

-- Check the errors, and rollback if an error was encountered, otherwise commit
if( 0 != @outErrorCode )
begin
    rollback transaction
end
else
begin
    commit transaction
end

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    -- @inGenerateUnitTestOutput can be 1 but only for unit tests.
--    select
--        [Table] = 'SP-Detail',
--        TypeFID = CustomerStatementDocumentType.DocumentTypeID,
--        StmtDocType = left( CustomerStatementDocumentType.[Description], 15),
--        DocumentNo = left( Detail.DocumentNumber, 15 ),
--        SDDocNumber = left( StatementDetail.[Description], 15 ),
--        SDDDocNumber = left( StatementDetailDist.[Description], 15 ),
--        [Description] = left( Detail.[Description], 15 ),
--        Detail.Amount,
--        DocDate = format( Detail.DocumentDate, 'MM-dd-yy' ),
--        OrderNo = left( Detail.OrderNumber, 15 ),
--        InvNumber = left( Detail.InvoiceNumber, 15 ),
--        InvDate = convert( varchar(10), Detail.InvoiceDate, 101 ),
--        [Status] = left( Detail.[Status], 15 ),
--        ApplyToDocDate = convert( varchar(10), Detail.ApplyToDocumentDate, 101 ),
--        ApplyToDocType = Detail.ApplyToDocumentType,
--        ApplyToDoc = left( Detail.ApplyToDocument, 15 ),
--        ApplyToInvNo = left( Detail.ApplyToInvoiceNumber, 15 ),
--        ApplyToInvDate = convert( varchar(10), Detail.ApplyToInvoiceDate, 101 ),
--        CustNo = Detail.CustomerNumber,
--        Shipper = left( Detail.ShipperName, 15 )
--    from #Detail as Detail
--    left outer join CustomerStatementDocumentType on
--        Detail.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
--    left outer join AcctTransactions as SDDAcctTrxns on
--        SDDAcctTrxns.DocumentNumber = Detail.DocumentNumber and
--        SDDAcctTrxns.[Source] = 'StatementDetailDist'
--    left outer join StatementDetailDist on StatementDetailDist.SDDPriKey = SDDAcctTrxns.SourceRecord
--    left outer join AcctTransactions as SDAcctTrxns on
--        SDAcctTrxns.DocumentNumber = Detail.DocumentNumber and
--        SDAcctTrxns.[Source] = 'StatementDetail'
--    left outer join StatementDetail on StatementDetail.SDPriKey = SDAcctTrxns.SourceRecord
--    order by CustomerStatementDocumentType.DocumentTypeID,
--        Detail.DocumentNumber,
--        Detail.DocumentDate
--
--    select
--        [Table] = 'SP-OrdBal',
--        TypeFID = CustomerStatementDocumentType.DocumentTypeID,
--        StmtDocType = left( CustomerStatementDocumentType.[Description], 15),
--        [Description] = left( OrderBalance.[Description], 15 ),
--        OrderNo = left( OrderBalance.OrderNumber, 15 ),
--        ShipperName = left( OrderBalance.ShipperName, 15 ),
--        BalFwdDate = convert( varchar(10), OrderBalance.BalanceForwardDate, 101 ),
--        CustNo = OrderBalance.CustomerNumber,
--        OrderBalance.Amount,
--        OrderBalance.[Status],
--        ApplyToDocDate = convert( varchar(10), OrderBalance.ApplyToDocumentDate, 101 ),
--        ApplyToInvNo = left( OrderBalance.ApplyToInvoiceNumber, 15 ),
--        ApplyToInvDate = convert( varchar(10), OrderBalance.ApplyToInvoiceDate, 101 )
--    from #OrderBalance as OrderBalance
--    left outer join CustomerStatementDocumentType on
--        OrderBalance.CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID
--    order by CustomerStatementDocumentType.DocumentTypeID,
--        OrderBalance.ApplyToDocumentDate,
--        OrderBalance.ApplyToInvoiceNumber
--end

-- Drop temporary tables
drop table #Detail
drop table #OrderBalance
GO
GRANT EXECUTE ON  [dbo].[CustomerStatementGenerate_XLedger] TO [MssExec]
GO