/*
*    Description: This is for Sirva only. Moves all the data associated with a specific VanlineStatementStageHeader
*    row into the corresponding Revenue Rating Import Tables:
*        VanlineStatementStagetDetail --> RevenueRatingImportHeader
*        VanlineStatementStageDetailDist --> RevenueRatingImportDetail
*
*/
CREATE procedure [dbo].[VanlineStatementMoveToRatingImport]
    @inVanlineStatementStageHeaderID int,
    @inAgentPriKey int
as
begin
    set nocount on;
    declare @theXmlInterfaceID int;
    declare @theXmlSystemID int;

    -- By default, we'll use the "new" Sirva abstract service codes.
    -- But, users can opt in and use the old legacy service codes if they
    -- have not yet mapped their item codes to the "new" Sirva codes.
    declare @theUseLegacyServiceCodes int = 0;

    -- We'll use up to three different methods to locate the XmlInterfaceID.
    -- The first method is to use the RevenueImportType table:
    select
        @theXmlInterfaceID = XmlSystem.XmlInterfaceFID,
        @theXmlSystemID = XmlSystem.XmlSystemID
    from VanlineStatementStageHeader
    inner join VanlineGroup on VanlineGroup.VanlineExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode
    inner join RevenueImportType on RevenueImportType.VanlineGroupExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode and
        RevenueImportType.Name = 'Sirva Abstract'
    inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.[Name] = RevenueImportType.XmlSystemDataTypeMapName
    inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
    where VanlineStatementStageHeader.VanlineStatementStageHeaderID = @inVanlineStatementStageHeaderID

    if( @theXmlInterfaceID is null )
    begin
        -- The second method is to use the VanlineGroup's XmlSystemDataTypeMapFID:
        select
            @theXmlInterfaceID = XmlSystem.XmlInterfaceFID,
            @theXmlSystemID = XmlSystem.XmlSystemID
        from VanlineStatementStageHeader
        inner join VanlineGroup on VanlineGroup.VanlineExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemDataTypeMapID = VanlineGroup.XmlSystemDataTypeMapFID
        inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
        where VanlineStatementStageHeader.VanlineStatementStageHeaderID = @inVanlineStatementStageHeaderID
    end

    if( @theXmlInterfaceID is null )
    begin
        -- The third (and last ditch) method is to use a well-known XmlSystemDataTypeMap name for the van line group:
        select
            @theXmlInterfaceID = XmlSystem.XmlInterfaceFID,
            @theXmlSystemID = XmlSystem.XmlSystemID
        from VanlineStatementStageHeader
        inner join VanlineGroup on VanlineGroup.VanlineExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.[Name] = case
            when VanlineGroup.VanlineExternalCode = 'SIRVA' then 'Sirva Order Information'
            else 'Unknown Rating'
        end
        inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
        where VanlineStatementStageHeader.VanlineStatementStageHeaderID = @inVanlineStatementStageHeaderID
    end

    -- See if the user is opting in to use the "old" Sirva service codes.
    if( @theXmlSystemID is not null )
    begin
        if( exists (
            select top (1) 1
            from XmlSystemOptions
            where XmlSystemOptions.XmlSystemFID = @theXmlSystemID and
                XmlSystemOptions.[Name] = 'UseOldServiceCodes' and
                dbo.GetBooleanVarChar( XmlSystemOptions.[Value] ) = 'true' ) )
        begin
            -- User wants to use the old legacy service codes ( 0, 1, 4-5, 8-3, 8-7, etc.).
            set @theUseLegacyServiceCodes = 1
        end
    end

    -- Insert all VanlineStatementStageDetail records attached to a VanlineStatementStageHeader. The
    -- VanlineStatemenStagetDetail records become RevenueRatingImportHeader records.  OrderControl
    -- can have duplicate records. We will only insert into the header the unique Orders and attach
    -- the VanlineStatementStageDetailDist Orders to the one header.
    insert into RevenueRatingImportHeader
    (
        BookingAgentPercentage,
        DestinationAgentPercentage,
        DiscountLineHaul,
        DiscountPercentage,
        DownloadDateTime,
        HaulingAgentPercentage,
        LineHaul,
        OrderSeg,
        PSeg,
        OSeg,
        SSeg,
        OriginAgentPercentage,
        RevenueImportTypeFID,
        XMLInterfaceFID,
        XmlExternalInterfaceVanlineCode,
        GrossTransportationAmount,
        TotalWeight,
        IsOwnAuthorityFlag
    )
    select distinct
        BookingAgentPercentage = null,
        DestinationAgentPercentage = null,
        DiscountLineHaul = null,
        DiscountPercentage = null,
        DownloadDateTime = VanlineStatementStageHeader.StatementDate,
        HaulingAgentPercentage = null,
        LineHaul = null,
        OrderSeg = VanlineStatementStageDetail.OrderControl,
        PSeg = '',
        OSeg = '',
        SSeg = '',
        OriginAgentPercentage = null,
        RevenueImportTypeFID = RevenueImportType.PriKey,
        XMLInterfaceFID = XmlExternalInterfaceVanlineMap.XmlInterfaceFID,
        XmlExternalInterfaceVanlineCode = XmlExternalInterfaceVanlineMap.ExternalCode,
        GrossTransportationAmount = null,
        TotalWeight = VanlineStatementStageDetail.Weight,
        IsOwnAuthorityFlag = 0
    from VanlineStatementStageHeader
    inner join VanlineStatementStageDetail on VanlineStatementStageDetail.VanlineStatementStageHeaderFID = VanlineStatementStageHeader.VanlineStatementStageHeaderID
    inner join RevenueImportType on RevenueImportType.VanlineGroupExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode and
        RevenueImportType.Name = 'Sirva Abstract'
    inner join XmlExternalInterfaceVanlineMap on XmlExternalInterfaceVanlineMap.XmlInterfaceFID = @theXmlInterfaceID and
        XmlExternalInterfaceVanlineMap.ExternalCode = VanlineStatementStageDetail.BrandCode
    where VanlineStatementStageDetail.VanlineStatementStageHeaderFID = @inVanlineStatementStageHeaderID and
        isnull( VanlineStatementStageDetail.OrderControl, '' ) != '' and
        not exists
        (
            select top (1) 1
            from RevenueRatingImportHeader as ExistingRevenueRatingImportHeader
            where ExistingRevenueRatingImportHeader.RevenueImportTypeFID = RevenueImportType.PriKey and
                ExistingRevenueRatingImportHeader.XmlExternalInterfaceVanlineCode = XmlExternalInterfaceVanlineMap.ExternalCode and
                ExistingRevenueRatingImportHeader.OrderSeg = VanlineStatementStageDetail.OrderControl and
                ExistingRevenueRatingImportHeader.PSeg = '' and
                ExistingRevenueRatingImportHeader.OSeg = '' and
                ExistingRevenueRatingImportHeader.SSeg = ''
        )

    -- Insert all VanlineStatementStageDetailDist records attached to a VanlineStatementStageDetail. The
    -- VanlineStatementStageDetailDist records become RevenueRatingImportDetail records.
    insert into RevenueRatingImportDetail
    (
        AgentFID,
        AgentPercentage,
        CartonCode,
        Description,
        Discount,
        DistributionAmount,
        DistributionCode,
        GrossAmount,
        InvoiceAmount,
        Quantity,
        Rate,
        RevenueRatingImportHeaderFID,
        RevenueType,
        InvoiceFlag,
        AddBackFlag,
        PointOfServiceFID,
        RateTypeFID,
        Quantity2,
        MilitaryItemCode,
        MilitaryItemCodeQualifier,
        VanlineWaived
    )
    select
        AgentFID = @inAgentPriKey,
        AgentPercentage = null,
        CartonCode = null,
        Description = VanlineStatementStageDetailDist.ServiceDescription,
        Discount = null,
        DistributionAmount = null,
        DistributionCode = case
            when @theUseLegacyServiceCodes = 1 then VanlineStatementStageDetailDist.ServiceCode
            else VanlineStatementStageDetailDist.ServiceCodeExternal
        end,
        GrossAmount = null,
        InvoiceAmount = VanlineStatementStageDetailDist.Amount,
        Quantity = null,
        Rate = null,
        RevenueRatingImportHeader.RevenueRatingImportHeaderID,
        RevenueType = null,
        InvoiceFlag = 0,
        AddBackFlag = 0,
        PointOfServiceFID = null,
        RateTypeFID = null,
        Quantity2 = null,
        MilitaryItemCode = null,
        MilitaryItemCodeQualifier = null,
        VanlineWaived = 0
    from VanlineStatementStageDetailDist
    inner join VanlineStatementStageDetail on VanlineStatementStageDetail.VanlineStatementStageDetailID = VanlineStatementStageDetailDist.VanlineStatementStageDetailFID
    inner join VanlineStatementStageHeader on VanlineStatementStageHeader.VanlineStatementStageHeaderID = VanlineStatementStageDetail.VanlineStatementStageHeaderFID
    inner join RevenueImportType on RevenueImportType.VanlineGroupExternalCode = VanlineStatementStageHeader.VanlineGroupExternalCode and
        RevenueImportType.Name = 'Sirva Abstract'
    inner join XmlExternalInterfaceVanlineMap on XmlExternalInterfaceVanlineMap.XmlInterfaceFID = @theXmlInterfaceID and
        XmlExternalInterfaceVanlineMap.ExternalCode = VanlineStatementStageDetail.BrandCode
    inner join RevenueRatingImportHeader on RevenueRatingImportHeader.RevenueImportTypeFID = RevenueImportType.PriKey and
        RevenueRatingImportHeader.XmlExternalInterfaceVanlineCode = XmlExternalInterfaceVanlineMap.ExternalCode and
        RevenueRatingImportHeader.OrderSeg = VanlineStatementStageDetail.OrderControl and
        RevenueRatingImportHeader.PSeg = '' and
        RevenueRatingImportHeader.OSeg = '' and
        RevenueRatingImportHeader.SSeg = ''
    where VanlineStatementStageHeader.VanlineStatementStageHeaderID = @inVanlineStatementStageHeaderID and
        isnull(VanlineStatementStageDetail.OrderControl, '') != '' and
        VanlineStatementStageDetailDist.Amount != 0
end
GO
GRANT EXECUTE ON  [dbo].[VanlineStatementMoveToRatingImport] TO [MssExec]
GO