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

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

-- Declare Great Plains tables used in the query
declare @theHasMSS10001 bit = 0

-- Eventually, all GP users will have the MSS10001 table, but for now, we don't ASSUME that it is there.
-- If it is not there, then nothing can be factored.  And, if you do not have the factoring security module
-- activated, then nothing can be factored.
if( exists( select top 1 1 from SecModules where [Description] = 'Factoring' and Active = 1 ) )
begin
    if( exists( select top 1 1 from SysObjects_Synonym where type = 'U' and name = 'MSS10001' ) )
    begin
        set @theHasMSS10001 = 1
    end
    else
    begin
        set @theHasMSS10001 = 0
    end
end

/**********************************************************
    Populate the GP Warehouse using data from the
    Great Plains Dynamics accounting system.
**********************************************************/
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 = rtrim( RM20101.CUSTNMBR ),
    AccountingCustomerFid = null,

    -- OrderInfo
    OrderID = case
                when MSS10000.MSS_OrdPriKey = 0 then null
                else MSS10000.MSS_OrdPriKey
            end,
    Authority = rtrim( MSS10000.MSS_Authority ),
    OrderNumber =
        case
            when rtrim( MSS10000.MSS_OrderNo ) = '' then null
            else rtrim( MSS10000.MSS_OrderNo )
        end,
    Salesperson = rtrim( MSS10000.MSS_SalesPerson ),
    Shipper =
        case
            when Orders.PriKey is not null then ltrim( isnull( Orders.FirstName + ' ', '' ) + Orders.LastName )
            when rtrim( MSS10000.MSS_ShipperName ) = '' then null
            else rtrim( MSS10000.MSS_ShipperName )
        end,
    OrderOriginAddress = dbo.BuildAddressCityStateCountryPostalCode( null, OrderAddresses.OriginCity, OrderAddresses.OriginState, null, null ),
    OrderDestinationAddress = dbo.BuildAddressCityStateCountryPostalCode( null, OrderAddresses.DestinationCity, OrderAddresses.DestinationState, null, null ),
    BranchID = rtrim( MSS10000.MSS_BranchCode ),
    BranchFID = isnull( Branch.BranchPriKey, -1 ),

    -- InvoiceInfo
    InvoiceNumber =
        case
            when InvoicedHeader.IHPriKey is null then null
            else rtrim( MSS10000.MSS_InvoiceNumber )
        end,
    InvoiceDate =
        case
            when InvoicedHeader.IHPriKey is null then null
            else rtrim( MSS10000.MSS_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 = rtrim( RM20101.DOCNUMBR ),
    DocumentDescription = rtrim( RM20101.TRXDSCRN ),
    DocumentDate = RM20101.DOCDATE,
    DueDate = RM20101.DUEDATE,
    PostDate = RM20101.POSTDATE,
    JournalDate = RM20101.GLPOSTDT,
    DocumentType = RM20101.RMDTYPAL,
    OriginalTransactionAmount =
        case
            when RM20101.RMDTYPAL in ( 7, 8, 9 ) then -RM20101.ORTRXAMT
            else RM20101.ORTRXAMT
        end,
    CurrentTransactionAmount =
        case
            when RM20101.RMDTYPAL in ( 7, 8, 9 ) then -RM20101.CURTRXAM
            else RM20101.CURTRXAM
        end,
    DocumentAge = datediff( day,
        case @inUsePostingDateForGPWarehouseAging
            when 1 then RM20101.POSTDATE
            else RM20101.DOCDATE
        end, getdate() ),
    ItemCode = case
                when MSS10000.MSS_ItemCode = 0 then null
                else MSS10000.MSS_ItemCode
            end,
    PONumber =
        case
            when InvoicedHeader.PONumber is null then rtrim( Orders.PurchaseOrderNo )
            else rtrim( InvoicedHeader.PONumber )
        end,
    GBLNumber = MilitaryOrder.GBLNumber,
    AcctTransactionFid = MSS10000.MSS_ATPriKey
from RM20101_Synonym as RM20101
inner join RM00101_Synonym as RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
left outer join MSS10000_Synonym as MSS10000 on
    MSS10000.DOCNUMBR = RM20101.DOCNUMBR and
    MSS10000.DOCTYPE = RM20101.RMDTYPAL and
    MSS10000.SERIES = 3
left outer join InvoicedHeader on
(
    MSS10000.MSS_InvoiceNumber = InvoicedHeader.InvoiceNumber and
    MSS10000.MSS_OrdPriKey = InvoicedHeader.OrdPriKey
)
left outer join Sysuser on InvoicedHeader.CreatedByFID = Sysuser.SysUserID
left outer join OrderAddresses on MSS10000.MSS_OrdPriKey = OrderAddresses.OrderFID
left outer join Orders on MSS10000.MSS_OrdPriKey = Orders.PriKey
left outer join MilitaryOrder on Orders.PriKey = MilitaryOrder.OrdersFID
left outer join Branch on MSS10000.MSS_BranchCode = Branch.BranchID
where
    RM00101.INACTIVE = 0 and
    RM20101.CURTRXAM != 0

-- 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.
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

declare @theNoCreditLimit int = ( select AccountingCustomerCreditLimitTypeId from AccountingCustomerCreditLimitType where TypeName = 'No Credit' )

-- populate the GP Customer table
insert into GPCustomer
(
    -- Customer Info
    CustomerClass,
    CustomerNumber,
    AccountingCustomerFid,
    CustomerName,
    CustomerBalance,
    CustomerAge,
    LastPaymentDate,
    LastPaymentAmount,
    CreditLimit,
    CreditLimitType,
    AccountingCustomerCreditLimitTypeFid
)
select
    -- CustomerInfo
    CustomerClass = rtrim( RM00101.CUSTCLAS ),
    CustomerNumber = rtrim( RM00101.CUSTNMBR ),
    AccountingCustomerFid = null,
    CustomerName = rtrim( RM00101.CUSTNAME ),
    CustomerBalance = cast( 0 as money ),
    CustomerAge = cast( null as int ),
    LastPaymentDate = cast( null as datetime ),
    LastPaymentAmount = cast( null as money ),
    CreditLimit = RM00101.CRLMTAMT,
    CreditLimitType = isnull( AccountingCustomerCreditLimitType.TypeName,'No Credit' ),
    AccountingCustomerCreditLimitTypeFid = isnull( AccountingCustomerCreditLimitType.AccountingCustomerCreditLimitTypeId, @theNoCreditLimit )
from RM00101_Synonym as RM00101
left outer join AccountingCustomerCreditLimitType on AccountingCustomerCreditLimitType.GPCreditLimitTypeCode = RM00101.CRLMTTYP
where RM00101.INACTIVE = 0

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

-- 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.
update GPCustomer set
    LastPaymentDate = CustomerLastPaymentDate.LastPaymentDate
from
(
    select
        CustomerNumber,
        LastPaymentDate = max( LastPaymentDate )
    from
    (
        select
            CustomerNumber = RM20101.CUSTNMBR,
            LastPaymentDate = max( isnull( StatementDetail.DateTimeCreated, RM20101.DOCDATE ) )
        from RM20101_Synonym as RM20101
        left outer join MSS10000_Synonym as MSS10000 on
            RM20101.DOCNUMBR = MSS10000.DOCNUMBR and
            MSS10000.DOCTYPE = RM20101.RMDTYPAL and
            MSS10000.SERIES = 3
        left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
        left outer join StatementDetailDist on
            AcctTransactions.Source = 'StatementDetailDist' and
            AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
        left outer join StatementDetail on
            StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
        where
            RM20101.RMDTYPAL = 9
        group by RM20101.CUSTNMBR
        union
        select
            CustomerNumber = RM30101.CUSTNMBR,
            LastPaymentDate = max( isnull( StatementDetail.DateTimeCreated, RM30101.DOCDATE ) )
        from RM30101_Synonym as RM30101
        left outer join MSS10000_Synonym as MSS10000 on
            RM30101.DOCNUMBR = MSS10000.DOCNUMBR and
            MSS10000.DOCTYPE = RM30101.RMDTYPAL and
            MSS10000.SERIES = 3
        left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
        left outer join StatementDetailDist on
            AcctTransactions.Source = 'StatementDetailDist' and
            AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
        left outer join StatementDetail on
            StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
        where
            RM30101.RMDTYPAL = 9
        group by RM30101.CUSTNMBR
    ) as LastPaymentDates
    group by CustomerNumber
) as CustomerLastPaymentDate
where GPCustomer.CustomerNumber = CustomerLastPaymentDate.CustomerNumber

-- 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.
update GPCustomer set
    LastPaymentAmount = LastPaymentAmounts.LastPaymentAmount
from
(
    select
        CustomerNumber = RM20101.CUSTNMBR,
        LastPaymentAmount = max( isnull( StatementDetail.Amount, RM20101.ORTRXAMT ) )
    from RM20101_Synonym as RM20101
    inner join GPCustomer as InnerGPCustomer on
        InnerGPCustomer.CustomerNumber = RM20101.CUSTNMBR
    left outer join MSS10000_Synonym as MSS10000 on
        RM20101.DOCNUMBR = MSS10000.DOCNUMBR and
        MSS10000.DOCTYPE = RM20101.RMDTYPAL and
        MSS10000.SERIES = 3
    left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
    left outer join StatementDetailDist on
        AcctTransactions.Source = 'StatementDetailDist' and
        AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
    left outer join StatementDetail on
        StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
    where
        RM20101.RMDTYPAL = 9 and
        InnerGPCustomer.LastPaymentDate = isnull( StatementDetail.DateTimeCreated, RM20101.DOCDATE )
    group by RM20101.CUSTNMBR
) as LastPaymentAmounts
where GPCustomer.CustomerNumber = LastPaymentAmounts.CustomerNumber and
    LastPaymentAmounts.LastPaymentAmount is not null

-- Set the amount of the last payment made by the customer that is in a "History" state in GP (from the RM30101 table) if the
-- History amount is greater than the Open amount for the customer on that date.
-- In other words, if payments for a customer are recorded in RM20101 and RM30101 for the customer's last payment date, this
-- statement will only replace the last payment amount for that customer in GPCustomer if RM30101's amount is greater than RM20101's amount.
-- This is an arbitrary way of choosing a payment amount when there are payments for the last payment date in both tables.
-- If the payment came from MSS, use the amount from the MSS statement instead of the amount recorded for that payment in GP.
update GPCustomer set
    LastPaymentAmount =
        case
            when ( LastPaymentAmounts.LastPaymentAmount > isnull( GPCustomer.LastPaymentAmount, 0 ) )
                then LastPaymentAmounts.LastPaymentAmount
            else GPCustomer.LastPaymentAmount
        end
from
(
    select
        CustomerNumber = RM30101.CUSTNMBR,
        LastPaymentAmount = max( isnull( StatementDetail.Amount, RM30101.ORTRXAMT ) )
    from RM30101_Synonym as RM30101
    inner join GPCustomer as InnerGPCustomer on
        InnerGPCustomer.CustomerNumber = RM30101.CUSTNMBR
    left outer join MSS10000_Synonym as MSS10000 on
        RM30101.DOCNUMBR = MSS10000.DOCNUMBR and
        MSS10000.DOCTYPE = RM30101.RMDTYPAL and
        MSS10000.SERIES = 3
    left outer join AcctTransactions on AcctTransactions.ATPriKey = MSS10000.MSS_ATPriKey
    left outer join StatementDetailDist on
        AcctTransactions.Source = 'StatementDetailDist' and
        AcctTransactions.SourceRecord = StatementDetailDist.SDDPriKey
    left outer join StatementDetail on
        StatementDetailDist.SDPriKey = StatementDetail.SDPriKey
    where
        RM30101.RMDTYPAL = 9 and
        InnerGPCustomer.LastPaymentDate = isnull( StatementDetail.DateTimeCreated, RM30101.DOCDATE )
    group by RM30101.CUSTNMBR
) as LastPaymentAmounts
where GPCustomer.CustomerNumber = LastPaymentAmounts.CustomerNumber and
    LastPaymentAmounts.LastPaymentAmount is not null
GO
GRANT EXECUTE ON  [dbo].[BuildGPWarehouse_Legacy] TO [MssExec]
GO