USP_REVENUEBATCH_VALIDATEAPPLICATIONRECOGNITIONS

      CREATE procedure dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONRECOGNITIONS
      (
                @APPLICATIONRECOGNITIONS xml,
        @SPLITS xml,
        @APPLICATIONTYPECODE tinyint,
        @ADDITIONALAPPLICATIONS xml,
        @BATCHROWID uniqueidentifier = null
      )
      as
      begin
        declare @RECOGNITIONS xml
        declare @CONSTITUENTID uniqueidentifier
        declare @AMOUNT money

        declare @ADDITIONALAPPLICATIONTABLE table
        (
          TYPECODE tinyint,
          DESIGNATIONID uniqueidentifier
        )

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

          declare RECOGNITIONSCURSOR cursor local fast_forward for 
          select
            case when T.c.exist('./RECOGNITIONS/ITEM') = 1 then T.c.query('(RECOGNITIONS)[1]') else null end as RECOGNITIONS
          from @APPLICATIONRECOGNITIONS.nodes('/APPLICATIONRECOGNITIONS/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 RECOGNITIONSCURSOR

          fetch next from RECOGNITIONSCURSOR into @RECOGNITIONS

          while @@FETCH_STATUS = 0
          begin
            declare RECOGNITIONSCONSTITUENTCURSOR cursor local fast_forward for
            select T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as CONSTITUENTID,
            T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT'
            from @RECOGNITIONS.nodes('RECOGNITIONS/ITM') T(c)

            open RECOGNITIONSCONSTITUENTCURSOR
            fetch next from RECOGNITIONSCONSTITUENTCURSOR into @CONSTITUENTID,@AMOUNT
            while @@FETCH_STATUS = 0
            begin
                        if not exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
                            exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT @REVENUEBATCHCONSTITUENTID = @CONSTITUENTID, @ISDONOR = 0, @BATCHROWID = @BATCHROWID;

                --Validate that the amount is non-negative

                if @AMOUNT < 0
                  raiserror('BBERR_RECOGNITIONNEGATIVE',13,1)

              fetch next from RECOGNITIONSCONSTITUENTCURSOR into @CONSTITUENTID
            end

            close RECOGNITIONSCONSTITUENTCURSOR
            deallocate RECOGNITIONSCONSTITUENTCURSOR
            fetch next from RECOGNITIONSCURSOR into @RECOGNITIONS
          end

          close RECOGNITIONSCURSOR
          deallocate RECOGNITIONSCURSOR
        end
      end