/**
* Formerly, this was all embedded inside a TADOQuery object in MoversSuite.  This is pretty heavy T-SQL
* to be an adhoc query in a c++ .dfm file.
*/
CREATE PROCEDURE [dbo].[AddSalesTaxRevenueItems]
    @inInvoicedHeaderID int,
    @inOrderID int,
    @inAlternateCustomerNumber varchar(15),
    @inAlternateCustomerAddressCode varchar(15),
    @inAlternateAccountingCustomerFid int,
    @inAlternateAccountingCustomerAddressFid int,
    @inSalesTaxAmount money
as

set nocount on

declare @outErrorCode int
set @outErrorCode = 0

declare @theCustomerInvoiceReportID int
declare @theBillingMajorID int
declare @theBillingMinorID int
declare @theSalesTaxItemCodeID int
declare @theRevenueGroupID int

declare @thePointOfServiceID int
declare @theEDIRevenueGroupItemID int
declare @theEDIRevenueItemID int
declare @theEDIItemDescription varchar(128)

-- ItemCode fields
declare @theDescription varchar( 128 )

declare @theOrderAuthorityID int
declare @theInterCompanyFlag bit
declare @theOrderBranchID int
declare @theOrderDivisionID int
declare @theOrderShipmentType int

declare @theAlternateBillToCustomerNumber varchar(15)
declare @theAlternateBillToCustomerAddressCode varchar(15)
declare @theAlternateBillToAccountingCustomerFid int
declare @theAlternateBillToAccountingCustomerAddressFid int

select
    @theOrderAuthorityID = Orders.AuthPriKey,
    @theOrderBranchID = Orders.BranchPriKey,
    @theOrderDivisionID = Orders.DivisionFID,
    @theOrderShipmentType = Orders.ShipmentTypeFID,

    -- Get the Alternate Bill To info from the order if the Alternate Bill To is not provided by parameters
    @theAlternateBillToCustomerNumber = case
        when InboundCustomerAddressJoinFields.CustomerJoinField is not null then @inAlternateCustomerNumber
        when OrdersCustomer.CustomerJoinField is not null then Orders.CustomerNumber
        else null
    end,
    @theAlternateBillToCustomerAddressCode = case
        when InboundCustomerAddressJoinFields.CustomerJoinField is not null then isnull( @inAlternateCustomerAddressCode, '' )
        when OrdersCustomer.CustomerJoinField is not null then isnull( Orders.CustomerAddressCode, '' )
        else null
    end,
    @theAlternateBillToAccountingCustomerFid = case
        when InboundCustomerAddressJoinFields.CustomerJoinField is not null then @inAlternateAccountingCustomerFid
        when OrdersCustomer.CustomerJoinField is not null then Orders.AccountingCustomerFid
        else null
    end,
    @theAlternateBillToAccountingCustomerAddressFid = case
        when InboundCustomerAddressJoinFields.CustomerJoinField is not null then @inAlternateAccountingCustomerAddressFid
        when OrdersCustomer.CustomerJoinField is not null then Orders.AccountingCustomerAddressFid
        else null
    end
from Orders
inner join OrdersCustomer_Synonym as OrdersCustomer on OrdersCustomer.PriKey = Orders.PriKey
cross join dbo.GetCustomerAddressJoinFields_Synonym( @inAlternateAccountingCustomerFid, @inAlternateAccountingCustomerAddressFid,
    @inAlternateCustomerNumber, @inAlternateCustomerAddressCode ) as InboundCustomerAddressJoinFields
where Orders.PriKey = @inOrderID

-- See if this customer is an EDI customer.
set @theCustomerInvoiceReportID =
(
    select
        CustomerInvoiceReportCustomer.CustomerInvoiceReportID
    from dbo.GetCustomerJoinField_Synonym( @theAlternateBillToAccountingCustomerFid, @theAlternateBillToCustomerNumber ) as AlternateBillToCustomerJoinField
    inner join CustomerInvoiceReportCustomer_Synonym as CustomerInvoiceReportCustomer on CustomerInvoiceReportCustomer.CustomerJoinField = AlternateBillToCustomerJoinField.CustomerJoinField
)

select
    @theSalesTaxItemCodeID = SalesTaxSetup.SalesTaxItemCodeFID,
    @theRevenueGroupID = ItemCode.RGPriKey,
    @theDescription = ItemCode.[Description]
from SalesTaxSetup
left outer join ItemCode on SalesTaxSetup.SalesTaxItemCodeFID = ItemCode.ICPriKey

if( @theRevenueGroupID is null )
begin
    set @outErrorCode = 1
