/**
*    This procedure is called to process revenue rating invoice details information.
*    @param @inRatingXmlDoc The Xml document handle for the openxml calls.
*    @param @inRatingXml The actual Xml text containing header information.
*    @param @inHeaderPriKey The primary key for the associated header information.  We'll
*            create a new header for this import using the header that was passed in.
*    @param @outErrorCode The error code for this procedure, 0 if none.
*            0 - success
*            2164 - unknown error, detail not added.
*            2165 - carrier import type not defined.
*            2166 - unknown error, header not added.
*/

CREATE PROCEDURE [dbo].[XmlImportProcessRatingInvoiceDetails]
    @inRatingXmlDoc int,
    @inRatingXml text,
    @inHeaderPriKey int,
    @outErrorCode int output,
    @outErrorData varchar(max) output
as

-- No count
set nocount on;

-- Set up any error codes that may be used
declare @ERRORCODE_DETAIL_NOT_ADDED int = 2164;
declare @ERRORCODE_CARRIER_IMPORT_TYPE_UNDEFINED int = 2165;
declare @ERRORCODE_HEADER_NOT_ADDED int = 2166;

-- Store the Revenue Rating DetailID
declare @theInterfaceID int

declare @theExternalVanLineCode varchar(32);
declare @theOrderSeg varchar(16);
declare @thePSeg varchar(1);
declare @theOSeg varchar(1);
declare @theSSeg varchar(1);
declare @theSourceRevenueImportTypeName varchar(50);
declare @theRevenueImportTypeName varchar(50);
declare @theErrorCode int;
declare @theICPriKey int;
declare @theItemCode int;
declare @theRGPriKey int;
declare @theInvoice varchar( 10 );
declare @theAgentPercentage decimal( 12, 4 );
declare @theVanlineGroupExternalIdentifier varchar(10);
declare @theXPathQuery nvarchar(1024);
declare @theXmlDescription varchar(128);
declare @theDescription varchar(128);
declare @theDistributionCode varchar(10);
declare @theRawPrintDescription1 varchar(128);
declare @theRawPrintDescription2 varchar(128);
declare @theRawPrintDescription3 varchar(128);
declare @thePrintDescription1 varchar(128);
declare @thePrintDescription2 varchar(128);
declare @thePrintDescription3 varchar(128);
declare @theReversePrintDescription varchar(128);
declare @theRateTypeExternalCode varchar(16);
declare @thePos int;
declare @theFoundIt bit;
declare @theQuantity varchar(20);
declare @theQuantity2 varchar(20);
declare @theRate varchar(20);
declare @theRowsAddedCount int;
declare @theErrorValue int;
declare @theRevenueRatingImportHeaderID int;
declare @theOkToContinueFlag bit = 0;
declare @theGrossAmount float;
declare @theDiscountPct float;

-- Get the number of detail records
declare @theCurrentDetail int = 1;
declare @theNumDetailsRecords int =
(
    select count( * )
    from openxml( @inRatingXmlDoc, '/MSS:Order/MSS:Revenue/MSS:InvoiceDetails/MSS:InvoiceDetail' )
    with
    (
        OrderSeg    varchar(16) '../../../@OrderSeg'
    ) as XmlOrder
);

if( @theNumDetailsRecords > 0 )
begin
    select
        @theSourceRevenueImportTypeName = RevenueImportType.[Name],
        @theRevenueImportTypeName = case RevenueImportType.[Name]
            -- Correspnding carrier import types for this stored proc must be explicitly defined here.
            when 'Atlas Revenue' then 'Atlas Carrier'
            else null
        end,
        @theExternalVanLineCode = RevenueRatingImportHeader.XmlExternalInterfaceVanlineCode,
        @theOrderSeg = RevenueRatingImportHeader.OrderSeg,
        @thePSeg = RevenueRatingImportHeader.PSeg,
        @theOSeg = RevenueRatingImportHeader.OSeg,
        @theSSeg = RevenueRatingImportHeader.SSeg,
        @theInterfaceID = RevenueRatingImportHeader.XmlInterfaceFID
    from RevenueRatingImportHeader
    inner join RevenueImportType on RevenueImportType.PriKey = RevenueRatingImportHeader.RevenueImportTypeFID
    where RevenueRatingImportHeader.RevenueRatingImportHeaderID = @inHeaderPriKey;

    if( @theRevenueImportTypeName is null )
    begin
        select
            @outErrorCode = @ERRORCODE_CARRIER_IMPORT_TYPE_UNDEFINED,
            @outErrorData = @theSourceRevenueImportTypeName
    end
    else
    begin
        set @theOkToContinueFlag = 1;
    end;
