/**
*    Builds the GPWarehouse data when XLedger is the accounting system.
*
*    Called from the BuildGPWarehouse stored proc.
*
*/

CREATE procedure [dbo].[BuildGPWarehouse_XLedger]
    @inUsePostingDateForGPWarehouseAging bit
as
set nocount on

declare @theInitializedGlobalSystemOptionName varchar(50) = 'XledgerFullyAppliedAcctTransactionsInitialized'
declare @theGsoValue varchar(5) = 'false'

if( @theGsoValue = isnull( (
    select dbo.GetBooleanVarChar( [Value] )
    from GlobalSystemOption
    where [Name] = @theInitializedGlobalSystemOptionName ), @theGsoValue ) )
begin
    -- Only do this once on any database the first time that we build the
    -- GP warehouse for XLedger.  We'll mark any prior accounting system
    -- AcctTransactions and AcctTransactionsApplyToInfos as 'fully applied'.

    -- The purpose of the session parameter is to prevent accidental
    -- invocation of BuildGPWarehouse_XLedger_Initialize SP.
    declare @theSession varbinary(128) = dbo.GetHashKey( @theInitializedGlobalSystemOptionName, '098dgfs8@hn097a8df@9dsnfbh284609582734613138757474615150346834b8aer' )
    exec BuildGPWarehouse_XLedger_Initialize
        @inSessionId = @theSession
end

/**********************************************************
    Populate the GP Warehouse using data from the
    XLedger accounting system.
**********************************************************/
-- Step 1: Get all the A/R invoices, debits and credits that are not apply tos.
insert into GPWarehouse
(
    -- Customer Info
    CustomerNumber,
    AccountingCustomerFid,

    -- Order Info
    OrderID,
    Authority,
    OrderNumber,
    Salesperson,
    Shipper,
    OrderOriginAddress,
    OrderDestinationAddress,
    BranchID,
    BranchFID,

    -- Invoice Info
    InvoiceNumber,
    InvoiceDate,
    InvoiceAmount,
    InvoiceAge,
    InvoiceCreatedDate,
    InvoiceCreatedBy,
    InvoicedHeaderID,

    -- Document Info
    DocumentNumber,
    DocumentDescription,
    DocumentDate,
    DueDate,
    PostDate,
    JournalDate,
    DocumentType,
    OriginalTransactionAmount,
    CurrentTransactionAmount,
    DocumentAge,
    ItemCode,
    PONumber,
    GBLNumber,
    AcctTransactionFid
)
select
    -- CustomerInfo
    CustomerNumber = AccountingCustomer.CustomerNumber,
    AccountingCustomerFid = AccountingCustomer.AccountingCustomerId,

    -- OrderInfo
    OrderID = Orders.PriKey,
    Authority = AcctTransactions.Authority,
    OrderNumber = case
        when rtrim( AcctTransactions.OrderNo ) = '' then null
        else rtrim( AcctTransactions.OrderNo )
    end,
    Salesperson = rtrim( AcctTransactions.SalesPerson ),
    Shipper = case
        when Orders.PriKey is not null then ltrim( isnull( Orders.FirstName + ' ', '' ) + Orders.LastName )
        when AcctTransactions.ShipperName = '' then null
        else AcctTransactions.ShipperName
    end,
    OrderOriginAddress = dbo.BuildAddressCityStateCountryPostalCode( null, OrderAddresses.OriginCity, OrderAddresses.OriginState, null, null ),
    OrderDestinationAddress = dbo.BuildAddressCityStateCountryPostalCode( null, OrderAddresses.DestinationCity, OrderAddresses.DestinationState, null, null ),
    BranchID = rtrim( AcctTransactions.BranchID ),
    BranchFID = isnull( Branch.BranchPriKey, -1 ),

    -- InvoiceInfo
    InvoiceNumber = case
        when InvoicedHeader.IHPriKey is null then null
        else AcctTransactions.InvoiceNumber
    end,
    InvoiceDate = case
        when InvoicedHeader.IHPriKey is null then null
        else AcctTransactions.InvoiceDate
    end,
    InvoiceAmount = InvoicedHeader.InvoiceTotal,
    InvoiceAge = datediff( day, InvoicedHeader.InvoiceDate, getdate() ),
    InvoiceCreatedDate = InvoicedHeader.CreatedDate,
    InvoiceCreatedBy = dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME ),
    InvoicedHeaderID = InvoicedHeader.IHPriKey,

    -- DocumentInfo
    DocumentNumber = AcctTransactions.DocumentNumber,
    DocumentDescription = AcctTransactions.ItemDescription,
    DocumentDate = AcctTransactions.DocumentDate,
    DueDate = InvoicedHeader.DueDate,
    PostDate = convert( date, isnull( XLedgerJournalEntryBatch.CompletedOn, XLedgerJournalEntryBatch.CreatedOn ) ),
    JournalDate = AcctTransactions.JournalDate,
    DocumentType = 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,
    OriginalTransactionAmount = case AcctTransactions.TransactionType
        when 'A/R Credit Memo' then -AcctTransactions.TransactionAmount
        else AcctTransactions.TransactionAmount
    end,
    -- CurrentTransactionAmount will get updated via ApplyTos
    CurrentTransactionAmount = case AcctTransactions.TransactionType
        when 'A/R Credit Memo' then -AcctTransactions.TransactionAmount
        else AcctTransactions.TransactionAmount
    end,
    DocumentAge = datediff( day, case @inUsePostingDateForGPWarehouseAging
        when 1 then convert( datetime, convert( date, isnull( XLedgerJournalEntryBatch.CompletedOn, XLedgerJournalEntryBatch.CreatedOn ) ) )
        else AcctTransactions.DocumentDate
    end, getdate() ),
    ItemCode = AcctTransactions.ItemCode,
    PONumber = case
        when InvoicedHeader.PONumber is null then Orders.PurchaseOrderNo
        else InvoicedHeader.PONumber
    end,
    GBLNumber = MilitaryOrder.GBLNumber,
    AcctTransactionFid = AcctTransactions.ATPriKey