end
else
begin
    -- Get the Billing Major Prikey for Revenue Group
    exec RevenueGroupExistsForOrder
        @inOrderID,
        @theRevenueGroupID,
        @theBillingMajorID output

    if( 0 >= isnull( @theBillingMajorID, 0 ) )
    begin
        -- If this is an EDI customer, then get the EDI Item for the revenue group, if there is one.
        if( @theCustomerInvoiceReportID is not null )
        begin
            set @theEDIRevenueGroupItemID =
            (
                select top 1 CustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID
                from CustomerInvoiceReportEDIItem
                where CustomerInvoiceReportEDIItem.CustomerInvoiceReportFID = @theCustomerInvoiceReportID and
                    CustomerInvoiceReportEDIItem.RevenueGroupFID = @theRevenueGroupID
                order by CustomerInvoiceReportEDIItem.[Description]
            )
        end

        -- Insert a Billing Major Item if we do not have one
        insert into BillingMajorItem
        (
            OrdPriKey,
            RevGroupPriKey,
            [Description],
            InvoiceFlag,
            CustomerInvoiceReportEDIItemFID
        )
        select
            OrdPriKey = @inOrderID,
            RevGroupPriKey = RevGroups.RGPriKey,
            [Description] = RevGroups.[Description],
            InvoiceFlag = RevGroups.InvoiceFlag,
            CustomerInvoiceReportEDIItemFID = @theEDIRevenueGroupItemID
        from RevGroups
        where RevGroups.RGPriKey = @theRevenueGroupID

        set @theBillingMajorID = scope_identity()
    end

    -- If this is an EDI customer, then once we know the @theICPriKey, we can find the best matching EDI item code for our BillingMinorItem.
    if( @theCustomerInvoiceReportID is not null )
    begin
        select top 1
            @theEDIRevenueItemID = CustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID,
            @theEDIItemDescription = CustomerInvoiceReportEDIItem.[Description]
        from CustomerInvoiceReportEDIItem
        where CustomerInvoiceReportEDIItem.CustomerInvoiceReportFID = @theCustomerInvoiceReportID and
        (
            CustomerInvoiceReportEDIItem.ItemCodeFID = @theSalesTaxItemCodeID
        )
        order by
            case
                when isnull( @theSalesTaxItemCodeID, -1 ) = isnull( CustomerInvoiceReportEDIItem.ItemCodeFID, -2 ) then 0
                else 8
            end +
            case
                when 'Sales Tax' = CustomerInvoiceReportEDIItem.[Description] then 0
                else 1
            end,
            CustomerInvoiceReportEDIItem.[Description],
            CustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID

        -- If the point of service was not provided in the download, see if we can determine it from the EDI Item codes.
        if( @theEDIRevenueItemID is not null )
        begin
            -- See if there is just one EDI record with the EDI item code that is on the EDI record we selected.
            if( 1 =
            (
                select count(*) from CustomerInvoiceReportEDIItem
                inner join CustomerInvoiceReportEDIItem as EDIItem on
                    EDIItem.CustomerInvoiceReportFID = CustomerInvoiceReportEDIItem.CustomerInvoiceReportFID and
                    EDIItem.EDIItemCode = CustomerInvoiceReportEDIItem.EDIItemCode
                where CustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID = @theEDIRevenueItemID
            ) )
            begin
                -- If so, then use the point of service that is on that EDI Item record.
                set @thePointOfServiceID =
                (
                    select PointOfServiceFID from CustomerInvoiceReportEDIItem
                    where CustomerInvoiceReportEDIItemID = @theEDIRevenueItemID
                )
            end
        end
    end

    -- Insert the sales tax billing minor item
    insert into BillingMinorItem
    (
        BMajPriKey,
        ICPriKey,
        InvoiceFlag,
        [Description],
        Amount,
        InvoiceAmount,
        Percentage,
        Quantity,
        DivisionFID,
        Rate,
        BranchFID,
        AgentPercentage,
        GrossAmount,
        Discount,
        ReductionAmount,
        ReducedInvoiceAmount,
        AllocatedInvoiceAmount,
        ARBranchFID,
        ARDivisionFID,
        AlternateCustomerNumber,
        AlternateCustomerAddressCode,
        AlternateAccountingCustomerFid,
        AlternateAccountingCustomerAddressFid,
        Quantity2,
        RateTypeFID,
        PointOfServiceFID,
        CustomerInvoiceReportEDIItemFID,
        IHPriKey,
        SalesTaxItem
    )
    select
        BMajPriKey = @theBillingMajorID,
        ICPriKey = @theSalesTaxItemCodeID,
        InvoiceFlag = 'N',
        [Description] =
            case
                when @theEDIRevenueItemID is not null then isnull( @theEDIItemDescription, @theDescription )
                else @theDescription
            end,
        Amount = convert( decimal( 19, 2 ), isnull( @inSalesTaxAmount, 0 ) ),
        InvoiceAmount = convert( decimal( 19, 2 ), isnull( @inSalesTaxAmount, 0 ) ),
        Percentage = null,
        Quantity = null,
        DivisionFID = dbo.GetRevenueItemDivision( Branch.AgentPriKey, @inOrderID, @theSalesTaxItemCodeID ),
        Rate = null,
        BranchFID = @theOrderBranchID,
        AgentPercentage = 100,
        GrossAmount = null,
        Discount = null,
        ReductionAmount = 0,
        ReducedInvoiceAmount = convert( decimal( 19, 2 ), @inSalesTaxAmount, 2 ),
        AllocatedInvoiceAmount = null,
        ARBranchFID = @theOrderBranchID,
        ARDivisionFID = case @theInterCompanyFlag
            when 1 then isnull( @theOrderDivisionID, dbo.GetDefaultDivisionFromBranch( @theOrderBranchID ) )
            else dbo.GetRevenueItemDivision( Branch.AgentPriKey, @inOrderID, @theSalesTaxItemCodeID )
        end,
        AlternateCustomerNumber = @theAlternateBillToCustomerNumber,
        AlternateCustomerAddressCode = @theAlternateBillToCustomerAddressCode,
        AlternateAccountingCustomerFid = @theAlternateBillToAccountingCustomerFid,
        AlternateAccountingCustomerAddressFid = @theAlternateBillToAccountingCustomerAddressFid,
        Quantity2 = null,
        RateTypeFID = null,
        PointOfServiceFID = isnull( @thePointOfServiceID, PointOfService.PointOfServiceID ),
        CustomerInvoiceReportEDIItemFID = @theEDIRevenueItemID,
        IHPriKey = @inInvoicedHeaderID,
        SalesTaxItem = 1
    from Branch
    cross join PointOfService
    where Branch.BranchPriKey = @theOrderBranchID and
        PointOfService.[Description] = 'NA'

end
select ErrorCode = @outErrorCode
GO
GRANT EXECUTE ON  [dbo].[AddSalesTaxRevenueItems] TO [MssExec]
GO