SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/**
*    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 legacy (GP)
*    accounting system and CustomerStatementGenerate_XLedger is used by XLedger accounting system.
*
*    @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
*    @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
*    @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_Legacy]
    @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 @theHistoryDateString varchar(512)
declare @theOpenDateApplyFromString varchar(512)
declare @theHistoryDateApplyFromString 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 = 'RM20101.DOCDATE'
    set @theHistoryDateString = 'RM30101.DOCDATE'
    set @theOpenDateApplyFromString = 'RM20101.DOCDATE'
    set @theHistoryDateApplyFromString = 'RM30101.DOCDATE'
end
else
begin
    set @theOpenDateString = 'case when MSS10000.MSS_InvoiceDate = ''1900-1-1'' then RM20101.DOCDATE else MSS10000.MSS_InvoiceDate end'
    set @theHistoryDateString = 'case when MSS10000.MSS_InvoiceDate = ''1900-1-1'' then RM30101.DOCDATE else MSS10000.MSS_InvoiceDate end'
    set @theOpenDateApplyFromString = 'case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM20101.DOCDATE else MSS10000_FROM.MSS_InvoiceDate end'
    set @theHistoryDateApplyFromString = 'case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM30101.DOCDATE else MSS10000_FROM.MSS_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
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.CustomerClass = RM00101.CUSTCLAS and CustomerStatementRunCustomerClass.CustomerStatementRunFID = ' + rtrim( str( @outStatementRunID ) )
end
else
begin
    set @theCustomerClassFilter = ''
end

-- Create a string that will be used to filter the statement cycle
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 = MSS10000.MSS_BranchCode 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 ) + ' = RM00101.CUSTNMBR '
end
else
begin
    set @theCustomerNumberFilter = ''
end

-- Current #1 - 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.
exec
(
    'insert into #Detail
    (
        CustomerStatementDocumentTypeFID,
        DocumentNumber,
        Description,
        Amount,
        DocumentDate,
        Visible,
        CustomerNumber,
        OrderNumber,
        ShipperName,
        InvoiceNumber,
        InvoiceDate,
        Status
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        DocumentNumber = RM20101.DOCNUMBR,
        Description = RM20101.TRXDSCRN,
        Amount =
            case
                when RM20101.RMDTYPAL in ( 7, 9 ) then -RM20101.ORTRXAMT
                else RM20101.ORTRXAMT
            end,
        DocumentDate= RM20101.DOCDATE,
        Visible =
            case
                when isnull( MSS10000.MSS_InvoiceNumber, '''' ) = '''' then 1
                else ' + @inShowDetail + '
            end,
        CustomerNumber = RM20101.CUSTNMBR,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        InvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' ),
        InvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM20101.DOCDATE else MSS10000.MSS_InvoiceDate end,
        Status = ''Open''
    from RM20101_Synonym as RM20101
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM20101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM20101.DOCNUMBR and MSS10000.DOCTYPE = RM20101.RMDTYPAL and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    left outer join RM20201_Synonym as RM20201 on RM20201.APFRDCNM = RM20101.DOCNUMBR and RM20201.APFRDCTY = RM20101.RMDTYPAL
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM20101.RMDTYPAL
    where
    ( ' +
        @theOpenDateString + ' > ''' + @theSummarizeDateString + ''' and ' +
        @theOpenDateString + ' <= ''' + @theCutOffDateString + ''' and
        (
            ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
            RM20101.RMDTYPAL != 9
        ) and
        RM20201.DEX_ROW_ID is null and
        RM20101.VOIDSTTS = 0
    )'
)

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

-- Current #2 - Get all History transactions with a date in the given date range and do not exist as an ApplyFrom document.
--        Use the Original Transaction Amount.
exec
(
    'insert into #Detail
    (
        CustomerStatementDocumentTypeFID,
        DocumentNumber,
        Description,
        Amount,
        DocumentDate,
        Visible,
        CustomerNumber,
        OrderNumber,
        ShipperName,
        InvoiceNumber,
        InvoiceDate,
        Status
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        DocumentNumber = RM30101.DOCNUMBR,
        Description = RM30101.TRXDSCRN,
        Amount =
            case
                when RM30101.RMDTYPAL in ( 7, 9 ) then -RM30101.ORTRXAMT
                else RM30101.ORTRXAMT
            end,
        DocumentDate= RM30101.DOCDATE,
        Visible =
            case
                when isnull( MSS10000.MSS_InvoiceNumber, '''' ) = '''' then 1
                else ' + @inShowDetail + '
            end,
        CustomerNumber = RM30101.CUSTNMBR,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        InvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' ),
        InvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM30101.DOCDATE else MSS10000.MSS_InvoiceDate end,
        Status = ''History''
    from RM30101_Synonym as RM30101
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM30101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM30101.DOCNUMBR and MSS10000.DOCTYPE = RM30101.RMDTYPAL and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    left outer join RM30201_Synonym as RM30201 on RM30201.APFRDCNM = RM30101.DOCNUMBR and RM30201.APFRDCTY = RM30101.RMDTYPAL
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM30101.RMDTYPAL
    where
    (' +
        @theHistoryDateString + ' > ''' + @theSummarizeDateString + ''' and ' +
        @theHistoryDateString + ' <= ''' + @theCutOffDateString + ''' and
        
        -- AJB: I think this is missing
        (
            ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
            RM30101.RMDTYPAL != 9
        ) and

        RM30201.DEX_ROW_ID is null and
        RM30101.VOIDSTTS = 0
    )'
)

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

-- Current #3a - Get all trx from the Open Apply tables that have a date within the current period.
-- They need to use the ApplyToDoc's order information for grouping purposes.  These records may or may not
-- apply to a record that is within the current period, but since their date is current, they must be
-- shown in detail under the order/invoice they apply to.
-- Use the ApplyTo Transaction Amount.
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 = RM20201.APFRDCNM,
        Description = RM20101.TRXDSCRN,
        Amount = -RM20201.APPTOAMT,
        DocumentDate = RM20101.DOCDATE,
        Visible =
            case
                when isnull( MSS10000_FROM.MSS_InvoiceNumber, '''' ) = '''' then 1
                else ' + @inShowDetail + '
            end,
        CustomerNumber = RM20101.CUSTNMBR,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        InvoiceNumber = isnull( MSS10000_FROM.MSS_InvoiceNumber, '''' ),
        InvoiceDate = case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM20201.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end,
        Status = ''Open ApplyTo'',
        ApplyToDocumentDate = RM20201.APTODCDT,
        ApplyToDocumentType = RM20201.APTODCTY,
        ApplyToDocument = RM20201.APTODCNM,
        ApplyToInvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' ),
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM20101.DOCDATE else MSS10000.MSS_InvoiceDate end
    from RM20101_Synonym as RM20101
    inner join RM20201_Synonym as RM20201 on RM20201.APFRDCNM = RM20101.DOCNUMBR and RM20201.APFRDCTY = RM20101.RMDTYPAL
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM20101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000_FROM on MSS10000_FROM.DOCNUMBR = RM20101.DOCNUMBR and MSS10000_FROM.DOCTYPE = RM20101.RMDTYPAL and MSS10000_FROM.SERIES = 3
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM20201.APTODCNM and MSS10000.DOCTYPE = RM20201.APTODCTY ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM20201.APFRDCTY
    where
    (
        ' + @theOpenDateApplyFromString + ' > ''' + @theSummarizeDateString + ''' and
        ' + @theOpenDateApplyFromString + ' <= ''' + @theCutOffDateString + ''' and
        RM20101.VOIDSTTS = 0
    )'
)

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

-- Current #3b - Get all trx from the History Apply tables that have a date within in the current period.
-- They need to use the ApplyToDoc's order information for grouping purposes. These record's may or may not
-- apply to a record that is within the current period, but since their date is current, they must be
-- shown in detail under the order/invoice they apply to.
-- Use the ApplyTo Transaction Amount.
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 = RM30101.DOCNUMBR,
        Description = RM30101.TRXDSCRN,
        Amount = -RM30201.APPTOAMT,
        DocumentDate = RM30101.DOCDATE,
        Visible =
            case
                when isnull( MSS10000_FROM.MSS_InvoiceNumber, '''' ) = '''' then 1
                else ' + @inShowDetail + '
            end,
        CustomerNumber = RM30101.CUSTNMBR,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        InvoiceNumber = isnull( MSS10000_FROM.MSS_InvoiceNumber, '''' ),
        InvoiceDate = case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM30201.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end,
        Status = ''History ApplyTo'',
        ApplyToDocumentDate = RM30201.APTODCDT,
        ApplyToDocumentType = RM30201.APTODCTY,
        ApplyToDocument = RM30201.APTODCNM,
        ApplyToInvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' ),
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM30101.DOCDATE else MSS10000.MSS_InvoiceDate end
    from RM30101_Synonym as RM30101
    inner join RM30201_Synonym as RM30201 on RM30201.APFRDCNM = RM30101.DOCNUMBR and RM30201.APFRDCTY = RM30101.RMDTYPAL
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM30101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000_FROM on MSS10000_FROM.DOCNUMBR = RM30101.DOCNUMBR and MSS10000_FROM.DOCTYPE = RM30101.RMDTYPAL and MSS10000_FROM.SERIES = 3
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM30201.APTODCNM and MSS10000.DOCTYPE = RM30201.APTODCTY and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM30201.APFRDCTY
    where
    (
        ' + @theHistoryDateApplyFromString + ' > ''' + @theSummarizeDateString + ''' and
        ' + @theHistoryDateApplyFromString + ' <= ''' + @theCutOffDateString + ''' and
        RM30101.VOIDSTTS = 0
    )'
)

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

-- Current #4 - Get all Open transactions with a current amount (not fully applied) that exist as an
-- ApplyFrom record, and insert them with their Current Transaction Amount.
exec
(
    'insert into #Detail
    (
        CustomerStatementDocumentTypeFID,
        DocumentNumber,
        Description,
        Amount,
        DocumentDate,
        Visible,
        CustomerNumber,
        OrderNumber,
        ShipperName,
        InvoiceNumber,
        InvoiceDate,
        Status
    )
    select distinct
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        DocumentNumber = RM20101.DOCNUMBR,
        Description = RM20101.TRXDSCRN,
        Amount = -RM20101.CURTRXAM,
        DocumentDate= RM20101.DOCDATE,
        Visible =
            case
                when isnull( MSS10000.MSS_InvoiceNumber, '''' ) = '''' then 1
                else ' + @inShowDetail + '
            end,
        CustomerNumber = RM20101.CUSTNMBR,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        InvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' ),
        InvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM20101.DOCDATE else MSS10000.MSS_InvoiceDate end,
        Status = ''Open ApplyFrom''
    from RM20101_Synonym as RM20101
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM20101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM20101.DOCNUMBR and MSS10000.DOCTYPE = RM20101.RMDTYPAL ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join RM20201_Synonym as RM20201 on RM20201.APFRDCNM = RM20101.DOCNUMBR and RM20201.APFRDCTY = RM20101.RMDTYPAL
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM20101.RMDTYPAL
    where
    (' +
        @theOpenDateString + ' > ''' + @theSummarizeDateString + ''' and ' +
        @theOpenDateString + ' <= ''' + @theCutOffDateString + ''' and
        RM20101.CURTRXAM != 0 and
        RM20101.VOIDSTTS = 0
    )'
)

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

-- BBF #1 - Get all Open transactions with a non-zero Current Transaction Amount prior to the SummarizeTo date
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount =
            case
                when RM20101.RMDTYPAL in ( 7, 9 ) then -RM20101.CURTRXAM
                else RM20101.CURTRXAM
            end,
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = RM20101.CUSTNMBR,
        Status = ''Current'',
        ApplyToDocumentDate = RM20101.DOCDATE,
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM20101.DOCDATE else MSS10000.MSS_InvoiceDate end,
        ApplyToInvoiceNumber = ''''
    from RM20101_Synonym as RM20101
    left outer join RM20201_Synonym as RM20201 on RM20201.APFRDCNM = RM20101.DOCNUMBR and RM20201.APFRDCTY = RM20101.RMDTYPAL
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM20101.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM20101.DOCNUMBR and MSS10000.DOCTYPE = RM20101.RMDTYPAL and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM20101.RMDTYPAL
    where
    (
        ' + @theOpenDateString + ' <= ''' + @theSummarizeDateString + ''' and
        (
            ' + @inNonInvoicedItemsFlag + ' = 1 or
            isnull( MSS10000.MSS_InvoiceNumber, '''' ) != ''''
        ) and
        (
            ' + @inExcludeUnappliedPaymentsFlag + ' = 0 or
            RM20201.DEX_ROW_ID is not null or
            RM20101.RMDTYPAL != 9
        ) and
        RM20101.VOIDSTTS = 0
    )
    group by
        CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        MSS10000.MSS_OrderNo,
        MSS10000.MSS_ShipperName,
        RM20101.RMDTYPAL,
        RM20101.CURTRXAM,
        RM20101.CUSTNMBR,
        RM20101.DOCDATE,
        RM20101.DOCNUMBR,
        MSS10000.MSS_InvoiceDate'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrdBal 1'
--    select * from #OrderBalance
--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 = 'RM20201.APFRDCDT'
end
else
begin
    set @theOrderBalanceDateString = 'case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM20201.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end'
end

-- BBF #2a - Get all ApplyFrom trx that ApplyTo Open transactions within the current date range with a date
-- prior to the SummarizeTo date.
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = Detail.OrderNumber,
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount =
            case
                when CustomerStatementDocumentType.DocumentTypeID in ( 7, 9 ) then -RM20201.APPTOAMT
                else RM20201.APPTOAMT
            end,
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = Detail.CustomerNumber,
        Status = ''ApplyTo'',
        ApplyToDocumentDate = RM20201.APTODCDT,
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM20201.APTODCDT else MSS10000.MSS_InvoiceDate end,
        ApplyToInvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' )
    from #Detail as Detail
    inner join CustomerStatementDocumentType as DetailDocType on DetailDocType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = Detail.CustomerNumber ' + @theCustomerNumberFilter + '
    inner join RM20201_Synonym as RM20201 on RM20201.APTODCNM = Detail.DocumentNumber and RM20201.APTODCTY = DetailDocType.DocumentTypeID and Detail.Status = ''Open''
    left outer join RM20101_Synonym as RM20101 on RM20101.DOCNUMBR = RM20201.APFRDCNM and RM20101.RMDTYPAL = RM20201.APFRDCTY
    left outer join RM30101_Synonym as RM30101 on RM30101.DOCNUMBR = RM20201.APFRDCNM and RM30101.RMDTYPAL = RM20201.APFRDCTY
    left outer join MSS10000_Synonym as MSS10000_FROM on MSS10000_FROM.DOCNUMBR = RM20201.APFRDCNM and MSS10000_FROM.DOCTYPE = RM20201.APFRDCTY and MSS10000_FROM.SERIES = 3
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM20201.APTODCNM and MSS10000.DOCTYPE = RM20201.APTODCTY and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM20201.APFRDCTY
    where
    (
        (
            ' + @inNonInvoicedItemsFlag + ' = 1 or
            Detail.InvoiceNumber != ''''
        ) and ' +
        @theOrderBalanceDateString + ' <= ''' + @theSummarizeDateString + ''' and
        isnull( RM20101.VOIDSTTS, RM30101.VOIDSTTS ) = 0
    )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrdBal 2a'
--    select * from #OrderBalance
--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 = 'RM30201.APFRDCDT'
end
else
begin
    set @theOrderBalanceDateString = 'case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then RM30201.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end'
end

-- BBF #2b - Get all ApplyFrom trx that ApplyTo History transactions within the current date range with a date
-- prior to the SummarizeTo date.
exec
(
    'insert into #OrderBalance
    (
        CustomerStatementDocumentTypeFID,
        OrderNumber,
        ShipperName,
        Description,
        Amount,
        BalanceForwardDate,
        CustomerNumber,
        Status,
        ApplyToDocumentDate,
        ApplyToInvoiceDate,
        ApplyToInvoiceNumber
    )
    select
        CustomerStatementDocumentTypeFID = CustomerStatementDocumentType.CustomerStatementDocumentTypeID,
        OrderNumber = Detail.OrderNumber,
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount =
            case
                when CustomerStatementDocumentType.DocumentTypeID in ( 7, 9 ) then -RM30201.APPTOAMT
                else RM30201.APPTOAMT
            end,
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = Detail.CustomerNumber,
        Status = ''ApplyTo'',
        ApplyToDocumentDate = RM30201.APTODCDT,
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then RM30201.APTODCDT else MSS10000.MSS_InvoiceDate end,
        ApplyToInvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' )
    from #Detail as Detail
    inner join CustomerStatementDocumentType as DetailDocType on DetailDocType.CustomerStatementDocumentTypeID = Detail.CustomerStatementDocumentTypeFID
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = Detail.CustomerNumber ' + @theCustomerNumberFilter + '
    left outer join RM30201_Synonym as RM30201 on RM30201.APTODCNM = Detail.DocumentNumber and RM30201.APTODCTY = DetailDocType.DocumentTypeID and Detail.Status = ''History''
    left outer join RM20101_Synonym as RM20101 on RM20101.DOCNUMBR = RM30201.APFRDCNM and RM20101.RMDTYPAL = RM30201.APFRDCTY
    left outer join RM30101_Synonym as RM30101 on RM30101.DOCNUMBR = RM30201.APFRDCNM and RM30101.RMDTYPAL = RM30201.APFRDCTY
    left outer join MSS10000_Synonym as MSS10000_FROM on MSS10000_FROM.DOCNUMBR = RM30201.APFRDCNM and MSS10000_FROM.DOCTYPE = RM30201.APFRDCTY and MSS10000_FROM.SERIES = 3
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = RM30201.APTODCNM and MSS10000.DOCTYPE = RM30201.APTODCTY and MSS10000.SERIES = 3' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = RM30201.APFRDCTY
    where
    (
        (
            ' + @inNonInvoicedItemsFlag + ' = 1 or
            Detail.InvoiceNumber != ''''
        ) and ' +
        @theOrderBalanceDateString + ' <= ''' + @theSummarizeDateString + ''' and
        isnull( RM20101.VOIDSTTS, RM30101.VOIDSTTS ) = 0
    )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrdBal 2b'
--    select * from #OrderBalance
--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 =
        ' case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then ApplyTable.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end > ''' + @theSummarizeDateString + ''' and
        case when MSS10000_FROM.MSS_InvoiceDate = ''1900-01-01'' then ApplyTable.APFRDCDT else MSS10000_FROM.MSS_InvoiceDate end <= ''' + @theCutOffDateString + ''' and
        case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then ApplyTable.APTODCDT else MSS10000.MSS_InvoiceDate end <= ''' + @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( MSS10000.MSS_OrderNo, '''' ),
        ShipperName = isnull( MSS10000.MSS_ShipperName, '''' ),
        Description = ''Beginning Balance Forward'',
        Amount = abs( ApplyTable.APPTOAMT ),
        BalanceForwardDate = ''' + @theSummarizeDateString + ''',
        CustomerNumber = ApplyTable.CUSTNMBR,
        Status = ''ApplyTo'',
        ApplyToDocumentDate = ApplyTable.APTODCDT,
        ApplyToInvoiceDate = case when MSS10000.MSS_InvoiceDate = ''1900-01-01'' then ApplyTable.APTODCDT else MSS10000.MSS_InvoiceDate end,
        ApplyToInvoiceNumber = isnull( MSS10000.MSS_InvoiceNumber, '''' )
    from
    (
        select APPTOAMT, CUSTNMBR, APTODCDT, APTODCTY, APTODCNM, APFRDCTY, APFRDCDT, APFRDCNM from RM20201_Synonym
        union
        select APPTOAMT, CUSTNMBR, APTODCDT, APTODCTY, APTODCNM, APFRDCTY, APFRDCDT, APFRDCNM from RM30201_Synonym
    ) ApplyTable
    left outer join RM20101_Synonym as RM20101 on RM20101.DOCNUMBR = ApplyTable.APFRDCNM and RM20101.RMDTYPAL = ApplyTable.APFRDCTY
    left outer join RM30101_Synonym as RM30101 on RM30101.DOCNUMBR = ApplyTable.APFRDCNM and RM30101.RMDTYPAL = ApplyTable.APFRDCTY
    inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = ApplyTable.CUSTNMBR ' + @theCustomerNumberFilter + '
    left outer join MSS10000_Synonym as MSS10000_FROM on MSS10000_FROM.DOCNUMBR = ApplyTable.APFRDCNM and MSS10000_FROM.DOCTYPE = ApplyTable.APFRDCTY
    left outer join MSS10000_Synonym as MSS10000 on MSS10000.DOCNUMBR = ApplyTable.APTODCNM  and MSS10000.DOCTYPE = ApplyTable.APTODCTY ' +
        @theCustomerClassFilter + ' ' +
        @theStatementCycleFilter + ' ' +
        @theBranchFilter + '
    inner join CustomerStatementDocumentType on CustomerStatementDocumentType.DocumentTypeID = ApplyTable.APFRDCTY
    where
    (
        ' + @theOrderBalanceDateString + '
        (
            ( ' + @inNonInvoicedItemsFlag + ' = 1 or
            isnull( MSS10000.MSS_InvoiceNumber, '''' ) != '''' ) and
            isnull( RM20101.VOIDSTTS, RM30101.VOIDSTTS ) = 0
        )
    )'
)

--if( @inGenerateUnitTestOutput = 1 )
--begin
--    select 'Insert #OrdBal 3'
--    select * from #OrderBalance
--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 = RM00101.CUSTNAME,
        StatementName = RM00101.STMTNAME,
        CustomerNumber = RM00101.CUSTNMBR,
        CustomerAddress1 = RM00101.ADDRESS1,
        CustomerAddress2 = RM00101.ADDRESS2,
        CustomerAddress3 = RM00101.ADDRESS3,
        CustomerCity = RM00101.CITY,
        CustomerState = RM00101.STATE,
        CustomerPostalCode = RM00101.ZIP,
        CustomerCountry = RM00101.COUNTRY,
        PaymentTerms = RM00101.PYMTRMID,
        CurrentAmount = 0,
        From30To60Amount = 0,
        From61To90Amount = 0,
        From91To120Amount = 0,
        GreaterThan120Amount = 0,
        CustomerClass = RM00101.CUSTCLAS,
        StatementCycle = CustomerStatementStatementCycle.StatementCycle,
        EmailAddress = rtrim( SY01200.INET1 )
    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
    ) Detail
    left outer join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = Detail.CustomerNumber
    left outer join CustomerStatementStatementCycle on CustomerStatementStatementCycle.StatementCycleID = RM00101.STMTCYCL
    left outer join SY01200_Synonym as SY01200 on SY01200.Master_Type = ''CUS'' and SY01200.Master_ID = RM00101.CUSTNMBR and SY01200.ADRSCODE = RM00101.ADRSCODE
    where Detail.Status not like ''%ApplyTo''
    group by
        RM00101.CUSTNAME,
        RM00101.STMTNAME,
        RM00101.CUSTNMBR,
        RM00101.ADDRESS1,
        RM00101.ADDRESS2,
        RM00101.ADDRESS3,
        RM00101.CITY,
        RM00101.STATE,
        RM00101.ZIP,
        RM00101.COUNTRY,
        RM00101.PYMTRMID,
        RM00101.CUSTCLAS,
        CustomerStatementStatementCycle.StatementCycle,
        SY01200.INET1'
)

-- 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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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 Detail.InvoiceDate = '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.
--    -- Results Table #1
--    select
--        [Table] = 'SP-Detail',
--        TypeFID = CustomerStatementDocumentType.DocumentTypeID,
--        StmtDocType = left( CustomerStatementDocumentType.[Description], 15),
--        DocumentN0 = left( Detail.DocumentNumber, 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
--    order by CustomerStatementDocumentType.DocumentTypeID,
--        Detail.DocumentNumber,
--        Detail.DocumentDate
--
--    -- Results Table #2
--    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_Legacy] TO [MssExec]
GO