/**
* Don't use this stored proc directly but instead use spCSMBuildStatement_Synonym.
* spCSMBuildStatement_Synonym will either point to this stored proc or to
* spCSMBuildStatement_Legacy.
*
* This procedure is called to create records in the commission statement tables from MoversSuite transactions.
* Generates all commission statements for the vendors in the vendor class.
* This "XLedger" stored proc only works with XLedger, and returns an error code of 1 if the accounting system is not XLedger.
*    
*    @param @VendorClass The vendor class that the information is built for.
*    @param @DueDate The date that the commission is due.
*    @param @CreatedBy Primary key of a SysUser record. Identifies the person creating this commission statement.
*    @param @CSHPriKey Outputs the primary key of the CommStmtHeader record that this stored proc created for the commission statement.
*/
create procedure [dbo].[spCSMBuildStatement_XLedger]
    @VendorClass    varchar(15),
    @DueDate         datetime,
    @CreatedBy         int,
    @CSHPriKey        int output
as
set nocount on

declare @theLastStmtDate datetime
declare @TRUE bit
set @TRUE = 1

-- Create a temporary table.
create table #Transactions
(
    DocumentNumber varchar(21) not null default '',
    DocumentDate datetime not null default '1/1/1900',
    DocumentAmount money not null default 0,
    OrderNumber varchar(23) not null default '',
    ShipperName varchar(50) not null default '',
    InvoiceAmount money not null default 0,
    RevenueAmount money not null default 0,
    CommissionType varchar(8) not null default '',
    CommissionBasis varchar(15) not null default '',
    CommissionAmount money not null default 0,
    CommissionPerc float not null default 0,
    CommissionFactor money not null default 0,
    OrderWeight int not null default 0,
    AmountAfterSplits money not null default 0,
    ItemDescription varchar(128) not null default 'None',
    AllocatedItemDescription varchar(128) null,
    VoucherNumber varchar(21) not null default '',
    AcctTransactionID int null,
    VendorId char(15) null,
    VendorName char(65) null,
    NetAmount money not null default 0,
    Miles int,
    AccountingVendorFid int null
);
        