end;

if( @theOkToContinueFlag = 1 )
begin
    -- Grab primary key to see if this stored proc created a header previously
    set @theRevenueRatingImportHeaderID = isnull(
    (
        select RevenueRatingImportHeaderID
        from RevenueRatingImportHeader
        inner join RevenueImportType on RevenueImportType.PriKey = RevenueRatingImportHeader.RevenueImportTypeFID
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.[Name] = RevenueImportType.XmlSystemDataTypeMapName
        inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
        where RevenueImportType.[Name] = @theRevenueImportTypeName and
            RevenueRatingImportHeader.XmlInterfaceFID = XmlSystem.XmlInterfaceFID and
            isnull( RevenueRatingImportHeader.XmlExternalInterfaceVanlineCode, '' ) = isnull( @theExternalVanLineCode, '' ) and
            OrderSeg = @theOrderSeg and
            isnull( PSeg, '' ) = isnull( @thePSeg, '' ) and
            isnull( OSeg, '' ) = isnull( @theOSeg, '' ) and
            isnull( SSeg, '' ) = isnull( @theSSeg, '' )
    ), -1 );

    -- If this header already exists, then update it, otherwise insert a new header
    if ( -1 != @theRevenueRatingImportHeaderID )
    begin
        update RevenueRatingImportHeader set
            BookingAgentPercentage = SourceRevenueRatingImportHeader.BookingAgentPercentage,
            DestinationAgentPercentage = SourceRevenueRatingImportHeader.DestinationAgentPercentage,
            DiscountLineHaul = SourceRevenueRatingImportHeader.DiscountLineHaul,
            DiscountPercentage = SourceRevenueRatingImportHeader.DiscountPercentage,
            DownloadDateTime = SourceRevenueRatingImportHeader.DownloadDateTime,
            HaulingAgentPercentage = SourceRevenueRatingImportHeader.HaulingAgentPercentage,
            LineHaul = SourceRevenueRatingImportHeader.LineHaul,
            GrossTransportationAmount = SourceRevenueRatingImportHeader.GrossTransportationAmount,
            TotalWeight = SourceRevenueRatingImportHeader.TotalWeight,
            OriginAgentPercentage = SourceRevenueRatingImportHeader.OriginAgentPercentage
        from RevenueRatingImportHeader
        inner join RevenueRatingImportHeader as SourceRevenueRatingImportHeader on
            SourceRevenueRatingImportHeader.RevenueRatingImportHeaderID = @inHeaderPriKey
        where RevenueRatingImportHeader.RevenueRatingImportHeaderID = @theRevenueRatingImportHeaderID
    end
    else
    begin
        -- We need a separate header so we'll just mostly duplicate the one that was passed into us.
        insert into RevenueRatingImportHeader
        (
            BookingAgentPercentage,
            DestinationAgentPercentage,
            DiscountLineHaul,
            DiscountPercentage,
            DownloadDateTime,
            HaulingAgentPercentage,
            LineHaul,
            GrossTransportationAmount,
            TotalWeight,
            OrderSeg,
            PSeg,
            OSeg,
            SSeg,
            OriginAgentPercentage,
            RevenueImportTypeFID,
            XmlExternalInterfaceVanlineCode,
            XMLInterfaceFID,
            IsOwnAuthorityFlag
        )
        select
            BookingAgentPercentage = RevenueRatingImportHeader.BookingAgentPercentage,
            DestinationAgentPercentage = RevenueRatingImportHeader.DestinationAgentPercentage,
            DiscountLineHaul = RevenueRatingImportHeader.DiscountLineHaul,
            DiscountPercentage = RevenueRatingImportHeader.DiscountPercentage,
            DownloadDateTime = RevenueRatingImportHeader.DownloadDateTime,
            HaulingAgentPercentage = RevenueRatingImportHeader.HaulingAgentPercentage,
            LineHaul = RevenueRatingImportHeader.LineHaul,
            GrossTransportationAmount = RevenueRatingImportHeader.GrossTransportationAmount,
            TotalWeight = RevenueRatingImportHeader.TotalWeight,
            OrderSeg = RevenueRatingImportHeader.OrderSeg,
            PSeg = RevenueRatingImportHeader.PSeg,
            OSeg = RevenueRatingImportHeader.OSeg,
            SSeg = RevenueRatingImportHeader.SSeg,
            OriginAgentPercentage = RevenueRatingImportHeader.OriginAgentPercentage,
            RevenueImportTypeFID = RevenueImportType.PriKey,
            XmlExternalInterfaceVanlineCode = RevenueRatingImportHeader.XmlExternalInterfaceVanlineCode,
            XMLInterfaceFID = XmlSystem.XmlInterfaceFID,
            IsOwnAuthorityFlag = RevenueRatingImportHeader.IsOwnAuthorityFlag
        from RevenueRatingImportHeader
        inner join RevenueImportType on RevenueImportType.[Name] = @theRevenueImportTypeName
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.[Name] = RevenueImportType.XmlSystemDataTypeMapName
        inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
        where RevenueRatingImportHeader.RevenueRatingImportHeaderID = @inHeaderPriKey;

        select
            @theErrorValue = @@error,
            @theRowsAddedCount = @@rowcount;

        if( @theRowsAddedCount > 0 )
        begin
            set @theRevenueRatingImportHeaderID =  scope_identity();
        end
        else
        begin
            select
                @outErrorCode = @ERRORCODE_HEADER_NOT_ADDED,
                @outErrorData = concat( 'SQL Error: ', str( @theErrorValue ) ),
                @theOkToContinueFlag = 0;
        end;
    end;
