/**
*
* Description: Build the Apply grid from XLedger data.
*
* Compare and keep in sync with the UpdateXLApplyGridOriginalBalances stored proc.
*
* Error Codes:
*
* Note that @SDDPriKey will be 0 or null when @inCustomerNumber or @inVendorNumber is not null and not empty and
* that @SDDPriKey will be not null and > 0 when @inCustomerNumber and @inVendorNumber are null or empty.
*
* Note that @SDDPriKey will be 0 or null when @inCustomerNumber is not null and not empty and
* that @SDDPriKey will be not null and > 0 when @inCustomerNumber is null or empty.
* @inCustomerNumber When set, builds the apply grid for data for the specified customer, which isn't tied to an order.
* @inOrderCustomerNumber: when specified, statement documents (and only statement documents) will be generated for
* records tied to this customer number, and which are on the same order as the provided StatementDetailDist.
*/

CREATE PROCEDURE [dbo].[spAPPG_XLRunApplyGrid]
    @SDPriKey int,
    @SDDPriKey int,
    @inCustomerNumber varchar(15) = null,
    @inVendorNumber varchar(15) = null,
    @inOrderCustomerNumber varchar(15) = null
as

set nocount on

declare @theOrderID int
declare @theOrderPriKeyStr nvarchar(12)
declare @theSQL nvarchar( max )
declare @thePostingStatus varchar(10)

declare @theStatementDocumentPriKeysTable table
(
    StatementDocumentID int
)

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

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

if( isnull( rtrim( @inCustomerNumber ), '' ) = '' )
begin
    set @inCustomerNumber = null
end

if( isnull( rtrim( @inOrderCustomerNumber ), '' ) = '' )
begin
    set @inOrderCustomerNumber = null
end

if( isnull( rtrim( @inVendorNumber ), '' ) = '' )
begin
    set @inVendorNumber = null
end

declare @theCustomerHasAlreadyBeenAdded bit
set @theCustomerHasAlreadyBeenAdded = 0
declare @theVendorHasAlreadyBeenAdded bit
set @theVendorHasAlreadyBeenAdded = 0

if( @inVendorNumber is not null )
begin
    -- We are adding vendor data so no customer data is to be added.
    set @theCustomerHasAlreadyBeenAdded = 1

    if( exists ( select 1 from StatementPayable where StatementDetailFID = @SDPriKey and isnull( StatementDetailDistFID, 0 ) = @SDDPriKey and GPFlag = 1 and VendorID = @inVendorNumber ) )
    begin
        -- This vendor has already been added so we do not want to do this again.  Although the app detects this, it cannot tell if another
        -- user already added this vendor.
        set @theVendorHasAlreadyBeenAdded = 1
    end
    else
    begin
        select
            @theOrderID = null,
            @thePostingStatus = StatementDetail.[Status]
        from StatementDetail
        where
            StatementDetail.SDPriKey = @SDPriKey
    end
end
else if( @inCustomerNumber is not null )
begin
    -- We are adding customer data so no vendor data is to be added.
    set @theVendorHasAlreadyBeenAdded = 1
    if( exists ( select 1 from StatementDocument where StDetailPriKey = @SDPriKey and StDetailDistPriKey is null and CustNumber = @inCustomerNumber ) )
    begin
        -- This customer has already been added so we do not want to do this again.  Although the app detects this, it cannot tell if another
        -- user already added this customer.
        set @theCustomerHasAlreadyBeenAdded = 1
    end
    else
    begin
        select
            @theOrderID = null,
            @thePostingStatus = StatementDetail.[Status]
        from StatementDetail
        where
            StatementDetail.SDPriKey = @SDPriKey
    end