-- Get the transactions from AcctTransactions.
-- Using CTEs to determine Hold Status based on most recent hold records.
with SplitHold_CTE as (
    select
        SplitCommissionsFID,
        HoldUpdateStatus,
        row_number() over (partition by SplitCommissionsFID order by SplitCommissionsHoldID desc) as RowNumber
    from SplitCommissionsHold
),
CommDetailHold_CTE as (
    select
        CommissionedDetailFID,
        HoldUpdateStatus,
        row_number() over (partition by CommissionedDetailFID order by CommissionedDetailHoldID desc) as RowNumber
    from CommissionedDetailHold
)
insert into #Transactions
(
    VendorID,
    VendorName,
    VoucherNumber,
    DocumentNumber,
    DocumentDate,
    DocumentAmount,
    OrderNumber,
    ItemDescription,
    AcctTransactionID,
    AccountingVendorFid,
    ShipperName,
    InvoiceAmount,
    RevenueAmount,
    NetAmount,
    CommissionType,
    CommissionBasis,
    CommissionAmount,
    CommissionPerc,
    CommissionFactor,
    OrderWeight,
    AmountAfterSplits,
    AllocatedItemDescription,
    Miles
)
select
    VendorID = rtrim(Vendor.VendorID),
    VendorName = rtrim(Vendor.[Name]),
    VoucherNumber = AcctTransactions.VoucherNumber,
    DocumentNumber = AcctTransactions.DocumentNumber,
    DocumentDate = AcctTransactions.DocumentDate,
    DocumentAmount =
        case
            when AcctTransactions.TransactionType = 'A/P Credit Memo' then -AcctTransactions.TransactionAmount
            else AcctTransactions.TransactionAmount
        end,
    OrderNumber = AcctTransactions.OrderNo,
    ItemDescription = AcctTransactions.ItemDescription,
    AcctTransactionID = AcctTransactions.ATPriKey,
    AccountingVendorFid = Vendor.AccountingVendorId,
    -- Derived Mover's Suite Fields
    ShipperName = isnull(AcctTransactions.ShipperName, ''),
    InvoiceAmount =    
        case
            when BillingMinorItem.Percentage is not null then isnull(BillingMajorItem.Amount, 0)
            else isnull(BillingMinorItem.InvoiceAmount, 0)
        end,
    RevenueAmount =
        case
            when BillingMinorItem.Amount is null then isnull(BillingMinorDist.Amount, 0)
            else isnull(BillingMinorItem.Amount, 0)
        end,
    NetAmount =
        case
            when BillingMinorItem.Percentage is not null then isnull(BillingMinorItem.InvoiceAmount, 0)
            else isnull(BillingMinorItem.InvoiceAmount - BillingMinorItem.ReductionAmount, 0)
        end,
    CommissionType =
        case
            when CommissionedDetail.CDPriKey is null
            then isnull(SplitCommissions.CommissionType, '0')
            else isnull(CommissionedDetail.CommissionType, '')
        end,
    CommissionBasis = isnull(CommissionBasis.Basis, ''),
    CommissionAmount =
        case
            when CommissionedDetail.CDPriKey is null
            then isnull(SplitCommissions.Amount, 0)
            else isnull(CommissionedDetail.Amount, 0)
        end,
    CommissionPerc =
        case
            when CommissionedDetail.CDPriKey is null
            then isnull(SplitCommissions.CommissionPerc, 0)
            else isnull(CommissionedDetail.CommissionPerc, 0)
        end,
    CommissionFactor = isnull(CommissionedDetail.CommissionFactor, 0),
    OrderWeight = isnull(Orders.[Weight], 0),
    AmountAfterSplits =
        case
            when CommissionedDetail.CDPriKey is null
            then isnull(SplitCommissions.Amount, 0)
            else isnull(CommissionedDetail.CommissionAfterSplit, 0)
        end,
    AllocatedItemDescription =
        case
            when BillingMinorItem.Percentage is not null then BillingMajorItem.[Description]
            else null
        end,
    Miles =
        case
            when CommissionedDetail.CDPriKey is null
            then CommissionedDetail_Split.Miles
            else CommissionedDetail.Miles
        end
from AcctTransactions
inner join Vendor_Synonym as Vendor on AcctTransactions.VendorNumber = Vendor.VendorID
left outer join Orders on Orders.PriKey = AcctTransactions.OrdPriKey
left outer join SplitCommissions on
    AcctTransactions.SourceRecord = isnull(SplitCommissions.SCPriKey, 0) and
    AcctTransactions.Source = 'SplitCommissions'
left outer join CommissionedDetail CommissionedDetail_Split on CommissionedDetail_Split.CDPriKey = SplitCommissions.CDPriKey
left outer join CommissionedDetail on
    AcctTransactions.SourceRecord = isnull(CommissionedDetail.CDPriKey, 0) and
    AcctTransactions.Source = 'CommissionedDetail'
left outer join BillingMinorItem on
    CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey or
    CommissionedDetail_Split.BMinPriKey = BillingMinorItem.BMinPriKey
left outer join BillingMajorItem on BillingMajorItem.BMajPriKey = BillingMinorItem.BMajPriKey
left outer join RevGroups on RevGroups.RGPriKey = BillingMajorItem.RevGroupPriKey
left outer join BillingMinorDist on
    CommissionedDetail.BMinDistPrikey = BillingMinorDist.BMinDistPrikey or
    CommissionedDetail_Split.BMinDistPrikey = BillingMinorDist.BMinDistPrikey