end;

if( @theOkToContinueFlag = 1 )
begin
    -- First delete all invoice details for this header
    delete RevenueRatingImportInvoiceDetail
    where RevenueRatingImportHeaderFID = @theRevenueRatingImportHeaderID

    -- @theRevenueRatingImportHeaderID here always means to look in RevenueRatingImportHeader table.
    set @theVanlineGroupExternalIdentifier = dbo.GetVanlineGroupExternalCodeFromRatingType( @theRevenueRatingImportHeaderID, 0 );

    -- Iterate through all detail records, inserting each one
    while( ( @theCurrentDetail <= @theNumDetailsRecords ) and ( @outErrorCode = 0 ) )
    begin
        -- Setup our XPath query for the openxml
        set @theXPathQuery = concat( '/MSS:Order/MSS:Revenue/MSS:InvoiceDetails/MSS:InvoiceDetail[', ltrim( str( @theCurrentDetail ) ), ']' );

        -- Add detail if there was not error looking of agent
        if( 0 = @outErrorCode )
        begin
            select
                @theXmlDescription = case when XmlDescriptionNull = 1 then null else XmlDescription end,
                @theDistributionCode = case when XmlDistributionCodeNull = 1 then null else XmlDistributionCode end,
                @theGrossAmount = case when XmlGrossAmountNull = 1 then null else cast( XmlGrossAmount as float ) end,
                @theDiscountPct = case when XmlDiscountPctNull = 1 then null else cast( XmlDiscountPct as float ) end,
                @theRawPrintDescription1 = case when XmlPrintDescription1Null = 1 or isnull( XmlPrintDescription1, '' ) = '' then null else XmlPrintDescription1 end,
                @theRawPrintDescription2 = case when XmlPrintDescription2Null = 1 or isnull( XmlPrintDescription2, '' ) = '' then null else XmlPrintDescription2 end,
                @theRawPrintDescription3 = case when XmlPrintDescription3Null = 1 or isnull( XmlPrintDescription3, '' ) = '' then null else XmlPrintDescription3 end
            from openxml( @inRatingXmlDoc, @theXPathQuery )
            with
            (
                XmlDescriptionNull            bit                'MSS:Description/@xsi:nil',
                XmlDescription                varchar(128)    'MSS:Description',

                XmlDistributionCodeNull        bit                'MSS:DistributionCode/@xsi:nil',
                XmlDistributionCode            varchar(10)        'MSS:DistributionCode',

                XmlGrossAmountNull            bit                'MSS:GrossAmount/@xsi:nil',
                XmlGrossAmount                float            'MSS:GrossAmount',

                XmlDiscountPctNull            bit                'MSS:DiscountPct/@xsi:nil',
                XmlDiscountPct                float            'MSS:DiscountPct',

                XmlPrintDescription1Null    bit                'MSS:PrintDescription/@xsi:nil',
                XmlPrintDescription1        varchar(128)    'MSS:PrintDescription',

                XmlPrintDescription2Null    bit                'MSS:PrintDescription2/@xsi:nil',
                XmlPrintDescription2        varchar(128)    'MSS:PrintDescription2',

                XmlPrintDescription3Null    bit                'MSS:PrintDescription3/@xsi:nil',
                XmlPrintDescription3        varchar(128)    'MSS:PrintDescription3'
            );

            select
                -- Replacing commas so commas are not part of weights (3,880) etc.
                @thePrintDescription1 = ltrim( rtrim( replace( @theRawPrintDescription1, ',', '' ) ) ),
                @thePrintDescription2 = ltrim( rtrim( replace( @theRawPrintDescription2, ',', '' ) ) ),
                @thePrintDescription3 = ltrim( rtrim( replace( @theRawPrintDescription3, ',', '' ) ) ),
                @theDescription = null,
                @theQuantity = null,
                @theQuantity2 = null,
                @theRate = null,
                @theRateTypeExternalCode = null,
                @theFoundIt = 0;

            -- If an ItemCode description exists for this revenue item then we'll use that instead of the provided description
            exec spRevenueImportGetItemCode
                @theDistributionCode,
                null, -- The CartonCode
                null, -- The AgentPercentage is not used so ignore this parameter.
                null, -- The ShipmentType is not used so ignore this parameter
                @theErrorCode output,
                @theICPriKey output,
                @theItemCode output,
                @theDescription output,
                @theRGPriKey output,
                @theInvoice output,
                @theAgentPercentage output,
                @theVanlineGroupExternalIdentifier; -- @inVanlineGroupExternalCode

            -- Extract quantity, quantity2 and/or rate from the printed description lines.
            if( @thePrintDescription1 is not null )
            begin
                -- Example: FUEL LINEHAUL @ 11.00 %
                -- Example: DESTINATION FUEL-CTG @ 11.00 %
                if( @thePrintDescription1 like '% @ % !%' escape '!' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @thePos = charindex( '% ', @theReversePrintDescription );
                    if( @thePos > 0 )
                    begin
                        set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 2, len( @theReversePrintDescription ) - @thePos - 1 );
                        set @thePos = charindex( ' ', @theReversePrintDescription );
                        if( @thePos > 1 )
                        begin
                            set @theRate = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                            set @theFoundIt = isnumeric( @theRate );
                        end;
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theRate = null;
                    end
                    else
                    begin
                        set @theRateTypeExternalCode = 'FUEL-PCT';
                    end;
                end;
                -- Example: DEST CARTAGE 3880 LBS
                if( @theFoundIt = 0 and @thePrintDescription1 like '% % LBS' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @theReversePrintDescription = substring( @theReversePrintDescription, 5, len( @theReversePrintDescription ) - 4 );
                    set @thePos = charindex( ' ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theQuantity = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        set @theFoundIt = isnumeric( @theQuantity );
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theQuantity = null;
                    end;
                end;
                -- Example: EA ADDL DA 03/05/24 - 03/12/24 = 8 DAYS 3880 LBS @ 0.80 CWT
                if( @theFoundIt = 0 and @thePrintDescription1 like '% % DAYS % LBS @ % CWT' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @theReversePrintDescription = substring( @theReversePrintDescription, 5, len( @theReversePrintDescription ) - 4 );
                    set @thePos = charindex( ' @ SBL ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theRate = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        if( isnumeric( @theRate ) = 1 )
                        begin
                            set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 7, len( @theReversePrintDescription ) - @thePos - 6 );
                            set @thePos = charindex( ' SYAD ', @theReversePrintDescription );
                            if( @thePos > 1 )
                            begin
                                set @theQuantity2 = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                                if( isnumeric( @theQuantity2 ) = 1 )
                                begin
                                    set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 6, len( @theReversePrintDescription ) - @thePos - 5 );
                                    set @thePos = charindex( ' ', @theReversePrintDescription );
                                    if( @thePos > 1 )
                                    begin
                                        set @theQuantity = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                                        set @theFoundIt = isnumeric( @theQuantity );
                                    end;
                                end;
                            end;
                        end;
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theRate = null;
                        set @theQuantity = null;
                        set @theQuantity2 = null;
                    end
                    else
                    begin
                        set @theRateTypeExternalCode = 'SIT';
                    end;
                end;
                -- Example: DEST SERV CHG 3880 LBS @ 7.34 CWT
                -- Example: ORIG SERV CHG 3880 LBS @ 5.04 CWT
                if( @theFoundIt = 0 and @thePrintDescription1 like '% LBS @ % CWT' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @theReversePrintDescription = substring( @theReversePrintDescription, 5, len( @theReversePrintDescription ) - 4 );
                    set @thePos = charindex( ' @ SBL ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theRate = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        if( isnumeric( @theRate ) = 1 )
                        begin
                            set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 7, len( @theReversePrintDescription ) - @thePos - 6 );
                            set @thePos = charindex( ' ', @theReversePrintDescription );
                            if( @thePos > 1 )
                            begin
                                set @theQuantity = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                                set @theFoundIt = isnumeric( @theQuantity );
                            end;
                        end;
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theRate = null;
                        set @theQuantity = null;
                    end
                    else
                    begin
                        set @theRateTypeExternalCode = 'CWT';

                        if( @thePrintDescription1 like 'ORIG %' or @thePrintDescription1 like 'DEST %' )
                        begin
                            set @theQuantity2 = @theQuantity;
                            set @theQuantity = '1';
                        end;
                    end;
                end;
                -- Example: RT DATE: 02/16/24 ACT.WT: 3880 TRANSPORTATION WT: 3880
                if( @theFoundIt = 0 and @thePrintDescription1 like '%ACT.WT: % TRANSPORTATION WT: %' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @thePos = charindex( ' ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theQuantity = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        set @theFoundIt = isnumeric( @theQuantity );
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theQuantity = null;
                    end;
                end;
                -- Example: LOAD GOLF-CART 1 @ 267.82
                -- Example: RELOAD GOLF-CART 1 @ 267.82
                if( @theFoundIt = 0 and @thePrintDescription1 like '% %@ %' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription1 );
                    set @thePos = charindex( ' ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theRate = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        if( isnumeric( @theRate ) = 1 )
                        begin
                            set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 1, len( @theReversePrintDescription ) - @thePos );
                            if( substring( @theReversePrintDescription, 1, 2 ) = '@ ' )
                            begin
                                set @theReversePrintDescription = substring( @theReversePrintDescription, 3, len( @theReversePrintDescription ) - 2 );
                                set @thePos = charindex( ' ', @theReversePrintDescription );
                                if( @thePos > 1 )
                                begin
                                    set @theQuantity = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                                    set @theFoundIt = isnumeric( @theQuantity );
                                end;
                            end
                        end;
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theRate = null;
                        set @theQuantity = null;
                    end
                    else
                    begin
                        set @theRateTypeExternalCode = 'EACH';
                    end;
                end;
                -- Example (Desc1): DEST - 1ST DAY STG STG RATES FROM: JACKSONVILLE, FL (Desc2): 03/04/24 3880 LBS @ 18.92 CWT
                if( @theFoundIt = 0 and @thePrintDescription1 like '% - 1ST DAY STG %' and @thePrintDescription2 like '% LBS @ % CWT' )
                begin
                    set @theReversePrintDescription = reverse( @thePrintDescription2 );
                    set @theReversePrintDescription = substring( @theReversePrintDescription, 5, len( @theReversePrintDescription ) - 4 );
                    set @thePos = charindex( ' @ SBL ', @theReversePrintDescription );
                    if( @thePos > 1 )
                    begin
                        set @theRate = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                        if( isnumeric( @theRate ) = 1 )
                        begin
                            set @theReversePrintDescription = substring( @theReversePrintDescription, @thePos + 7, len( @theReversePrintDescription ) - @thePos - 6 );
                            set @thePos = charindex( ' ', @theReversePrintDescription );
                            if( @thePos > 1 )
                            begin
                                set @theQuantity2 = reverse( substring( @theReversePrintDescription, 1, @thePos - 1 ) );
                                set @theFoundIt = isnumeric( @theQuantity2 );
                                set @theQuantity = '1';
                            end;
                        end;
                    end;
                    if( @theFoundIt = 0 )
                    begin
                        set @theRate = null;
                        set @theQuantity = null;
                        set @theQuantity2 = null;
                    end
                    else
                    begin
                        set @theRateTypeExternalCode = 'CWT';
                    end;
                end;
            end;

            set @theReversePrintDescription = substring( ltrim( rtrim( concat( @theRawPrintDescription1, ' ' + @theRawPrintDescription2, ' ' + @theRawPrintDescription3 ) ) ), 1, 128 );

            -- OK, now we are ready to insert our invoice detail.
            insert into RevenueRatingImportInvoiceDetail
            (
                RevenueRatingImportHeaderFID,
                [Description],
                DistributionCode,
                GrossAmount,
                DiscountPct,
                PrintDescription,
                Quantity,
                Quantity2,
                Rate,
                RateTypeFID
            )
            select
                RevenueRatingImportHeaderFID = @theRevenueRatingImportHeaderID,
                [Description] = case when isnull( @theDescription, '' ) = '' then @theXmlDescription else @theDescription end,
                DistributionCode = @theDistributionCode,
                GrossAmount = @theGrossAmount,
                DiscountPct = @theDiscountPct,
                PrintDescription = case when isnull( @theReversePrintDescription, '' ) = '' then null else @theReversePrintDescription end,
                Quantity = case when isnull( @theQuantity, '' ) = '' or isnumeric( @theQuantity ) = 0 then null else convert( decimal(12,4), @theQuantity ) end,
                Quantity2 = case when isnull( @theQuantity2, '' ) = '' or isnumeric( @theQuantity2 ) = 0 then null else convert( decimal(12,4), @theQuantity2 ) end,
                Rate = case when isnull( @theRate, '' ) = '' or isnumeric( @theRate ) = 0 then null else convert( decimal(12,4), @theRate ) end,
                RateTypeFID = case
                    when isnull( @theRateTypeExternalCode, '' ) = '' then null
                    else ( select RateTypeFID from XmlInterfaceRateTypeMap where XmlInterfaceFID = @theInterfaceID and ExternalRateTypeCode = @theRateTypeExternalCode )
                end;

            select
                @theErrorValue = @@error,
                @theRowsAddedCount = @@rowcount;

            -- Check to see if the insert succeeded
            if( ( @theRowsAddedCount = 0 ) or ( @theErrorValue != 0 ) )
            begin
                set @outErrorCode = @ERRORCODE_DETAIL_NOT_ADDED;
            end
        end; -- if( 0 = @outErrorCode )

        -- Update to our next XPath record.
        set @theCurrentDetail = @theCurrentDetail + 1;
    
    end; -- while
end;
GO
GRANT EXECUTE ON  [dbo].[XmlImportProcessRatingInvoiceDetails] TO [MssExec]
GO