from XLedgerJournalEntryBatchItem
inner join XLedgerJournalEntryBatch on XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid
inner join AcctTransactions on AcctTransactions.ATPriKey = XLedgerJournalEntryBatchItem.AcctTransactionFid
inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
inner join AccountingCustomer on AccountingCustomer.AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid
left outer join Orders on Orders.PriKey = AcctTransactions.OrdPriKey
left outer join OrderAddresses on AcctTransactions.OrdPriKey = OrderAddresses.OrderFID
left outer join MilitaryOrder on Orders.PriKey = MilitaryOrder.OrdersFID
left outer join Branch on AcctTransactions.BranchID = Branch.BranchID
left outer join InvoicedHeader on
(
    AcctTransactions.InvoiceNumber = InvoicedHeader.InvoiceNumber and
    AcctTransactions.OrdPriKey = InvoicedHeader.OrdPriKey
)
left outer join Sysuser on InvoicedHeader.CreatedByFID = Sysuser.SysUserID
-- We only want the non-apply to AcctTransactions.
left outer join AcctTransactionsApplyToInfo on AcctTransactionsApplyToInfo.AcctTransactionsFID = AcctTransactions.ATPriKey
where XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null and
    AcctTransactions.FullyAppliedTo = 0 and
    AcctTransactions.TransactionType in ( 'A/R Sales / Invoice', 'A/R Debit Memo', 'A/R Credit Memo' ) and
    AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoID is null

-- Step 2: Get all the A/R "apply tos".  These all typically reduce the balances on the items
-- from step 1.
;with AppliedToAmounts
(
    ApplyToDocument,
    ApplyToDocumentType,
    ApplyAmount
) as
(
    select
        AcctTransactionsApplyToInfo.ApplyToDocument,
        AcctTransactionsApplyToInfo.ApplyToDocumentType,
        ApplyAmount = sum( AcctTransactionsApplyToInfo.ApplyAmount )
    from AcctTransactionsApplyToInfo
    inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
    inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactions.ATPriKey and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
    where AcctTransactionsApplyToInfo.FullyAppliedTo = 0
    group by
        AcctTransactionsApplyToInfo.ApplyToDocument,
        AcctTransactionsApplyToInfo.ApplyToDocumentType
),