end
else
begin
    --if inOrderCustomerNumber is specified, it means we are only interested in the generation of StatementDocuments.
    if( @inOrderCustomerNumber is not null or
        exists ( select 1 from StatementPayable where StatementDetailFID = @SDPriKey and isnull( StatementDetailDistFID, 0 ) = @SDDPriKey and GPFlag = 1 ) )
    begin
        -- Vendor payables from GP have already been imported so we do not want to do this again.  We get here if the order has no
        -- statement documents, which is how spAPPG_BuildApplyGrid determines if this sp needs to be run or not.
        set @theVendorHasAlreadyBeenAdded = 1
    end

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

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

-- If the StatementDetail is posted or voided, there is definitely nothing for us to do.
if( isnull( @thePostingStatus, '' ) in ( 'Posted', 'Voided' ) )
begin
    set @theVendorHasAlreadyBeenAdded = 1
    set @theCustomerHasAlreadyBeenAdded = 1
end

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

    if( @inCustomerNumber is not null )
    begin
        set @theSQL = @theSQL + N'
            AcctTransactions.OrdPriKey is null and
            AccountingCustomer.CustomerNumber = ' + dbo.QuoteString( @inCustomerNumber )
    end
    else
    begin
        set @theSQL = @theSQL + N'
            AcctTransactions.OrdPriKey = ' + @theOrderPriKeyStr
    end

    set @theSQL = @theSQL + N'

        ;with AppliedToAmounts
        (
            ApplyToDocument,
            ApplyToDocumentType,
            ApplyAmount
        ) as
        (
            select
                AcctTransactionsApplyToInfo.ApplyToDocument,
                AcctTransactionsApplyToInfo.ApplyToDocumentType,
                ApplyAmount = sum( AcctTransactionsApplyToInfo.ApplyAmount )
            from AcctTransactionsApplyToInfo
            inner join AcctTransactions on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
            inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactions.ATPriKey and
                XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
            where AcctTransactionsApplyToInfo.FullyAppliedTo = 0 and'

    if( @inCustomerNumber is not null )
    begin
        set @theSQL = @theSQL + N'
                AcctTransactions.OrdPriKey is null and
                AccountingCustomer.CustomerNumber = ' + dbo.QuoteString( @inCustomerNumber )
    end
    else
    begin
        set @theSQL = @theSQL + N'
                AcctTransactions.OrdPriKey = ' + @theOrderPriKeyStr
    end

    set @theSQL = @theSQL + N'
            group by
                AcctTransactionsApplyToInfo.ApplyToDocument,
                AcctTransactionsApplyToInfo.ApplyToDocumentType
        ), TargetedAcctTransaction
        (
            ATPriKey,
            TransactionType,
            ApplyAmount
        ) as
        (
            select top 1
                AcctTransactions.ATPriKey,
                AcctTransactions.TransactionType,
                AppliedToAmounts.ApplyAmount
            from AcctTransactions
            inner join AppliedToAmounts on AcctTransactions.DocumentNumber = AppliedToAmounts.ApplyToDocument and
                AcctTransactions.TransactionType = AppliedToAmounts.ApplyToDocumentType
            where AcctTransactions.FullyAppliedTo = 0
            order by AcctTransactions.ATPriKey desc
        )
        update OpenArItem set
            AppliedAmt = case TargetedAcctTransaction.TransactionType
                when ''A/R Credit Memo'' then -TargetedAcctTransaction.ApplyAmount
                else TargetedAcctTransaction.ApplyAmount
            end
        from #OpenArItem as OpenArItem
        inner join TargetedAcctTransaction on TargetedAcctTransaction.ATPriKey = OpenArItem.AcctTransactionFid
    '
    exec sp_executesql @theSQL

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

    -- By using the list of the StatementDocument primary keys that were just inserted, will use that
    -- temp table for the subsequent sql statements to identify the StatementDocument record(s) of
    -- interest and allow the query plan(s) to be much more efficient.
    update StatementDocument set
        BranchFID = case AcctTransactions.Source
            when 'BillingMinorItem' then BillingMinorItem.ARBranchFID
            when 'BillingMinorDist' then BillingMinorDist.BranchFID
            when 'StatementChargeBack' then
            (
                select isnull( StatementDetailDist.UnappliedBranchFID, StatementDetail.UnappliedBranchFID )
                from StatementDetail
                inner join StatementChargeBack on StatementChargeBack.SDPriKey = StatementDetail.SDPriKey
                left outer join StatementDetailDist on StatementChargeBack.SDDPrikey = StatementDetailDist.SDDPriKey
                where StatementChargeBack.SCBPriKey = AcctTransactions.SourceRecord
            )
            when 'StatementDetail' then
            (
                select StatementDetail.BranchPriKey
                from StatementDetail
                where StatementDetail.SDPriKey = AcctTransactions.SourceRecord
            )
            when 'StatementDetailDist' then
            (
                select StatementDetailDist.UnappliedBranchFID
                from StatementDetailDist
                where StatementDetailDist.SDDPriKey = AcctTransactions.SourceRecord
            )
            when 'StatementDocument' then
            (
                select StatementDocument.BranchFID
                from StatementDocument
                where StatementDocument.SDPriKey = AcctTransactions.SourceRecord
            )
            when 'StatementGLAllocation' then
            (
                select StatementGLAllocation.BranchPriKey
                from StatementGLAllocation
                where StatementGLAllocation.StatementGLAllocationID = AcctTransactions.SourceRecord
            )
            when 'ClaimHandlingRevenue' then
            (
                select ClaimHandlingRevenue.BranchFID
                from ClaimHandlingRevenue
                where ClaimHandlingRevenue.ClaimHandlingRevenueID = AcctTransactions.SourceRecord
            )
            when 'ClaimItemDueFrom' then
            (
                select Claim.BranchFID
                from ClaimItemDueFrom
                inner join ClaimItem on ClaimItemDueFrom.ClaimItemFID = ClaimItem.ClaimItemID
                inner join Claim on ClaimItem.ClaimFID = Claim.ClaimID
                where  AcctTransactions.SourceRecord = ClaimItemDueFrom.ClaimItemDueFromID
            )
            when 'ClaimLiabilityItem' then
            (
                select isnull( ClaimLiability.BranchFID, Claim.BranchFID )
                from ClaimLiabilityItem
                inner join ClaimLiability on ClaimLiabilityItem.ClaimLiabilityFID = ClaimLiability.ClaimLiabilityID
                inner join Claim on ClaimLiability.ClaimFID = Claim.ClaimID
                where  AcctTransactions.SourceRecord = ClaimLiabilityItem.ClaimLiabilityItemID
            )
            when 'ClaimSettlement' then
            (
                select Claim.BranchFID
                from ClaimSettlement
                inner join Claim on ClaimSettlement.ClaimFID = Claim.ClaimID
                where AcctTransactions.SourceRecord = ClaimSettlement.ClaimSettlementID
            )
            when 'CommissionedDetail' then
            (
                select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                from CommissionedDetail
                left outer join BillingMinorDist on CommissionedDetail.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                left outer join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                where AcctTransactions.SourceRecord = CommissionedDetail.CDPriKey
            )
            when 'SplitCommissions' then
            (
                select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                from SplitCommissions
                inner join CommissionedDetail on SplitCommissions.CDPriKey = CommissionedDetail.CDPriKey
                left outer join BillingMinorDist on CommissionedDetail.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                left outer join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                where AcctTransactions.SourceRecord = SplitCommissions.SCPriKey
            )
            when 'ThirdParty' then
            (
                select isnull( BillingMinorDist.BranchFID, BillingMinorItem.ARBranchFID )
                from ThirdParty
                left outer join BillingMinorDist on ThirdParty.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                left outer join BillingMinorItem on ThirdParty.BMinPriKey = BillingMinorItem.BMinPriKey
                where AcctTransactions.SourceRecord = ThirdParty.TPPriKey
            )
            else StatementDetailDist.BranchPriKey
        end
    from @theStatementDocumentPriKeysTable as theStatementDocumentPriKeysTable
    inner join StatementDocument on StatementDocument.SDPriKey = theStatementDocumentPriKeysTable.StatementDocumentID
    left outer join AcctTransactions on StatementDocument.ATPriKey = AcctTransactions.ATPriKey
    left outer join BillingMinorItem on BillingMinorItem.BMinPriKey = StatementDocument.BillingMinorPriKey
    left outer join BillingMinorDist on BillingMinorDist.BMinDistPriKey = StatementDocument.BMinDistPriKey
    left outer join StatementDetailDist on StatementDetailDist.SDDPriKey = StatementDocument.StDetailDistPriKey

    -- Mark this StatementDetail has having its apply grid built.  This is so that the PM Apply Screen knows this has been done
    -- because it is entirely possible that no StatementDocuments were actually created.
    update StatementDetail set
        IsApplyGridBuilt = 1
    from StatementDetail
    where
        StatementDetail.SDPriKey = @SDPriKey

