/**
*    Description: This is for Sirva Driver Compensation import. Adds a RevenueRatingImportHeader record based on data
*  sent to us on a download.
*
**/
create procedure [dbo].[RevenueRatingImportHeaderInsert]
    @inShipmentNbr varchar(30),
    @inBrand varchar(32),
    @inTransactionDateTime datetime,
    @inBilledAsWeight int,
    @inBilledAsMileage int,
    @inDebit money,
    @inCredit money,
    @inNetCompensation money,
    @outRevenueRatingHeaderID int output,
    @outBranchAgentID int output
as
begin
    
    set nocount on

    declare @theRevenueImportTypeID int
    declare @theXmlInterfaceID int
    declare @theVanlineID int
    declare @theXmlExternalVanlineMapID int

    select
        @theRevenueImportTypeID = ( select PriKey from RevenueImportType where XmlSystemDataTypeMapName = 'Sirva Rated Compensation' ),
        @theXmlInterfaceID = XmlExternalInterfaceVanlineMap.XmlInterfaceFID,
        @theXmlExternalVanlineMapID = XmlExternalInterfaceVanlineMap.XmlExternalInterfaceVanlineMapID,
        @theVanlineID = XmlExternalInterfaceVanlineMap.VanlineFID
    from XmlExternalInterfaceVanlineMap
    where
        XmlExternalInterfaceVanlineMap.ExternalCode = @inBrand

    declare @theOrderSeg varchar(16)
    declare @thePSeg varchar(1)
    declare @theOSeg varchar(1)
    declare @theSSeg varchar(1)
    
    -- We only get a six character ShipmentNbr (Order Number) from Sirva.
    -- This is the best we can do to get the latest matching order. Some
    -- order numbers in Movers Suite are 6 characters and some are larger.
    -- We will use our OrderSeg for matching in Revenue Import.
    select top 1
        @theOrderSeg = Orders.OrderSeg,
        @thePSeg = Orders.PSeg,
        @theOSeg = Orders.OSeg,
        @theSSeg = Orders.SSeg,
        @outBranchAgentID = Branch.AgentPriKey
    from Orders
    inner join Branch on Branch.BranchPriKey = Orders.BranchPriKey
    where
        substring( Orders.OrderSeg, 1, 6 ) = @inShipmentNbr and
        VanLineFID = @theVanlineID

    -- if we don't have a RevenueImportType or an OrderSeg then skip inserting RevenueRatingImport data.
    if ( @theRevenueImportTypeID is not null and @theOrderSeg != '' )
    begin

        insert into RevenueRatingImportHeader
        (
            BookingAgentPercentage,
            DestinationAgentPercentage,
            DiscountLineHaul,
            DiscountPercentage,
            DownloadDateTime,
            HaulingAgentPercentage,
            OrderSeg,
            PSeg,
            OSeg,
            SSeg,
            OriginAgentPercentage,
            RevenueImportTypeFID,
            XMLInterfaceFID,
            XmlExternalInterfaceVanlineCode,
            GrossTransportationAmount,
            LineHaul,
            TotalWeight,
            IsOwnAuthorityFlag
        )
        select
            BookingAgentPercentage = null,
            DestinationAgentPercentage = null,
            DiscountLineHaul = null,
            DiscountPercentage = null,
            DownloadDateTime = @inTransactionDateTime,
            HaulingAgentPercentage = null,
            OrderSeg = @theOrderSeg,
            PSeg = @thePSeg,
            OSeg = @theOSeg,
            SSeg = @theSSeg,
            OriginAgentPercentage = null,
            RevenueImportTypeFID = @theRevenueImportTypeID,
            XMLInterfaceFID = @theXmlInterfaceID,
            XmlExternalInterfaceVanlineCode = @inBrand,
            GrossTransportationAmount = @inNetCompensation,
            LineHaul = case
                when @inCredit = 0 then null
                else @inCredit
            end,
            TotalWeight = case
                when @inBilledAsWeight = 0 then null
                else @inBilledAsWeight
            end,
            IsOwnAuthorityFlag = 0

        set @outRevenueRatingHeaderID = scope_identity()
    end

end
GO
GRANT EXECUTE ON  [dbo].[RevenueRatingImportHeaderInsert] TO [MssExec]
GO