UFN_REVENUEUPDATEBATCH_GETPAYMENTSOLICITORS

      CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETPAYMENTSOLICITORS
      (
        @BATCHREVENUEID uniqueidentifier,
        @USESPLITS bit
      )
      returns xml
      as 
      begin
        declare @APPLICATIONSOLICITORS xml
        declare @APPLICATIONSOLICITORSTABLE table
        (
          ADDITIONALAPPLICATIONTYPECODE tinyint,
          ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
          APPLICATIONID uniqueidentifier,
          ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
        ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier
        )
        declare @MAINOPPORTUNITYID uniqueidentifier

        insert into @APPLICATIONSOLICITORSTABLE(ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID)
        select distinct ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID
        from dbo.BATCHREVENUESOLICITOR
        where BATCHREVENUEID = @BATCHREVENUEID

        select @MAINOPPORTUNITYID = OPPORTUNITYID from dbo.BATCHREVENUE where BATCHREVENUE.ID = @BATCHREVENUEID

        if @USESPLITS = 1
          set @APPLICATIONSOLICITORS = (select ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, BATCHREVENUESPLIT.AMOUNT as APPLICATIONAMOUNT,
             '' as APPLICATIONDESCRIPTION,
             dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(AST.ADDITIONALAPPLICATIONTYPECODE, AST.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 0, AST.ADDITIONALAPPLICATIONDECLINESGIFTAID, AST.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
             (select AMOUNT, CONSTITUENTID, ID, SEQUENCE, REVENUESOLICITORID
              from dbo.BATCHREVENUESOLICITOR
              where BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONTYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
                and (BATCHREVENUESOLICITOR.APPLICATIONID = AST.APPLICATIONID or AST.APPLICATIONID is null)
                and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
                and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
                and coalesce(BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                and BATCHREVENUESOLICITOR.BATCHREVENUEID = @BATCHREVENUEID
              for xml raw('ITEM'),type,elements,BINARY BASE64) as SOLICITORS,
              @MAINOPPORTUNITYID as ADDITIONALAPPLICATIONOPPORTUNITYID,
           ADDITIONALAPPLICATIONDECLINESGIFTAID,
           ADDITIONALAPPLICATIONSPONSORSHIPID
            from @APPLICATIONSOLICITORSTABLE AST
            inner join dbo.DESIGNATION on AST.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
            inner join dbo.BATCHREVENUESPLIT on 
        BATCHREVENUESPLIT.DESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
        and (BATCHREVENUESPLIT.REVENUESPLITID = AST.APPLICATIONID or AST.APPLICATIONID is null)
        and BATCHREVENUESPLIT.DECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
        and BATCHREVENUEID = @BATCHREVENUEID 
            for xml raw('ITEM'),type,elements,root('APPLICATIONSOLICITORS'),BINARY BASE64)
        else
          set @APPLICATIONSOLICITORS = (select APPLICATIONID, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, COLLECTIONDESCRIPTION, SOLICITORS, ADDITIONALAPPLICATIONOPPORTUNITYID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID
        from          
            (select APPLICATIONID, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, BATCHREVENUEADDITIONALAPPLICATIONS.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
             dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(AST.ADDITIONALAPPLICATIONTYPECODE, AST.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 0, AST.ADDITIONALAPPLICATIONDECLINESGIFTAID, AST.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
             (select AMOUNT, CONSTITUENTID, ID, SEQUENCE, REVENUESOLICITORID
              from dbo.BATCHREVENUESOLICITOR
              where BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONTYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
                and (BATCHREVENUESOLICITOR.APPLICATIONID = AST.APPLICATIONID or AST.APPLICATIONID is null)
                and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
         and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
            and coalesce(BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                and BATCHREVENUESOLICITOR.BATCHREVENUEID = @BATCHREVENUEID
              for xml raw('ITEM'),type,elements,BINARY BASE64) as SOLICITORS,
              BATCHREVENUEADDITIONALAPPLICATIONS.OPPORTUNITYID as ADDITIONALAPPLICATIONOPPORTUNITYID,
                 ADDITIONALAPPLICATIONDECLINESGIFTAID,
              ADDITIONALAPPLICATIONSPONSORSHIPID
            from @APPLICATIONSOLICITORSTABLE AST
            inner join dbo.DESIGNATION on AST.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
            inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS on 
                BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
        and (BATCHREVENUEADDITIONALAPPLICATIONS.REVENUESPLITID = AST.APPLICATIONID or AST.APPLICATIONID  is null)
                and BATCHREVENUEID = @BATCHREVENUEID 
                and BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
                and BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
            and coalesce(BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')

            union all

            select APPLICATIONID, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, BATCHREVENUEAPPLICATION.APPLIED as APPLICATIONAMOUNT,
             '' as APPLICATIONDESCRIPTION,
             dbo.UFN_REVENUEUPDATEBATCH_GETCOLLECTIONDESCRIPTION(AST.APPLICATIONID, 0) as COLLECTIONDESCRIPTION,
             (select AMOUNT, CONSTITUENTID, ID, SEQUENCE, REVENUESOLICITORID
              from dbo.BATCHREVENUESOLICITOR
              where BATCHREVENUESOLICITOR.APPLICATIONID = AST.APPLICATIONID
                and BATCHREVENUESOLICITOR.BATCHREVENUEID = @BATCHREVENUEID
              for xml raw('ITEM'),type,elements,BINARY BASE64) as SOLICITORS,
              null as ADDITIONALAPPLICATIONOPPORTUNITYID,
              ADDITIONALAPPLICATIONDECLINESGIFTAID,
           ADDITIONALAPPLICATIONSPONSORSHIPID
            from @APPLICATIONSOLICITORSTABLE AST
            inner join dbo.BATCHREVENUEAPPLICATION on coalesce(BATCHREVENUEAPPLICATION.REVENUESPLITID, BATCHREVENUEAPPLICATION.REVENUEID) = AST.APPLICATIONID
              and BATCHREVENUEID = @BATCHREVENUEID  ) as SOLICITORSSET
            for xml raw('ITEM'),type,elements,root('APPLICATIONSOLICITORS'),BINARY BASE64)                


        return @APPLICATIONSOLICITORS
      end