TargetedAcctTransaction
(
    ATPriKey,
    TransactionType,
    ApplyAmount
) as
(
    select
        AppliedToAcctTransactions.ATPriKey,
        AppliedToAcctTransactions.TransactionType,
        AppliedToAmounts.ApplyAmount
    from AppliedToAmounts
    cross apply
    (
        select top 1
            AcctTransactions.ATPriKey,
            AcctTransactions.DocumentNumber,
            AcctTransactions.TransactionType
        from AcctTransactions
        where AcctTransactions.DocumentNumber = AppliedToAmounts.ApplyToDocument and
            AcctTransactions.TransactionType = AppliedToAmounts.ApplyToDocumentType and
            AcctTransactions.FullyAppliedTo = 0
        order by AcctTransactions.ATPriKey
    ) as AppliedToAcctTransactions
)
update GPWarehouse set
    CurrentTransactionAmount = GPWarehouse.CurrentTransactionAmount + case TargetedAcctTransaction.TransactionType
        when 'A/R Credit Memo' then TargetedAcctTransaction.ApplyAmount
        else -TargetedAcctTransaction.ApplyAmount
    end
from GPWarehouse
inner join TargetedAcctTransaction on TargetedAcctTransaction.ATPriKey = GPWarehouse.AcctTransactionFid

declare @FullyAppliedAcctTransactions IntList

-- Remove all zero balance GPWarehouse items.
delete GPWarehouse
output DELETED.AcctTransactionFid
into @FullyAppliedAcctTransactions( Item )
from GPWarehouse
where GPWarehouse.CurrentTransactionAmount = 0

-- Don't need to mark things fully applied unless we had some.
if( @@ROWCOUNT > 0 )
begin
    -- Mark any AcctTransactions that are now fully applied.  Fully
    -- applied AcctTransactions are ignored on future runs of this SP.
    update AcctTransactions set
        FullyAppliedTo = 1
    from @FullyAppliedAcctTransactions as theFullyAppliedAcctTransactions
    inner join AcctTransactions on AcctTransactions.ATPriKey = theFullyAppliedAcctTransactions.Item

    declare @FullyAppliedAcctTransactionsApplyToInfo IntList

    -- Mark the AcctTransactionsApplyToInfos that are now fully applied.  Fully
    -- applied AcctTransactionsApplyToInfos are ignored on future runs of this SP.
    update AcctTransactionsApplyToInfo set
        FullyAppliedTo = 1
    output INSERTED.AcctTransactionsFID
    into @FullyAppliedAcctTransactionsApplyToInfo( Item )
    from @FullyAppliedAcctTransactions as theFullyAppliedAcctTransactions
    inner join AcctTransactions on AcctTransactions.ATPriKey = theFullyAppliedAcctTransactions.Item
    inner join AcctTransactionsApplyToInfo on AcctTransactions.DocumentNumber = AcctTransactionsApplyToInfo.ApplyToDocument and
        AcctTransactions.TransactionType = AcctTransactionsApplyToInfo.ApplyToDocumentType

    if( @@ROWCOUNT > 0 )
    begin
        -- This is updating the AcctTransactions that are linked to AcctTransactionsApplyToInfo
        -- that are now fully applied.  This helps step 1 weed out the AcctTranactions that
        -- are linked to one or more AcctTransactionsApplyToInfo to make that step faster.
        update AcctTransactions set
            FullyAppliedTo = 1
        from @FullyAppliedAcctTransactionsApplyToInfo as theFullyAppliedAcctTransactionsApplyToInfo
        inner join AcctTransactions on AcctTransactions.ATPriKey = theFullyAppliedAcctTransactionsApplyToInfo.Item
        where AcctTransactions.FullyAppliedTo = 0
    end
end