left outer join CommissionBasis on CommissionBasis.CBPriKey = CommissionedDetail.CBPriKey
left outer join CommStmtDetail on CommStmtDetail.VoucherNumber = AcctTransactions.VoucherNumber
left outer join SplitHold_CTE as SplitCommissionsOnHold on SplitCommissions.SCPriKey is not null and SplitCommissionsOnHold.SplitCommissionsFID = SplitCommissions.SCPriKey and SplitCommissionsOnHold.RowNumber = 1
left outer join CommDetailHold_CTE as CommissionsOnHold on CommissionedDetail.CDPriKey is not null and CommissionsOnHold.CommissionedDetailFID = CommissionedDetail.CDPriKey and CommissionsOnHold.RowNumber = 1
where
    AcctTransactions.TransactionType LIKE 'A/P%' and
    Vendor.VendorClass = @VendorClass and
    AcctTransactions.DocumentDate <= @DueDate and
    CommStmtDetail.VoucherNumber is null and
    -- Determine if the transaction is on hold:
    (
        -- Case 1: SplitCommissions
        (AcctTransactions.Source = 'SplitCommissions' and isnull(SplitCommissionsOnHold.HoldUpdateStatus, 0) = 0)
        or
        -- Case 2: CommissionedDetail
        (AcctTransactions.Source = 'CommissionedDetail' and isnull(CommissionsOnHold.HoldUpdateStatus, 0) = 0)
        or
        -- Case 3: Other A/P (advances, third party payments)
        (AcctTransactions.Source not in ('SplitCommissions', 'CommissionedDetail') and AcctTransactions.OnHoldFlag = 0)
    );
        
-- Get the balance forward information. 
set @theLastStmtDate = ( select top 1 ToDate from CommStmtHeader where VendorClassId = @VendorClass and Final = @TRUE order by MSID desc )

-- Ensure the late statement date is defaulted if it is null.
set @theLastStmtDate = isnull( @theLastStmtDate, '1/1/1900' )

if( @theLastStmtDate is not null )
begin
    insert into #Transactions
    (
        VendorID,
        VendorName,
        VoucherNumber,
        DocumentNumber,
        DocumentDate,
        DocumentAmount,
        OrderNumber,
        ItemDescription,
        AcctTransactionID,
        AccountingVendorFid
    )
    select
        VendorID = rtrim( Vendor.VendorID ),
        VendorName = rtrim( Vendor.[Name] ),
        VoucherNumber = '',
        DocumentNumber = '',
        DocumentDate = @theLastStmtDate,
        DocumentAmount = CommStmtVendor.Balance,
        OrderNumber = '',
        ItemDescription = 'Balance Forward',
        AcctTransactionID = null,
        AccountingVendorFid = Vendor.AccountingVendorId
    from Vendor_Synonym as Vendor
    inner join CommStmtVendor_Synonym as CommStmtVendor on Vendor.VendorJoinField = CommStmtVendor.VendorJoinField
    where 
        Vendor.VendorClass = @VendorClass and
        CommStmtVendor.MSID =
        (
            select max( CommStmtVendor_max.MSID )
            from CommStmtVendor as CommStmtVendor_max
            where CommStmtVendor_max.VendorID = CommStmtVendor.VendorID
        ) and
        isnull( CommStmtVendor.Balance, 0 ) != 0
end

-- Create an index on the temp table for update statement.
create index IX_AcctTransactions on #Transactions( AcctTransactionID )
    
-- Update amounts for splits
update #Transactions
set
    InvoiceAmount =    convert( decimal(10,2), isnull( BillingMajorItem.Amount, 0 ) * ( isnull( RevenueRatingImportedSplitHaul.Percentage, 100 ) / 100 ) ),
    NetAmount =  convert( decimal(10,2), isnull( BillingMinorItem.InvoiceAmount, 0 ) * ( isnull( RevenueRatingImportedSplitHaul.Percentage, 100 ) / 100 ) )
from #Transactions GP
inner join AcctTransactions on AcctTransactions.ATPriKey = GP.AcctTransactionID
left outer join CommissionedDetail on
    AcctTransactions.SourceRecord = isnull( CommissionedDetail.CDPriKey, 0 ) and
    AcctTransactions.Source = 'CommissionedDetail'
left outer join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
left outer join BillingMajorItem on BillingMajorItem.BMajPriKey = BillingMinorItem.BMajPriKey
left outer join CommissionBasis on CommissionBasis.CBPriKey = CommissionedDetail.CBPriKey
inner join RevenueRatingImportedSplitHaul on BillingMinorItem.RevenueRatingImportedSplitHaulFID = RevenueRatingImportedSplitHaul.RevenueRatingImportedSplitHaulID
where 
    CommissionBasis.Basis in ( 'Invoice Amount', 'Net Amount' ) and
    CommissionedDetail.CommissionType = 'P'