end

if( @theVendorHasAlreadyBeenAdded = 0 )
begin
    -- Add payables that are in GP if we are on an order or for a specific vendor when we are on the no order item.
    -- DOCTYPEs are defined in the PM40102 table.
    set @theSQL = N'select
        StatementDetailFID = ' + ltrim( str( @SDPriKey ) ) + ',
        StatementDetailDistFID = ' + case when @SDDPriKey = 0 then 'null' else ltrim( str( @SDDPriKey ) ) end + ',
        Description =
            case MSS10000.DOCTYPE
                when 1 then ''Invoice''
                when 2 then ''Finance Charge''
                when 3 then ''Misc Charge''
                when 4 then ''Return''
                when 5 then ''Credit Memo''
                when 6 then ''Payment''
                when 7 then ''Schedule''
                else ''Other''
            end + '' - '' +
            rtrim( ltrim( PM20000.TRXDSCRN ) ),
        BranchFID = Branch.BranchPriKey,
        DivisionFID = dbo.GetDefaultDivisionFromBranch( Branch.BranchPriKey ),
        VendorID = PM20000.VENDORID,
        Amount =     
            case
                when MSS10000.DOCTYPE in ( 1, 2, 3 ) then PM20000.CURTRXAM
                else -PM20000.CURTRXAM
            end,
        [1099Flag] = 0,
        DocumentNumber = ltrim( rtrim( PM20000.DOCNUMBR ) ),
        ApplyAmount = 0,
        GPFlag = 1
    from MSS10000_Synonym as MSS10000
    join PM20000_Synonym as PM20000 on MSS10000.docnumbr = PM20000.VCHRNMBR
    join PM00200_Synonym as PM00200 on PM20000.VENDORID = PM00200.VENDORID
    left outer join Branch on MSS10000.MSS_BranchCode = Branch.BranchID
    where
        MSS10000.Series = 4 and
        PM20000.Hold = 0 and
        PM20000.Voided = 0 and'

    if( @inVendorNumber is not null )
    begin
        set @theSQL = @theSQL + N'
        MSS10000.MSS_OrdPriKey = 0 and
        PM20000.CURTRXAM != 0 and
        PM20000.VENDORID = ' + dbo.QuoteString( @inVendorNumber )
    end
    else
    begin
        set @theSQL = @theSQL + N'
        PM20000.CURTRXAM != 0 and
        MSS10000.MSS_OrdPriKey = ' + @theOrderPriKeyStr
    end

    /*
    insert into StatementPayable
    (
        StatementDetailFID,
        StatementDetailDistFID,
        Description,
        BranchFID,
        DivisionFID,
        VendorID,
        Amount,
        [1099Flag],
        DocumentNumber,
        ApplyAmount,
        GPFlag
    )
    exec sp_executesql @theSQL
    */
end

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