/*
-- Populate the IsFactored column of the GPWarehouse table using the MSS10001 table
-- We only need to do this if the user has the Factoring feature and the MSS10001 table.
-- For XLedger, we are to ignore factoring!
if( @theHasMSS10001 = 1 )
begin
    update GPWarehouse set
        IsFactored = case MSS10001.MSS_Factor
            when 'YES' then 1
            else 0
        end
    from GPWarehouse
    inner join MSS10001_Synonym as MSS10001 on
        MSS10001.SERIES = 3 and
        MSS10001.DOCTYPE = GPWarehouse.DocumentType and
        MSS10001.DOCNUMBR = GPWarehouse.DocumentNumber
end
*/

-- Define a temp table so that we can update the invoice age of non-invoiced items
-- by using their document age.
declare @theNonInvoicedItems table
(
    CustomerNumber varchar(15),
    OrderID int,
    OrderNumber varchar(23),
    Shipper varchar(44),
    MaxDocumentAge int
)

-- For those items that have not been invoiced, determine the oldest
-- detail based upon Document Age
insert into @theNonInvoicedItems
(
    CustomerNumber,
    OrderID,
    OrderNumber,
    Shipper,
    MaxDocumentAge
)
select
    CustomerNumber = CustomerNumber,
    OrderID = isnull( OrderID, -1 ),
    OrderNumber = isnull( OrderNumber, '' ),
    Shipper = isnull( Shipper, '' ),
    MaxDocumentAge = max( DocumentAge )
from GPWarehouse
where
    InvoiceAge is null and
    InvoiceNumber is null
group by
    CustomerNumber,
    OrderID,
    OrderNumber,
    Shipper
order by
    CustomerNumber,
    OrderID,
    OrderNumber,
    Shipper

-- Now, update the non-invoiced items with the age of the oldest
-- non-invoiced document.
update GPWarehouse set
    GPWarehouse.InvoiceAge = theNonInvoicedItems.MaxDocumentAge
from GPWarehouse
inner join @theNonInvoicedItems as theNonInvoicedItems on
    GPWarehouse.CustomerNumber = theNonInvoicedItems.CustomerNumber and
    isnull( GPWarehouse.OrderID, -1 ) = theNonInvoicedItems.OrderID and
    isnull( GPWarehouse.OrderNumber, '' ) = theNonInvoicedItems.OrderNumber and
    isnull( GPWarehouse.Shipper, '' ) = theNonInvoicedItems.Shipper
where
    GPWarehouse.InvoiceAge is null and
    GPWarehouse.InvoiceNumber is null

-- populate the GP Customer table
declare @theNoCreditLimit int = ( select AccountingCustomerCreditLimitTypeId from AccountingCustomerCreditLimitType where TypeName = 'No Credit' )
declare @theAmountCreditLimit int = ( select AccountingCustomerCreditLimitTypeId from AccountingCustomerCreditLimitType where TypeName = 'Amount' )
declare @theUnlimitedCreditLimit int = ( select AccountingCustomerCreditLimitTypeId from AccountingCustomerCreditLimitType where TypeName = 'Unlimited' )

insert into GPCustomer
(
    -- Customer Info
    CustomerClass,
    CustomerNumber,
    AccountingCustomerFid,
    CustomerName,
    CustomerBalance,
    CustomerAge,
    LastPaymentDate,
    LastPaymentAmount,
    CreditLimit,
    CreditLimitType,
    AccountingCustomerCreditLimitTypeFid
)
select
    -- CustomerInfo
    CustomerClass = rtrim( AccountingCustomerClass.ClassCode ),
    CustomerNumber = rtrim( AccountingCustomer.CustomerNumber ),
    AccountingCustomerFid = AccountingCustomer.AccountingCustomerId,
    CustomerName = rtrim( AccountingCustomer.[Name] ),
    CustomerBalance = cast( 0 as money ),
    CustomerAge = cast( null as int ),
    LastPaymentDate = cast( null as datetime ),
    LastPaymentAmount = cast( null as money ),
    CreditLimit = AccountingCustomer.CreditLimit,
    CreditLimitType = isnull( AccountingCustomerCreditLimitType.TypeName, 'No Credit' ),
    AccountingCustomerCreditLimitTypeFid = isnull( AccountingCustomer.AccountingCustomerCreditLimitTypeFid, @theNoCreditLimit )
