USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS

      CREATE procedure dbo.USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS
      (
        @ADDITIONALAPPLICATIONSSTREAM xml,
        @REVENUEBATCHTYPECODE tinyint,
        @PAYMENTMETHODCODE tinyint,
        @CATEGORYCODEID uniqueidentifier,
        @ISORGANIZATION bit
      )
      as
            set nocount on;

            if @ADDITIONALAPPLICATIONSSTREAM is null
                return 0;

            declare @APPLICATIONSTABLE table
            (
                TYPECODE tinyint,
                APPLIED money,
                DESIGNATIONID uniqueidentifier,
                OTHERTYPECODEID uniqueidentifier,
                DECLINESGIFTAID bit,
                SPONSORSHIPID uniqueidentifier
            );

            insert into @APPLICATIONSTABLE(TYPECODE, APPLIED, DESIGNATIONID, OTHERTYPECODEID, DECLINESGIFTAID,SPONSORSHIPID)
                select
                    TYPECODE,
                    APPLIED,
                    DESIGNATIONID,
                    OTHERTYPECODEID,
                    DECLINESGIFTAID,
                    SPONSORSHIPID
                from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM)

            declare STREAMSCURSOR cursor local fast_forward for
                select TYPECODE, APPLIED, DESIGNATIONID, OTHERTYPECODEID from @APPLICATIONSTABLE;

            declare @TYPECODE tinyint;
            declare @APPLIED money;
            declare @DESIGNATIONID uniqueidentifier;
            declare @OTHERTYPECODEID uniqueidentifier;

            open STREAMSCURSOR;
            fetch next from STREAMSCURSOR into @TYPECODE, @APPLIED, @DESIGNATIONID, @OTHERTYPECODEID;

            while @@FETCH_STATUS = 0
            begin                
                --If an amount has been applied to a commitment, validate the GL mappings.

                if @APPLIED > 0 
                begin

          if @TYPECODE is null
          begin
            raiserror('Application type is required.', 13, 2);
            return 2;
          end

          if @TYPECODE = 1
          begin
            if @OTHERTYPECODEID is null
            begin
              raiserror('Other type is required if the application is Other.', 13, 2);
              return 2;
            end
          end
          else if @TYPECODE = 2
            if @ISORGANIZATION = 0
            begin
              raiserror('Only organizations can add unapplied matching gift payments.', 13, 2);
              return 2;
            end

          if @DESIGNATIONID is null
          begin
            raiserror('Please enter at least one designation.', 13, 2);
            return 2;
          end

          if exists(select top 1 ID from dbo.designation where ID=@DESIGNATIONID and ISACTIVE=0) 
          begin
            raiserror('BBERR_CANNOTPAYINACTIVEDESIGNATIONS : Revenue cannot be added to inactive designations.', 13, 2);
            return 2;
          end

                    exec dbo.USP_DESIGNATION_VALIDATEGLMAPPINGS @DESIGNATIONID, @REVENUEBATCHTYPECODE, @PAYMENTMETHODCODE, 0, 0, @CATEGORYCODEID;          

        end

                fetch next from STREAMSCURSOR into @TYPECODE, @APPLIED, @DESIGNATIONID, @OTHERTYPECODEID;
            end

            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

            close STREAMSCURSOR;
            deallocate STREAMSCURSOR;

      if (select count(distinct cast(DECLINESGIFTAID as nvarchar(1)) + cast(TYPECODE as nvarchar(1)) + cast(DESIGNATIONID as nvarchar(36)) + cast(isnull(SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') as nvarchar(36))) from @APPLICATIONSTABLE) <
        (select count(cast(DECLINESGIFTAID as nvarchar(1)) + cast(TYPECODE as nvarchar(1)) + cast(DESIGNATIONID as nvarchar(36)) + cast(isnull(SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') as nvarchar(36))) from @APPLICATIONSTABLE)
        begin
          raiserror('Duplicate designations cannot be specified for the same application type.', 13, 2);
          return 2;
        end

            return 0;