UFN_REVENUE_GENERATEDEFAULTGIFTFEES

            CREATE function dbo.UFN_REVENUE_GENERATEDEFAULTGIFTFEES(
                        @REVENUEID uniqueidentifier,
                        @CONSTITUENTID uniqueidentifier)
            returns @GIFTFEES table 
                (ID uniqueidentifier, 
                FEE money,
                WAIVED bit,
                SPLITRECEIPTAMOUNT money,
                BASECURRENCYID uniqueidentifier,
                ORGANIZATIONAMOUNT money,
                ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                TRANSACTIONAMOUNT money,
                TRANSACTIONCURRENCYID uniqueidentifier,
                BASEEXCHANGERATEID uniqueidentifier)

            AS    
            begin

                --Do nothing if this is turned off.

                if dbo.UFN_GIFTFEE_ENABLED() = 0
                    return;

                declare @LINEITEMAMOUNTSTOTALWITHGIFTFEES money;
                declare @TOTALAMOUNT money;
                declare @RECEIPTAMOUNT money;
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONAMOUNT money;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                declare @SPLITSXML xml = null;
        declare @SPLITS table 
        (
            ID uniqueidentifier,
            TRANSACTIONAMOUNT money,
            APPLICATIONCODE int,
            DESIGNATIONLEVELID uniqueidentifier,
            AMOUNT money,
            RECEIPTAMOUNT money,
            ORGANIZATIONAMOUNT money,
            BASECURRENCYID uniqueidentifier,
            TRANSACTIONCURRENCYID uniqueidentifier,
            BASEEXCHANGERATEID uniqueidentifier,
            ORGANIZATIONEXCHANGERATEID uniqueidentifier
        );

        insert into @SPLITS
        select 
                        REVENUESPLIT.ID, 
                        REVENUESPLIT.TRANSACTIONAMOUNT, 
                        REVENUESPLIT.APPLICATIONCODE, 
                        coalesce(DESIGNATION.DESIGNATIONLEVEL5ID, DESIGNATION.DESIGNATIONLEVEL4ID, DESIGNATION.DESIGNATIONLEVEL3ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL1ID) AS DESIGNATIONLEVELID,
                        REVENUE.AMOUNT,
                        REVENUE.RECEIPTAMOUNT,
                        REVENUE.ORGANIZATIONAMOUNT,
                        REVENUE.BASECURRENCYID,
                        REVENUE.TRANSACTIONCURRENCYID,
                        REVENUE.BASEEXCHANGERATEID,
                        REVENUE.ORGANIZATIONEXCHANGERATEID
                  from dbo.REVENUESPLIT
                  inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                  left outer join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                  where REVENUE.ID = @REVENUEID and 
                    ( (REVENUESPLIT.APPLICATIONCODE <> 10 and exists (select APPLICATIONCODE from dbo.GIFTFEEAPPLICATIONTYPE where APPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE)) or
                     (REVENUESPLIT.APPLICATIONCODE = 10 and exists (select APPLICATIONCODE from dbo.GIFTFEEAPPLICATIONTYPE where APPLICATIONCODE = case REVENUESPLIT.TYPECODE
                                                                                                                when 0 then 0 -- Donation

                                                                                                                when 1 then 1 -- Event registration

                                                                                                                when 2 then 5 -- Membership

                                                                                                             end))
          )


                select @LINEITEMAMOUNTSTOTALWITHGIFTFEES = sum(TRANSACTIONAMOUNT) from @SPLITS;
                select @TOTALAMOUNT = AMOUNT from @SPLITS;
                select @RECEIPTAMOUNT = RECEIPTAMOUNT from @SPLITS;
                select @ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT from @SPLITS;
                select @BASECURRENCYID = BASECURRENCYID from @SPLITS;
                select @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID from @SPLITS;
                select @BASEEXCHANGERATEID = BASEEXCHANGERATEID from @SPLITS;
                select @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID from @SPLITS;

                set @SPLITSXML = (select
                            ID,
                            TRANSACTIONAMOUNT,
                            APPLICATIONCODE,
                            DESIGNATIONLEVELID
                          from @SPLITS order by ID
                                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64); 
                insert into @GIFTFEES
                select
                        ID, 
                        FEE,
                        WAIVED,
                        SPLITRECEIPTAMOUNT,
                        BASECURRENCYID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID,
                        TRANSACTIONAMOUNT,
                        TRANSACTIONCURRENCYID,
                        BASEEXCHANGERATEID
                from dbo.UFN_REVENUE_GENERATEDEFAULTGIFTFEESWITHTRANSACTIONAMOUNT_3(@REVENUEID,
                                                  @CONSTITUENTID,
                                                  null, --PAYMENTMETHOD

                                                  @TOTALAMOUNT,
                                                  @RECEIPTAMOUNT,
                                                  @BASECURRENCYID,
                                                  @TRANSACTIONCURRENCYID,
                                                  @BASEEXCHANGERATEID,
                                                  @ORGANIZATIONEXCHANGERATEID,
                                                  @SPLITSXML,
                                                  @ORGANIZATIONAMOUNT);

                return;
            end