USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS

CREATE procedure dbo.USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS
(
  @BATCHREVENUEID uniqueidentifier,
  @APPLICATIONRECOGNITIONS xml,
  @APPLICATIONSOLICITORS xml,
  @SPLITS xml,
  @APPLICATIONTYPECODE tinyint,
  @ADDITIONALAPPLICATIONS xml,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime
)
as
begin

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  if @CURRENTDATE is null
    set @CURRENTDATE = getdate();

  declare @ADDITIONALAPPLICATIONTABLE table
  (
    TYPECODE tinyint,
    DESIGNATIONID uniqueidentifier,
    DECLINESGIFTAID bit,
    SPONSORSHIPID uniqueidentifier
  );

  if @SPLITS is not null
  begin

    insert into @ADDITIONALAPPLICATIONTABLE(TYPECODE, DESIGNATIONID, DECLINESGIFTAID, SPONSORSHIPID)
    select
      @APPLICATIONTYPECODE, 
      T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID, 
      T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
      T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
    from
      @SPLITS.nodes('/SPLITS/ITEM') T(c);

  end
  else if @ADDITIONALAPPLICATIONS is not null
  begin

    insert into @ADDITIONALAPPLICATIONTABLE(TYPECODE, DESIGNATIONID, DECLINESGIFTAID, SPONSORSHIPID)
    select 
      T.c.value('(TYPECODE)[1]','tinyint') TYPECODE,
      T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
      T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
      T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
    from
      @ADDITIONALAPPLICATIONS.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c);

  end

  declare @ADDITIONALAPPLICATIONTYPECODE tinyint;
  declare @ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier;
  declare @ADDITIONALAPPLICATIONDECLINESGIFTAID bit;
  declare @ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier;
  declare @RECOGNITIONS xml;
  declare @SOLICITORS xml;

  if @APPLICATIONRECOGNITIONS is not null
  begin

    declare RECOGNITIONSCURSOR cursor local fast_forward for 
    select
      T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]', 'tinyint') as ADDITIONALAPPLICATIONTYPECODE,
      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,
      T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]', 'bit') as ADDITIONALAPPLICATIONDECLINESGIFTAID,
      case
        when T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
          null
        else
          T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier')
      end as ADDITIONALAPPLICATIONSPONSORSHIPID
    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')
        and AAT.DECLINESGIFTAID = T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]', 'bit')
        and coalesce(AAT.SPONSORSHIPID, '00000000-0000-0000-0000-000000000000') = coalesce(T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000');

    open RECOGNITIONSCURSOR;
    fetch next from RECOGNITIONSCURSOR into @ADDITIONALAPPLICATIONTYPECODE, @ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONS, 
      @ADDITIONALAPPLICATIONDECLINESGIFTAID, @ADDITIONALAPPLICATIONSPONSORSHIPID;

    while @@FETCH_STATUS = 0
    begin

      insert into dbo.BATCHREVENUERECOGNITION
      (
        BATCHREVENUEID,
        AMOUNT,
        CONSTITUENTID,
        EFFECTIVEDATE,
        ID,
    REVENUERECOGNITIONTYPECODEID,
        ADDITIONALAPPLICATIONTYPECODE,
        ADDITIONALAPPLICATIONDESIGNATIONID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED, 
        REVENUERECOGNITIONID,
        ADDITIONALAPPLICATIONDECLINESGIFTAID,
        ADDITIONALAPPLICATIONSPONSORSHIPID
      )
      select
        @BATCHREVENUEID,
        T.c.value('(AMOUNT)[1]', 'money'),
        T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
        T.c.value('(EFFECTIVEDATE)[1]', 'datetime'),
        coalesce(nullif(T.c.value('(ID)[1]', 'uniqueidentifier'),'00000000-0000-0000-0000-000000000000'), newid()) ID,
        T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]', 'uniqueidentifier'), 
        @ADDITIONALAPPLICATIONTYPECODE,
        @ADDITIONALAPPLICATIONDESIGNATIONID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        T.c.value('(REVENUERECOGNITIONID)[1]', 'uniqueidentifier'),
        coalesce(@ADDITIONALAPPLICATIONDECLINESGIFTAID, 0),
        @ADDITIONALAPPLICATIONSPONSORSHIPID
        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 @ADDITIONALAPPLICATIONTYPECODE, @ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONS,
        @ADDITIONALAPPLICATIONDECLINESGIFTAID, @ADDITIONALAPPLICATIONSPONSORSHIPID;

    end

    close RECOGNITIONSCURSOR;
    deallocate RECOGNITIONSCURSOR;
  end

  if @APPLICATIONSOLICITORS is not null
  begin

    declare SOLICITORSCURSOR cursor local fast_forward for
    select
      T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]', 'tinyint') as ADDITIONALAPPLICATIONTYPECODE,
      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,
      T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]', 'bit') as ADDITIONALAPPLICATIONDECLINESGIFTAID,
      case
        when T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
          null
        else
          T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier') end as ADDITIONALAPPLICATIONSPONSORSHIPID
    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')
      and AAT.DECLINESGIFTAID = T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]', 'bit')    
      and coalesce(AAT.SPONSORSHIPID, '00000000-0000-0000-0000-000000000000') = coalesce(T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000');

    open SOLICITORSCURSOR

    fetch next from SOLICITORSCURSOR into @ADDITIONALAPPLICATIONTYPECODE, @ADDITIONALAPPLICATIONDESIGNATIONID, @SOLICITORS,
      @ADDITIONALAPPLICATIONDECLINESGIFTAID,@ADDITIONALAPPLICATIONSPONSORSHIPID;

    while @@FETCH_STATUS = 0
    begin
      insert into dbo.BATCHREVENUESOLICITOR
      (
        BATCHREVENUEID,
        AMOUNT,
        CONSTITUENTID,
        ID,
        ADDITIONALAPPLICATIONTYPECODE,
        ADDITIONALAPPLICATIONDESIGNATIONID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED,
        REVENUESOLICITORID,
            ADDITIONALAPPLICATIONDECLINESGIFTAID,
        ADDITIONALAPPLICATIONSPONSORSHIPID
      )
      select
        @BATCHREVENUEID,
        T.c.value('(AMOUNT)[1]', 'money'),
        T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
        coalesce(nullif(T.c.value('(ID)[1]', 'uniqueidentifier'),'00000000-0000-0000-0000-000000000000'), newid()) ID,
        @ADDITIONALAPPLICATIONTYPECODE,
        @ADDITIONALAPPLICATIONDESIGNATIONID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        T.c.value('(REVENUESOLICITORID)[1]', 'uniqueidentifier'),
        coalesce(@ADDITIONALAPPLICATIONDECLINESGIFTAID, 0),
        @ADDITIONALAPPLICATIONSPONSORSHIPID
      from
        @SOLICITORS.nodes('/SOLICITORS/ITEM') T(c);

      fetch next from SOLICITORSCURSOR into @ADDITIONALAPPLICATIONTYPECODE, @ADDITIONALAPPLICATIONDESIGNATIONID, @SOLICITORS,
        @ADDITIONALAPPLICATIONDECLINESGIFTAID, @ADDITIONALAPPLICATIONSPONSORSHIPID;
    end 

    close SOLICITORSCURSOR;
    deallocate SOLICITORSCURSOR;

  end

  return 0;
end