USP_REVENUEBATCH_ADDPAYMENTRECOGNITIONSANDSOLICITORS

      CREATE procedure dbo.USP_REVENUEBATCH_ADDPAYMENTRECOGNITIONSANDSOLICITORS
      (
        @BATCHREVENUEID uniqueidentifier,
        @APPLICATIONRECOGNITIONS xml,
        @APPLICATIONSOLICITORS xml,
        @REVENUESTREAMS xml,
        @CHANGEAGENTID uniqueidentifier,
        @CURRENTDATE datetime
      )
      as
      begin
        declare @APPLICATIONID uniqueidentifier
        declare @ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier
        declare @RECOGNITIONS xml
        declare @SOLICITORS xml

        declare @APPLICATIONTABLE table
        (        
          APPLICATIONID uniqueidentifier
        )

        if @REVENUESTREAMS is not null
          insert into @APPLICATIONTABLE(APPLICATIONID)
          select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as APPLICATIONID
          from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)

          insert into @APPLICATIONTABLE(APPLICATIONID)
          select T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID
          from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c) 
          where T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') is null

        if @APPLICATIONRECOGNITIONS is not null
        begin
          declare RECOGNITIONSCURSOR cursor local fast_forward for 
          select
            T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
            T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as ADDITIONALAPPLICATIONDESIGNATIONID,
            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 @APPLICATIONTABLE AAT on AAT.APPLICATIONID = T.c.value('(APPLICATIONID)[1]','uniqueidentifier')
          and AAT.APPLICATIONID is not null

          open RECOGNITIONSCURSOR

          fetch next from RECOGNITIONSCURSOR into @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONS

          while @@FETCH_STATUS = 0
          begin
            insert into dbo.BATCHREVENUERECOGNITION(BATCHREVENUEID, AMOUNT, CONSTITUENTID, EFFECTIVEDATE, ID, REVENUERECOGNITIONTYPECODEID,
                                                  APPLICATIONID, ADDITIONALAPPLICATIONDESIGNATIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, REVENUERECOGNITIONID,
                                                RECOGNITIONCREDITFKID, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITDESIGNATIONID)
            select @BATCHREVENUEID, T.c.value('(AMOUNT)[1]','money'), T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'), T.c.value('(EFFECTIVEDATE)[1]','datetime'), newid() ID, T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier'), 
                    @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, T.c.value('(REVENUERECOGNITIONID)[1]','uniqueidentifier'),
                    T.c.value('(RECOGNITIONCREDITFKID)[1]','uniqueidentifier'),
                    coalesce(T.c.value('(DONORCHALLENGERECOGNITIONTYPECODE)[1]','tinyint'), 0),
                    T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') 
                    from @RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c)
                    where  T.c.value('(AMOUNT)[1]','money') <> 0.0
                    or (
                    T.c.value('(AMOUNT)[1]', 'money') = 0.0
                    and not exists (
                        select 1
                        from dbo.BATCHREVENUERECOGNITION
                        where
                                CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier')
                                and AMOUNT <> 0.00
                     )
                    );

            fetch next from RECOGNITIONSCURSOR into @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONS
          end

          close RECOGNITIONSCURSOR
          deallocate RECOGNITIONSCURSOR
        end   

        if @APPLICATIONSOLICITORS is not null
        begin
          declare SOLICITORSCURSOR cursor local fast_forward for 
          select
            T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
            T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as ADDITIONALAPPLICATIONDESIGNATIONID,
            case when T.c.exist('./SOLICITORS/ITEM') = 1 then T.c.query('(SOLICITORS)[1]') else null end as SOLICITORS
          from @APPLICATIONSOLICITORS.nodes('/APPLICATIONSOLICITORS/ITEM') T(c)
          inner join @APPLICATIONTABLE AAT on AAT.APPLICATIONID = T.c.value('(APPLICATIONID)[1]','uniqueidentifier')
          and AAT.APPLICATIONID is not null

          open SOLICITORSCURSOR

          fetch next from SOLICITORSCURSOR into @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @SOLICITORS

          while @@FETCH_STATUS = 0
          begin
            insert into dbo.BATCHREVENUESOLICITOR(BATCHREVENUEID, AMOUNT, CONSTITUENTID, ID, APPLICATIONID, ADDITIONALAPPLICATIONDESIGNATIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, REVENUESOLICITORID)
            select @BATCHREVENUEID, T.c.value('(AMOUNT)[1]','money'), T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'), newid() ID, @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, T.c.value('(REVENUESOLICITORID)[1]','uniqueidentifier') 
            from @SOLICITORS.nodes('/SOLICITORS/ITEM') T(c)

            fetch next from SOLICITORSCURSOR into @APPLICATIONID, @ADDITIONALAPPLICATIONDESIGNATIONID, @SOLICITORS
          end

          close SOLICITORSCURSOR
          deallocate SOLICITORSCURSOR
        end   

        return 0;
      end