/**
*    
*    This stored procedure is used to change the alternate bill to information on all BillingMinorItems
*    on a specific order.  The BillingMinorItems with the "old" customer info will be changed to have the
*    "new" customer into.  This can only be done if the BillingMinorItem is not yet "generated" (which would
*    allow changing the customer number and/or the address code) or if generated, then only the address code
*    can be changed.
*    
*    Error Codes:
*    @ERROR_CODE_INVALID_NEW_ALTERNATE_BILLING
*    
*    Input Parameters:
*    @param @inOrderID                    The primary key of the order.
*    @param @inOldCustomerNumber            The old customer number to be changed to the new customer number.
*    @param @inOldCustomerAddressCode    The old customer address code to be changed to the new customer address code.
*    @param @inNewCustomerNumber            The new customer number to be assigned to the old customer number.
*    @param @inNewCustomerAddressCode    The new customer address code to be assigned to the old customer address code.
*    @param @outErrorCode                Indicates errors that were detected during call.
*    @param @outRecordsModified            Indicates the number of BillingMinorItems records that were modified during call.
*/
CREATE PROCEDURE [dbo].[ChangeAlternateBillToOnBillingMinorItems]
    @inOrderID int,
    @inOldCustomerNumber varchar(15),
    @inOldCustomerAddressCode varchar(15),
    @inNewCustomerNumber varchar(15),
    @inNewCustomerAddressCode varchar(15),
    @outErrorCode int output,
    @outRecordsModified int output
as
set nocount on

-- Set the output parameters.
select
    @outErrorCode = 0,
    @outRecordsModified = 0

declare @theRowsUpdated int

declare @theNewCustomerInfoIsValid bit
declare @theNewAlternateCustomerNumber varchar(15)
declare @theNewAlternateCustomerAddressCode varchar(15)
declare @theNewAlternateAccountingCustomerFid int
declare @theNewAlternateAccountingCustomerAddressFid int
declare @ERROR_CODE_INVALID_NEW_ALTERNATE_BILLING int = 1252

declare @theBillingMinorCount int
declare @theCounter int
declare @theBMinPriKey int

declare @SourceBMinPriKey table
(
    TableID int identity( 1, 1 ) not null,
    BMinPriKey int
)

declare @BillingMinorItemDetails table
(
    TableID int identity( 1, 1 ) not null,
    BMajPriKey int,
    GroupDescription varchar(128),
    GroupAmount decimal(12,2),
    RevenueGroupID int,
    SplitGroup bit,
    AllowDistributionsFlag bit,
    PostedDists int,
    HasCommissions int,
    HasPostedThirdParties int,
    HasPostedCommissions int,
    AddBackBillingSourceID int,
    BranchFID int,
    DivisionFID int,
    ICPriKey int,
    Description varchar(128),
    InvoiceFlag varchar(1),
    LSPriKey int,
    ARBranchFID int,
    ARDivisionFID int,
    RateTypeFID int,
    Quantity float,
    Quantity2 float,
    Rate float,
    GrossAmount money,
    Discount float,
    InvoiceAmount money,
    Percentage float,
    AllocatedInvoiceAmount money,
    ReductionAmount decimal(12,2),
    ReducedInvoiceAmount decimal(12,2),
    AgentPercentage float,
    Amount money,
    PointOfServiceFID int,
    ExtraStopFID int,
    CustomerInvoiceReportEDIItemFID int,
    TaxCode varchar(25),
    LaborRatingTypeFID int,
    RateSource varchar(50),
    RateSourceRecord int,
    DocumentNumber varchar(21),
    ApplyToDoc int,
    IHPriKey int,
    RevenueRatingImportedSplitHaulFID int,
    OriginalAllocation decimal(12,4),
    SplitHaulPercentage decimal(12,4),
    BMinPriKey int
)

-- Verify that the new customer info is valid.  We don't use the join fields to find the source
-- records that currently have the old customer number and address codes on them.
select
    @theNewCustomerInfoIsValid = 1,
    @theNewAlternateCustomerNumber = CustomerAddressInfo.CustomerNumber,
    @theNewAlternateCustomerAddressCode = CustomerAddressInfo.AddressCode,
    @theNewAlternateAccountingCustomerFid = CustomerAddressInfo.CustomerFid,
    @theNewAlternateAccountingCustomerAddressFid = CustomerAddressInfo.CustomerAddressId
from dbo.GetCustomerAddressInformation_Synonym( @inNewCustomerNumber, @inNewCustomerAddressCode ) as CustomerAddressInfo

-- We don't need to check if the @inOrderID is valid or not because if it isn't valid, we won't update anything.
if( isnull( @theNewCustomerInfoIsValid, 0 ) != 1 )
begin
    set @outErrorCode = @ERROR_CODE_INVALID_NEW_ALTERNATE_BILLING
