USP_SALESORDER_CALCULATETAXES

CREATE procedure dbo.USP_SALESORDER_CALCULATETAXES
(
    @ORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    if dbo.UFN_SALESORDER_ISTAXEXEMPT(@ORDERID) = 1 begin
        return;
    end

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @contextCache varbinary(128);

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @SALESMETHODTYPECODE tinyint
    select @SALESMETHODTYPECODE = SALESMETHODTYPECODE from dbo.SALESORDER where ID = @ORDERID

    declare @ORDERTICKET table
    (
        ID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        TOTAL money
    )

    insert into @ORDERTICKET
    select
        SALESORDERITEM.ID,
        SALESORDERITEMTICKET.PROGRAMID,
        SALESORDERITEM.TOTAL
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    where
        SALESORDERITEM.SALESORDERID = @ORDERID
        and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1;  -- Flat rate


    declare @MERCHANDISETAXES table
    (
        TAXID uniqueidentifier,
        TAXNAME nvarchar(100),
        TOTALTAX decimal(7,4),
        SALESORDERITEMID uniqueidentifier,
        SALESORDERITEMTOTAL money
    );

    insert into @MERCHANDISETAXES
    select
        TAX.ID,
        TAX.NAME,
        TAX.TOTALTAX,
        SALESORDERITEM.ID,
        SALESORDERITEM.TOTAL
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
    inner join
        dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
    inner join 
        dbo.MERCHANDISEPRODUCT on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
    inner join 
        dbo.TAX on TAX.ID = MERCHANDISEPRODUCT.TAXID
    where
        SALESORDERITEM.SALESORDERID = @ORDERID
        and TAX.ISACTIVE = 1
        and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1  -- Flat rate

        and MERCHANDISEPRODUCT.TAXID is not null

    begin try
        -- Taxes table variable... contains all taxes in current order, including a bit to describe the kind of tax.

        declare @TAXES table
        (
            ID uniqueidentifier,
            TAXID uniqueidentifier,
            TAXNAME nvarchar(100),
            TOTALS money,
            TOTALTAX decimal(7,4),
            ISPROGRAMTAX bit
        )

        -- Inserting program taxes

        insert into @TAXES
        select 
            null,
            TAX.ID,
            TAX.NAME,
            sum(ITEMAMOUNTPAID.TOTALAFTERDISCOUNTS) * TAX.TOTALTAX * .01,
            TAX.TOTALTAX,
            1
        from dbo.TAX
        inner join dbo.PROGRAMTAX on PROGRAMTAX.TAXID = TAX.ID
        inner join @ORDERTICKET OT on OT.PROGRAMID = PROGRAMTAX.PROGRAMID
        outer apply (
            select sum(SALESORDERITEMITEMDISCOUNT.AMOUNT) TOTAL
            from dbo.SALESORDERITEMITEMDISCOUNT
            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = OT.ID
        ) ITEMDISCOUNTS
        outer apply (
            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) TOTAL
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = OT.ID
        ) ORDERDISCOUNTS
        cross apply (
            select OT.TOTAL - coalesce(ITEMDISCOUNTS.TOTAL, 0) - coalesce(ORDERDISCOUNTS.TOTAL, 0) as TOTALAFTERDISCOUNTS
        ) as ITEMAMOUNTPAID
        where 
            TAX.ISACTIVE = 1
        group by TAX.ID, TAX.TOTALTAX, TAX.NAME;

        -- Inserting fee taxes

        insert into @TAXES
        select 
            null,
            TAX.ID as TAXID,
            TAX.NAME,
            round((isnull(SUM(SALESORDERITEM.TOTAL),0) * TAX.TOTALTAX * .01),2),
            TAX.TOTALTAX,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        inner join dbo.FEETAX on FEETAX.FEEID = SALESORDERITEMFEE.FEEID
        inner join dbo.TAX on TAX.ID = FEETAX.TAXID
        where 
            SALESORDERITEM.SALESORDERID = @ORDERID and 
            TAX.ISACTIVE = 1 and
            [SALESORDERITEM].PRICINGSTRUCTURECODE <> 1  -- Flat rate

        group by TAX.ID, TAX.TOTALTAX, TAX.NAME

        -- Inserting itinerary supply/equipment resource taxes

        insert into @TAXES
        select 
            null,
            TAX.ID as TAXID,
            TAX.NAME,
            round((isnull(SUM(SALESORDERITEM.TOTAL),0) * TAX.TOTALTAX * .01),2),
            TAX.TOTALTAX,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
        inner join dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
        inner join dbo.RESOURCETAX on ITINERARYRESOURCE.RESOURCEID = RESOURCETAX.RESOURCEID
        inner join dbo.TAX on TAX.ID = RESOURCETAX.TAXID
        where 
            SALESORDERITEM.SALESORDERID = @ORDERID and 
            TAX.ISACTIVE = 1 and
            [SALESORDERITEM].PRICINGSTRUCTURECODE <> 1  -- Flat rate

        group by TAX.ID, TAX.TOTALTAX, TAX.NAME

        -- Inserting itinerary item supply/equipment resource taxes

        insert into @TAXES
        select 
            null,
            TAX.ID as TAXID,
            TAX.NAME,
            round((isnull(SUM(SALESORDERITEM.TOTAL),0) * TAX.TOTALTAX * .01),2),
            TAX.TOTALTAX,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
        inner join dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
        inner join dbo.RESOURCETAX on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCETAX.RESOURCEID
        inner join dbo.TAX on TAX.ID = RESOURCETAX.TAXID
        where 
            SALESORDERITEM.SALESORDERID = @ORDERID and 
            TAX.ISACTIVE = 1 and
            [SALESORDERITEM].PRICINGSTRUCTURECODE <> 1  -- Flat rate

        group by TAX.ID, TAX.TOTALTAX, TAX.NAME

        -- Inserting itinerary staffing resource taxes

        insert into @TAXES
        select 
            null,
            TAX.ID as TAXID,
            TAX.NAME,
            round((isnull(SUM(SALESORDERITEM.TOTAL),0) * TAX.TOTALTAX * .01),2),
            TAX.TOTALTAX,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
        inner join dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
        inner join dbo.VOLUNTEERTYPETAX on ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPETAX.VOLUNTEERTYPEID
        inner join dbo.TAX on TAX.ID = VOLUNTEERTYPETAX.TAXID
        where 
            SALESORDERITEM.SALESORDERID = @ORDERID and 
            TAX.ISACTIVE = 1 and
            [SALESORDERITEM].PRICINGSTRUCTURECODE <> 1  -- Flat rate

        group by TAX.ID, TAX.TOTALTAX, TAX.NAME

        -- Inserting itinerary item staffing resource taxes

        insert into @TAXES
        select 
            null,
            TAX.ID as TAXID,
            TAX.NAME,
            round((isnull(SUM(SALESORDERITEM.TOTAL),0) * TAX.TOTALTAX * .01),2),
            TAX.TOTALTAX,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
        inner join dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
        inner join dbo.VOLUNTEERTYPETAX on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPETAX.VOLUNTEERTYPEID
        inner join dbo.TAX on TAX.ID = VOLUNTEERTYPETAX.TAXID
        where 
            SALESORDERITEM.SALESORDERID = @ORDERID and 
            TAX.ISACTIVE = 1 and
            [SALESORDERITEM].PRICINGSTRUCTURECODE <> 1  -- Flat rate

        group by TAX.ID, TAX.TOTALTAX, TAX.NAME

        -- Inserting merchandise taxes

        insert into @TAXES
        select
            null,
            MERCHANDISETAXES.TAXID,
            MERCHANDISETAXES.TAXNAME,
            sum(ITEMAMOUNTPAID.TOTALAFTERDISCOUNTS) * MERCHANDISETAXES.TOTALTAX * .01,
            MERCHANDISETAXES.TOTALTAX,
            0
        from
            @MERCHANDISETAXES as MERCHANDISETAXES
        outer apply (
            select sum(SALESORDERITEMITEMDISCOUNT.AMOUNT) TOTAL
            from dbo.SALESORDERITEMITEMDISCOUNT
            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = MERCHANDISETAXES.SALESORDERITEMID
        ) ITEMDISCOUNTS
        outer apply (
            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) TOTAL
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = MERCHANDISETAXES.SALESORDERITEMID
        ) ORDERDISCOUNTS
        cross apply (
            select MERCHANDISETAXES.SALESORDERITEMTOTAL - coalesce(ITEMDISCOUNTS.TOTAL, 0) - coalesce(ORDERDISCOUNTS.TOTAL, 0) as TOTALAFTERDISCOUNTS
        ) as ITEMAMOUNTPAID
        group by
            MERCHANDISETAXES.TAXID, MERCHANDISETAXES.TOTALTAX, MERCHANDISETAXES.TAXNAME;

        -- Removing taxes for $0

        delete from @TAXES
        where round([TOTALS],2) = 0

        -- Only attempt update or insert taxes if needed

        if exists(select * from @TAXES) begin
            -- Updating the IDs of the SOIs that already exist

            update @TAXES set
                TAXES.ID = SALESORDERITEM.ID
            from @TAXES TAXES
            inner join dbo.SALESORDERITEMTAX on 
                TAXES.TAXID = SALESORDERITEMTAX.TAXID
            inner join dbo.SALESORDERITEM on
                SALESORDERITEMTAX.TAXITEMID = SALESORDERITEM.ID
            where 
                SALESORDERITEM.TYPECODE = 4 and 
                SALESORDERITEM.SALESORDERID = @ORDERID;

            -- Updating the Tax SALESORDERITEM(s) to reflect new totals.

            with TAXTOTALS_CTE as
            (
                select
                    ID as SALESORDERITEMID,
                    round(sum(TOTALS),2) as TOTAL
                from @TAXES 
                where ID is not null
                group by ID
            )
            update dbo.SALESORDERITEM with (rowlock) set 
                PRICE = TAXTOTALS_CTE.TOTAL,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from TAXTOTALS_CTE
            where
                TAXTOTALS_CTE.SALESORDERITEMID = SALESORDERITEM.ID

            -- Prepare new taxes for insertion, taxes with null IDs do not exist as taxes yet    

            declare @TAXESTOTAL table
            (
                ID uniqueidentifier,
                TAXNAME nvarchar(100),
                TOTALS decimal(20,4),
                TOTALTAX decimal(20, 4)
            )

            insert into @TAXESTOTAL
            select
                newid(),
                TAXES.TAXNAME,
                round(sum(TAXES.TOTALS),2),
                TAXES.TOTALTAX
            from @TAXES TAXES
            where TAXES.ID is null
            group by TAXES.TAXNAME, TAXES.TOTALTAX

            -- Inserting Tax SALESORDERITEM(s)

            insert into dbo.SALESORDERITEM
            (
                ID,
                SALESORDERID,
                TYPECODE,
                DESCRIPTION,
                QUANTITY,
                PRICE,
                [PERCENT],
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED,
                ASSEMBLYNAME, SYSTEMTYPENAME 
            )
            select
                TAXES.ID,
                @ORDERID,
                4,
                TAXES.TAXNAME,
                1,
                TAXES.TOTALS,
                TAXES.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE,
                case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.CMS.FCL.ShoppingCart.SalesOrder' else '' end,
                case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.ContentManagement.FrameworkClassLibrary.ShoppingCart.SalesOrder.TaxItem' else '' end
            from @TAXESTOTAL TAXES

            -- Updating the IDs of the SOIs that already exist, need this update to properly link all SALESORDERITEMTAX items.

            update @TAXES set    
                ID = TAXESTOTAL.ID
            from @TAXESTOTAL TAXESTOTAL
            where
                TAXESTOTAL.TOTALTAX = [@TAXES].TOTALTAX and
                TAXESTOTAL.TAXNAME = [@TAXES].TAXNAME;

            -- Inserting linking items.. Program Taxes first                    

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select
                TAX.ID,
                TAX.NAME,
                OT.ID,
                TAXES.ID,
                TAX.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.PROGRAMTAX on PROGRAMTAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join @ORDERTICKET OT on 
                OT.PROGRAMID = PROGRAMTAX.PROGRAMID
            where 
                not exists
                (
                    select * from dbo.SALESORDERITEMTAX
                    inner join @TAXES TAXES on
                        SALESORDERITEMTAX.TAXID = TAXES.TAXID and  
                        SALESORDERITEMTAX.TAXITEMID = TAXES.ID
                    where 
                        SALESORDERITEMTAX.SALESORDERITEMID = OT.ID
                ) and
                TAXES.ISPROGRAMTAX = 1
            group by TAX.ID, TAX.NAME, OT.ID, TAXES.ID, TAX.TOTALTAX

            -- Fee Taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select
                TAX.ID,
                TAX.NAME,
                SALESORDERITEM.ID,
                TAXES.ID,
                TAX.TOTALTAX, 
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.FEETAX on FEETAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.FEEID = FEETAX.FEEID
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
            where
                not exists 
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = TAXES.TAXID and 
                        SOIT.SALESORDERITEMID = SALESORDERITEM.ID and 
                        SOIT.TAXITEMID = TAXES.ID
                ) and
                TAXES.ISPROGRAMTAX = 0 and
                SALESORDERITEM.SALESORDERID = @ORDERID
            group by TAX.ID, TAX.NAME, SALESORDERITEM.ID, TAXES.ID, TAX.TOTALTAX

            -- Inserting itinerary supply/equipment resource taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select
                TAX.ID,
                TAX.NAME,
                SALESORDERITEM.ID,
                TAXES.ID,
                TAX.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.RESOURCETAX on RESOURCETAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join dbo.ITINERARYRESOURCE on RESOURCETAX.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
            inner join dbo.SALESORDERITEMITINERARYRESOURCE on ITINERARYRESOURCE.ID = SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
            where 
                not exists 
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = TAXES.TAXID and 
                        SOIT.SALESORDERITEMID = SALESORDERITEM.ID and 
                        SOIT.TAXITEMID = TAXES.ID
                ) and
                TAXES.ISPROGRAMTAX = 0 and
                SALESORDERITEM.SALESORDERID = @ORDERID
            group by TAX.ID, TAX.NAME, SALESORDERITEM.ID, TAXES.ID, TAX.TOTALTAX

            -- Inserting itinerary item supply/equipment resource taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )                    
            select
                TAX.ID,
                TAX.NAME,
                SALESORDERITEM.ID,
                TAXES.ID,
                TAX.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.RESOURCETAX on RESOURCETAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join dbo.ITINERARYITEMRESOURCE on RESOURCETAX.RESOURCEID = ITINERARYITEMRESOURCE.RESOURCEID
            inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on ITINERARYITEMRESOURCE.ID = SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
            where 
                not exists 
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = TAXES.TAXID and 
                        SOIT.SALESORDERITEMID = SALESORDERITEM.ID and 
                        SOIT.TAXITEMID = TAXES.ID
                ) and
                TAXES.ISPROGRAMTAX = 0 and
                SALESORDERITEM.SALESORDERID = @ORDERID
            group by TAX.ID, TAX.NAME, SALESORDERITEM.ID, TAXES.ID, TAX.TOTALTAX

            -- Inserting itinerary staffing resource taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )                    
            select
                TAX.ID,
                TAX.NAME,
                SALESORDERITEM.ID,
                TAXES.ID,
                TAX.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.VOLUNTEERTYPETAX on VOLUNTEERTYPETAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join dbo.ITINERARYSTAFFRESOURCE on VOLUNTEERTYPETAX.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID
            inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on ITINERARYSTAFFRESOURCE.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
            where 
                not exists 
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = TAXES.TAXID and 
                        SOIT.SALESORDERITEMID = SALESORDERITEM.ID and 
                        SOIT.TAXITEMID = TAXES.ID
                ) and
                TAXES.ISPROGRAMTAX = 0 and
                SALESORDERITEM.SALESORDERID = @ORDERID
            group by TAX.ID, TAX.NAME, SALESORDERITEM.ID, TAXES.ID, TAX.TOTALTAX

            -- Inserting itinerary item staffing resource taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select
                TAX.ID,
                TAX.NAME,
                SALESORDERITEM.ID,
                TAXES.ID,
                TAX.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.TAX
            inner join dbo.VOLUNTEERTYPETAX on VOLUNTEERTYPETAX.TAXID = TAX.ID
            inner join @TAXES TAXES on TAXES.TAXID = TAX.ID
            inner join dbo.ITINERARYITEMSTAFFRESOURCE on VOLUNTEERTYPETAX.VOLUNTEERTYPEID = ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
            inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on ITINERARYITEMSTAFFRESOURCE.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
            where 
                not exists 
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = TAXES.TAXID and 
                        SOIT.SALESORDERITEMID = SALESORDERITEM.ID and 
                        SOIT.TAXITEMID = TAXES.ID
                ) and
                TAXES.ISPROGRAMTAX = 0 and
                SALESORDERITEM.SALESORDERID = @ORDERID
            group by TAX.ID, TAX.NAME, SALESORDERITEM.ID, TAXES.ID, TAX.TOTALTAX

            -- Inserting merchandise taxes

            insert into dbo.SALESORDERITEMTAX
            (
                TAXID,
                TAXNAME,
                SALESORDERITEMID,
                TAXITEMID,
                TOTALTAX,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select 
                MERCHANDISETAXES.TAXID,
                MERCHANDISETAXES.TAXNAME,
                MERCHANDISETAXES.SALESORDERITEMID,
                MERCHANDISETAXITEM.ID,
                MERCHANDISETAXES.TOTALTAX,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from
                @MERCHANDISETAXES MERCHANDISETAXES
            cross apply (
                select top 1 TAXES.ID
                from @TAXES as TAXES
                where TAXES.ISPROGRAMTAX = 0 and TAXES.TAXID = MERCHANDISETAXES.TAXID
            ) as MERCHANDISETAXITEM
            where
                not exists
                (
                    select * from dbo.SALESORDERITEMTAX SOIT 
                    where 
                        SOIT.TAXID = MERCHANDISETAXES.TAXID and 
                        SOIT.SALESORDERITEMID = MERCHANDISETAXES.SALESORDERITEMID and 
                        SOIT.TAXITEMID = MERCHANDISETAXITEM.ID
                );
        end

        -- Deleting taxes that no longer exist.

        declare @TAXESTOBEDELETED table (ID uniqueidentifier)
        insert into @TAXESTOBEDELETED
        select ID from dbo.SALESORDERITEM with (nolock)
        where
            SALESORDERID = @ORDERID and 
            TYPECODE = 4 and
            not exists
            (
                select 1 from @TAXES TAXES
                where TAXES.ID = SALESORDERITEM.ID
            )

        if @@rowcount > 0 begin
            set @contextCache = CONTEXT_INFO();
            set CONTEXT_INFO @CHANGEAGENTID;

            delete from dbo.SALESORDERITEMTAX
            where TAXITEMID in (select ID from @TAXESTOBEDELETED);

            delete from dbo.SALESORDERITEM with (rowlock)
            where ID in (select ID from @TAXESTOBEDELETED);

            if @contextCache is not null begin
                set CONTEXT_INFO @contextCache;
            end
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;