from AccountingCustomer
left outer join AccountingCustomerClass on AccountingCustomerClass.AccountingCustomerClassId = AccountingCustomer.AccountingCustomerClassFid
left outer join AccountingCustomerCreditLimitType on AccountingCustomerCreditLimitType.AccountingCustomerCreditLimitTypeId = AccountingCustomer.AccountingCustomerCreditLimitTypeFid
where AccountingCustomer.[Hidden] = 0

-- Update the CustomerBalance by summing each individual transaction amount
update GPCustomer set
    CustomerBalance =
    (
        select sum( isnull( GPWarehouse.CurrentTransactionAmount, 0 ) )
        from GPWarehouse
        where GPWarehouse.AccountingCustomerFid = GPCustomer.AccountingCustomerFid
    ),
    CustomerAge =
    (
        select max( isnull( GPWarehouse.InvoiceAge, isnull( GPWarehouse.DocumentAge, 0 ) ) )
        from GPWarehouse
        where GPWarehouse.AccountingCustomerFid = GPCustomer.AccountingCustomerFid
    )

-- Set the date of the last payment made by the customer (determined by the last open or historical payment for the customer in GP).
-- If the payment came from MSS, use the date from the MSS statement instead of the date recorded for that payment in GP.
;with AppliedToPayments
(
    AccountingCustomerFid,
    LastPaymentDate
) as
(
    select
        XLedgerCustomer.AccountingCustomerFid,
        LastPaymentDate = max( isnull( StatementDetail.DateTimeCreated, AcctTransactions.DocumentDate ) )
    from AcctTransactionsApplyToInfo
    inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
    inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactionsApplyToInfo.AcctTransactionsFID and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    left outer join StatementDetailDist on
        AcctTransactions.Source = 'StatementDetailDist' and
        AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
    left outer join StatementDetail on
        StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
    where AcctTransactions.TransactionType = 'A/R Cash Receipt'
    group by
        XLedgerCustomer.AccountingCustomerFid
)
update GPCustomer set
    LastPaymentDate = AppliedToPayments.LastPaymentDate
from GPCustomer
inner join AppliedToPayments on GPCustomer.AccountingCustomerFid = AppliedToPayments.AccountingCustomerFid

-- Set the amount of the last payment made by the customer that is in an "Open" state in GP (from the RM20101 table).
-- If the payment came from MSS, use the amount from the MSS statement instead of the amount recorded for that payment in GP.
;with AppliedToPayments
(
    AccountingCustomerFid,
    LastPaymentAmount
) as
(
    select
        XLedgerCustomer.AccountingCustomerFid,
        LastPaymentAmount = max( isnull( StatementDetail.Amount, AcctTransactions.TransactionAmount ) )
    from AcctTransactionsApplyToInfo
    inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
    inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactionsApplyToInfo.AcctTransactionsFID and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
    inner join XLedgerCustomer on XLedgerCustomer.ExternalDbId = XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId
    inner join GPCustomer as InnerGPCustomer on
        InnerGPCustomer.AccountingCustomerFid = XLedgerCustomer.AccountingCustomerFid
    left outer join StatementDetailDist on
        AcctTransactions.Source = 'StatementDetailDist' and
        AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
    left outer join StatementDetail on
        StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
    where AcctTransactions.TransactionType = 'A/R Cash Receipt' and
        InnerGPCustomer.LastPaymentDate = isnull( StatementDetail.DateTimeCreated, AcctTransactions.DocumentDate )
    group by
        XLedgerCustomer.AccountingCustomerFid
)
update GPCustomer set
    LastPaymentAmount = AppliedToPayments.LastPaymentAmount
from GPCustomer
inner join AppliedToPayments on GPCustomer.AccountingCustomerFid = AppliedToPayments.AccountingCustomerFid
where AppliedToPayments.LastPaymentAmount is not null
GO
GRANT EXECUTE ON  [dbo].[BuildGPWarehouse_XLedger] TO [MssExec]
GO