UFN_REVENUE_GENERATEGLACCOUNT_APPLYOVERRIDES

CREATE function dbo.UFN_REVENUE_GENERATEGLACCOUNT_APPLYOVERRIDES
(
    @REVENUEINFORMATION xml, 
    @REVENUESPLITINFORMATION xml,
    @REVENUETRANSACTIONTYPECODE tinyint, 
    @REVENUESPLITTYPECODE tinyint, 
    @APPLICATIONCODE tinyint, 
    @PAYMENTMETHODCODE tinyint, 
    @DESIGNATIONID uniqueidentifier
)
returns @RevenueXml table 
    (
        REVENUEINFORMATION xml, 
        REVENUESPLITINFORMATION xml
    )
as
begin

    if @REVENUEINFORMATION is not null
    begin
        if @PAYMENTMETHODCODE is not null
            begin
            declare @PAYMENTMETHODCODEXML as xml = '<PAYMENTMETHODCODE>'+cast(@PAYMENTMETHODCODE as nvarchar(3)) + '</PAYMENTMETHODCODE>'                  
            set @REVENUEINFORMATION.modify('
                    delete (/REVENUEINFORMATION/ITEM[1]/PAYMENTMETHODCODE)[1]
                ');
            set @REVENUEINFORMATION.modify('
                    insert sql:variable("@PAYMENTMETHODCODEXML")
                    as first into (/REVENUEINFORMATION/ITEM)[1]          
                ');
            end

        if @REVENUETRANSACTIONTYPECODE is not null
            begin
            declare @TRANSACTIONTYPECODEXML as xml = '<TRANSACTIONTYPECODE>'+cast(@REVENUETRANSACTIONTYPECODE as nvarchar(3)) + '</TRANSACTIONTYPECODE>'                  
            set @REVENUEINFORMATION.modify('
                    delete (/REVENUEINFORMATION/ITEM[1]/TRANSACTIONTYPECODE)[1]
                ');
            set @REVENUEINFORMATION.modify('
                    insert sql:variable("@TRANSACTIONTYPECODEXML")
                    as first into (/REVENUEINFORMATION/ITEM)[1]          
                ');
            end
    end                  


    if @REVENUESPLITINFORMATION is not null
    begin
        if @REVENUESPLITTYPECODE is not null
            begin
                declare @TYPECODEXML as xml = '<TYPECODE>'+cast(@REVENUESPLITTYPECODE as nvarchar(3)) + '</TYPECODE>'                  
            set @REVENUESPLITINFORMATION.modify('
                    delete (/REVENUESPLITINFORMATION/ITEM[1]/TYPECODE)[1]
                ');
                set @REVENUESPLITINFORMATION.modify('
                    insert sql:variable("@TYPECODEXML")
                    as first into (/REVENUESPLITINFORMATION/ITEM)[1]          
                ');
            end

        if @APPLICATIONCODE is not null
            begin
                declare @APPLICATIONCODEXML as xml = '<APPLICATIONCODE>'+cast(@APPLICATIONCODE as nvarchar(3)) + '</APPLICATIONCODE>'                  
            set @REVENUESPLITINFORMATION.modify('
                    delete (/REVENUESPLITINFORMATION/ITEM[1]/APPLICATIONCODE)[1]
                ');
                set @REVENUESPLITINFORMATION.modify('
                    insert sql:variable("@APPLICATIONCODEXML")
                    as first into (/REVENUESPLITINFORMATION/ITEM)[1]          
                ');
            end

        if @DESIGNATIONID is not null
            begin
                declare @DESIGNATIONXML as xml = '<DESIGNATIONID>'+cast(@DESIGNATIONID as nvarchar(36)) + '</DESIGNATIONID>'                  
            set @REVENUESPLITINFORMATION.modify('
                    delete (/REVENUESPLITINFORMATION/ITEM[1]/DESIGNATIONID)[1]
                ');
                set @REVENUESPLITINFORMATION.modify('
                    insert sql:variable("@DESIGNATIONXML")
                    as first into (/REVENUESPLITINFORMATION/ITEM)[1]          
                ');
            end
    end

    insert into @RevenueXml (REVENUEINFORMATION, REVENUESPLITINFORMATION)           
    select @REVENUEINFORMATION, @REVENUESPLITINFORMATION

    return
end