-- If there are records in #Transactions, insert them into Mover's Suite.
if exists ( select 1 from #Transactions where VendorID != '' )
begin
            
    -- Get the vendor class name and insert a record into CommStmtHeader.
    declare @theVendorClassName varchar(31)
    declare @theAccountingVendorClassId int
    select
        @theVendorClassName = VendorClass.[Description],
        @theAccountingVendorClassId = VendorClass.AccountingVendorClassId
    from VendorClass_Synonym as VendorClass
    where VendorClass.Code = @VendorClass
            
    insert into CommStmtHeader( VendorClassID, VendorClass, DateCreated, ToDate, Final, CreatedBy, AccountingVendorClassFid )
    select distinct
        VendorClassID     = @VendorClass,
        VendorClass     = @theVendorClassName,
        DateCreated        = convert( datetime, convert( varchar, getdate(), 101 ), 101 ),
        ToDate             = convert( datetime, convert( varchar, @DueDate, 101 ), 101 ),
        Final             = 0,
        CreatedBy        = @CreatedBy,
        AccountingVendorClassFid = @theAccountingVendorClassId
            
    set @CSHPriKey = scope_identity()
            
    -- Insert the vendor information.
    -- All records that are due before(or on) the DueDate, and after(or on) the FromDate are included.
    -- Also, any records that were created after the FromDate, and are Due previous to the FromDate.
    insert into CommStmtVendor( CommStmtHeader, VendorID, VendorName, Balance, AccountingVendorFid )
    select distinct
        CommStmtHeader     = @CSHPriKey,
        VendorID         = VendorID,
        VendorName         = VendorName,
        Balance         = 0,
        AccountingVendorFid = AccountingVendorFid
    from #Transactions
            
    insert into CommStmtDetail
    (
        VoucherNumber,
        DocumentNumber,
        DocumentDate,
        DocumentAmount,
        OrderNumber,
        ShipperName,
        InvoiceAmount,
        RevenueAmount,
        CommissionType,
        CommissionBasis,
        CommissionAmount,
        CommissionPerc,
        CommissionFactor,
        OrderWeight,
        AmountAfterSplits,
        ItemDescription,
        AllocatedItemDescription,
        CommStmtVendor,
        NetAmount,
        Miles
    )
    select
        VoucherNumber     = Transactions.VoucherNumber,
        DocumentNumber    = Transactions.DocumentNumber,
        DocumentDate      = Transactions.DocumentDate,
        DocumentAmount    = Transactions.DocumentAmount,
        OrderNumber       = Transactions.orderNumber,
        ShipperName       = Transactions.ShipperName,
        InvoiceAmount     = Transactions.InvoiceAmount,
        RevenueAmount     = Transactions.RevenueAmount,
        CommissionType    = Transactions.CommissionType,
        CommissionBasis   = Transactions.CommissionBasis,
        CommissionAmount  = Transactions.CommissionAmount,
        CommissionPerc    = Transactions.CommissionPerc,
        CommissionFactor      = Transactions.CommissionFactor,
        OrderWeight       = Transactions.orderWeight,
        AmountAfterSplits = Transactions.AmountAfterSplits,
        ItemDescription   = Transactions.ItemDescription,
        AllocatedItemDescription = Transactions.AllocatedItemDescription,
        CommStmtVendor      = CommStmtVendor.MSID,
        NetAmount          = Transactions.NetAmount,
        Miles              = Transactions.Miles
    from #Transactions Transactions
    inner join CommStmtVendor on ( CommStmtVendor.VendorID = Transactions.VendorID )
    where CommStmtVendor.CommStmtHeader = @CSHPriKey
            
    update CommStmtVendor
    set Balance = 
        isnull( ( select sum( csd.DocumentAmount )
                    from CommStmtDetail csd
                    where csd.CommStmtVendor = CommStmtVendor.MSID ), 0 )
    where CommStmtHeader = @CSHPriKey
end -- if exists ( select 1 from #Transactions where VendorID != '' )

-- cleanup our temp table
drop table #Transactions
GO
GRANT EXECUTE ON  [dbo].[spCSMBuildStatement_XLedger] TO [MssExec]
GO