USP_REVENUEBATCH_VALIDATEAPPLICATIONSOLICITORS

      CREATE procedure dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONSOLICITORS
      (
                @APPLICATIONSOLICITORS xml,
        @SPLITS xml,
        @APPLICATIONTYPECODE tinyint,
        @ADDITIONALAPPLICATIONS xml,
        @BASEEXCHANGERATEID uniqueidentifier = null
      )
      as
      begin
        declare @AMOUNT money
        declare @SOLICITORS xml

        declare @ADDITIONALAPPLICATIONTABLE table
        (
          TYPECODE tinyint,
          DESIGNATIONID uniqueidentifier,
          AMOUNT money
        )

        if not @APPLICATIONSOLICITORS is null
        begin
          if not @SPLITS is null
            insert into @ADDITIONALAPPLICATIONTABLE(TYPECODE, DESIGNATIONID, AMOUNT)
            select @APPLICATIONTYPECODE, T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
              T.c.value('(AMOUNT)[1]','money') AMOUNT
            from @SPLITS.nodes('/SPLITS/ITEM') T(c)
          else if not @ADDITIONALAPPLICATIONS is null
            insert into @ADDITIONALAPPLICATIONTABLE(TYPECODE, DESIGNATIONID, AMOUNT)
            select T.c.value('(TYPECODE)[1]','tinyint') TYPECODE, T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
              T.c.value('(APPLIED)[1]','money') AMOUNT
            from @ADDITIONALAPPLICATIONS.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)

          declare SOLICITORSCURSOR cursor local fast_forward for 
          select
            case when T.c.exist('./SOLICITORS/ITEM') = 1 then T.c.query('(SOLICITORS)[1]') else null end as SOLICITORS,
            case
                when @BASEEXCHANGERATEID is null then AAT.AMOUNT
                else dbo.UFN_CURRENCY_CONVERT(AAT.AMOUNT, @BASEEXCHANGERATEID)
            end as AMOUNT
          from @APPLICATIONSOLICITORS.nodes('/APPLICATIONSOLICITORS/ITEM') T(c)
          inner join @ADDITIONALAPPLICATIONTABLE AAT on AAT.DESIGNATIONID = T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier')
            and AAT.TYPECODE = T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint')

          open SOLICITORSCURSOR

          fetch next from SOLICITORSCURSOR into @SOLICITORS, @AMOUNT

          while @@FETCH_STATUS = 0
          begin
            if (select count(T.c.value('(AMOUNT)[1]','money')) from @SOLICITORS.nodes('/SOLICITORS/ITEM') T(c) where T.c.value('(AMOUNT)[1]','money') > @AMOUNT) > 0
                          raiserror('The amount credited to a single solicitor cannot be greater than the gift amount.', 13, 11);

            fetch next from SOLICITORSCURSOR into @SOLICITORS, @AMOUNT
          end

          close SOLICITORSCURSOR
          deallocate SOLICITORSCURSOR
        end
      end