end
else
begin
    if( @inOldCustomerNumber != @inNewCustomerNumber )
    begin
        -- If the old and new customer numbers match, the posted state of the items will not
        -- matter.  We will use the RevenueGetBillingMinorItem sp as it is what the UI uses
        -- to determine if the revenue item's alternate bill to control can be changed.
        insert into @SourceBMinPriKey( BMinPriKey )
        select
            BillingMinorItem.BMinPriKey
        from BillingMajorItem
        inner join BillingMinorItem on BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
        where BillingMajorItem.OrdPriKey = @inOrderID and
            BillingMinorItem.AlternateCustomerNumber = @inOldCustomerNumber and
            BillingMinorItem.AlternateCustomerAddressCode = @inOldCustomerAddressCode

        set @theBillingMinorCount = @@ROWCOUNT
        set @theCounter = 1
        while( @theCounter <= @theBillingMinorCount )
        begin
            select
                @theBMinPriKey = BMinPriKey
            from @SourceBMinPriKey
            where TableID = @theCounter

            insert into @BillingMinorItemDetails
            (
                BMajPriKey,
                GroupDescription,
                GroupAmount,
                RevenueGroupID,
                SplitGroup ,
                AllowDistributionsFlag,
                PostedDists,
                HasCommissions,
                HasPostedThirdParties,
                HasPostedCommissions,
                AddBackBillingSourceID,
                BranchFID,
                DivisionFID,
                ICPriKey,
                Description,
                InvoiceFlag,
                LSPriKey,
                ARBranchFID,
                ARDivisionFID,
                RateTypeFID,
                Quantity,
                Quantity2,
                Rate,
                GrossAmount,
                Discount,
                InvoiceAmount,
                Percentage,
                AllocatedInvoiceAmount,
                ReductionAmount,
                ReducedInvoiceAmount,
                AgentPercentage,
                Amount,
                PointOfServiceFID,
                ExtraStopFID,
                CustomerInvoiceReportEDIItemFID,
                TaxCode,
                LaborRatingTypeFID,
                RateSource,
                RateSourceRecord,
                DocumentNumber,
                ApplyToDoc,
                IHPriKey,
                RevenueRatingImportedSplitHaulFID,
                OriginalAllocation,
                SplitHaulPercentage,
                BMinPriKey
            )
            -- This should always only return exactly one revenue BillingMinorItem row as long as
            -- @theBMinPriKey is valid.
            exec RevenueGetBillingMinorItem
                @inBMinPriKey = @theBMinPriKey

            set @theCounter = @theCounter + 1
        end
    end

    -- Only update BillingMinorItem on our order that are unposted (have no document number) or if the CustomerJoinField is not changing.
    update BillingMinorItem set
        AlternateCustomerNumber = @theNewAlternateCustomerNumber,
        AlternateCustomerAddressCode = @theNewAlternateCustomerAddressCode,
        AlternateAccountingCustomerFid = @theNewAlternateAccountingCustomerFid,
        AlternateAccountingCustomerAddressFid = @theNewAlternateAccountingCustomerAddressFid
    from BillingMajorItem
    inner join BillingMinorItem on BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
    inner join BillingMinorItemAlternateCustomer_Synonym as BillingMinorItemAlternateCustomer on BillingMinorItemAlternateCustomer.BMinPriKey = BillingMinorItem.BMinPriKey
    cross join dbo.GetCustomerAddressJoinFields_Synonym( @theNewAlternateAccountingCustomerFid, @theNewAlternateAccountingCustomerAddressFid, @theNewAlternateCustomerNumber, @theNewAlternateCustomerAddressCode ) as NewCustomerAddressJoinFields
    left outer join @BillingMinorItemDetails as BillingMinorItemDetails on BillingMinorItemDetails.BMinPriKey = BillingMinorItem.BMinPriKey
    where BillingMajorItem.OrdPriKey = @inOrderID and
        BillingMinorItem.AlternateCustomerNumber = @inOldCustomerNumber and
        BillingMinorItem.AlternateCustomerAddressCode = @inOldCustomerAddressCode and
        (
            (
                isnull( BillingMinorItem.DocumentNumber, '' ) = '' and
                isnull( BillingMinorItemDetails.PostedDists, 0 ) != 1 and
                isnull( BillingMinorItem.ApplyToDoc, 0 ) = 0 and
                isnull( BillingMinorItem.IHPriKey, 0 ) = 0 and
                isnull( BillingMinorItemDetails.HasPostedCommissions, 0 ) = 0 and
                isnull( BillingMinorItemDetails.HasPostedThirdParties, 0 ) = 0
            ) or
            BillingMinorItemAlternateCustomer.CustomerJoinField = NewCustomerAddressJoinFields.CustomerJoinField
        )

    set @outRecordsModified = @@ROWCOUNT
end
GO
GRANT EXECUTE ON  [dbo].[ChangeAlternateBillToOnBillingMinorItems] TO [MssExec]
GO