USP_REVENUEBATCH_DEFAULTAPPLICATIONRECOGNITIONS_2

create procedure dbo.USP_REVENUEBATCH_DEFAULTAPPLICATIONRECOGNITIONS_2
(
    @APPLICATIONRECOGNITIONS xml output,
    @ADDITIONALAPPLICATIONSSTREAM xml,
    @GIVENANONYMOUSLY bit, 
    @CONSTITUENTID uniqueidentifier, 
    @DATE datetime,
    @BASECURRENCYID uniqueidentifier,
    @BASEEXCHANGERATEID uniqueidentifier,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @BASECURRENCYDECIMALDIGITS tinyint,
    @BASECURRENCYROUNDINGTYPECODE tinyint,
    @EXCHANGERATE decimal(20,8),
    @BATCHROWID uniqueidentifier
)
as
begin

    declare @TEMPAPPLICATIONRECOGNITIONS table
      (
        APPLICATIONID uniqueidentifier,
        APPLICATIONTYPECODE tinyint,
        ADDITIONALAPPLICATIONTYPECODE tinyint,
        ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
        APPLICATIONAMOUNT money,
        RECOGNITIONS xml,
        ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
        ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier
      );

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

      --JamesWill WI166948 2011-07-20 Since @TEMPADDITIONALAPPLICATIONS is used to default recognitions (which should be in base), convert the transaction 

      --currency from @ADDITIONALAPPLICATIONSSTREAM into base before creating the recognitions

      with [TRANSACTIONAPPLICATIONS] as 
      (
        select 
          T.c.value('(ID)[1]', 'uniqueidentifier') ID,
          T.c.value('(TYPECODE)[1]','tinyint') TYPECODE, 
          T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
          T.c.value('(APPLIED)[1]','money') APPLIED,
          T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
          T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
        from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)
      )
      insert into @TEMPADDITIONALAPPLICATIONS(ID,TYPECODE,DESIGNATIONID,APPLIED,DECLINESGIFTAID,SPONSORSHIPID)
        select
          ID,
          TYPECODE,
          DESIGNATIONID,
          case
            when @BASECURRENCYID = @TRANSACTIONCURRENCYID then
              APPLIED
            when @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' then
              dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPLIED, @EXCHANGERATE), @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
            else 
              dbo.UFN_CURRENCY_CONVERT(APPLIED, @BASEEXCHANGERATEID)
          end APPLIED,
          DECLINESGIFTAID,
          SPONSORSHIPID
        from [TRANSACTIONAPPLICATIONS]

     -- WI# 453232 Patton Hilliard 1/14/15

     -- This is the same validation used in USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS

     -- The USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS is not used in import validation, so a split with the same designation can be created

     -- Validating here causes the import to throw an exception indicating matching designations within a split

         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 @TEMPADDITIONALAPPLICATIONS) <
        (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 @TEMPADDITIONALAPPLICATIONS)
        begin
          raiserror('Duplicate designations cannot be specified for the same application type.', 13, 2);
          return 2;
        end

      declare @RECOGNITIONSDEFAULT_APPLICATIONID uniqueidentifier
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE tinyint
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier
      declare @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT money
      declare @RECOGNITIONSDEFAULT_APPLICATIONDESCRIPTION nvarchar
      declare @RECOGNITIONSDEFAULT_COLLECTIONDESCRIPTION nvarchar
      declare @RECOGNITIONSDEFAULT_RECOGNITIONS xml
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID bit
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier

      declare @TEMPRECOGNITIONS xml;

      declare ADDITIONALAPPLICATIONS_CURSOR cursor local fast_forward for 
        select ID, TYPECODE, DESIGNATIONID, APPLIED, DECLINESGIFTAID,SPONSORSHIPID from @TEMPADDITIONALAPPLICATIONS

      open ADDITIONALAPPLICATIONS_CURSOR;
      fetch next from ADDITIONALAPPLICATIONS_CURSOR into @RECOGNITIONSDEFAULT_APPLICATIONID, 
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, 
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, 
                                                         @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT,
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID,
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID;
      while @@FETCH_STATUS = 0
      begin
        select @TEMPRECOGNITIONS =
        (
          select CONSTITUENTID, REVENUERECOGNITIONTYPECODEID, AMOUNT, @DATE as [EFFECTIVEDATE]
          from UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_3(@GIVENANONYMOUSLY, @CONSTITUENTID, @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT, @DATE, null,@RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE,@RECOGNITIONSDEFAULT_APPLICATIONID,@BATCHROWID) where AMOUNT > 0
          for xml raw('ITEM'), type, elements,  binary base64
        );

        insert into @TEMPAPPLICATIONRECOGNITIONS(APPLICATIONID, APPLICATIONTYPECODE, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, RECOGNITIONS, ADDITIONALAPPLICATIONDECLINESGIFTAID,ADDITIONALAPPLICATIONSPONSORSHIPID)
        values(@RECOGNITIONSDEFAULT_APPLICATIONID, null, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT, @TEMPRECOGNITIONS, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID);

        fetch next from ADDITIONALAPPLICATIONS_CURSOR into @RECOGNITIONSDEFAULT_APPLICATIONID, 
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, 
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, 
                                                           @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT,
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID,
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID;
      end

      close ADDITIONALAPPLICATIONS_CURSOR;
      deallocate ADDITIONALAPPLICATIONS_CURSOR;

      set @APPLICATIONRECOGNITIONS = 
      (
         select APPLICATIONID, APPLICATIONTYPECODE, ADDITIONALAPPLICATIONTYPECODE,
                ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, RECOGNITIONS,
                ADDITIONALAPPLICATIONDECLINESGIFTAID,ADDITIONALAPPLICATIONSPONSORSHIPID
         from @TEMPAPPLICATIONRECOGNITIONS
         for xml raw('ITEM'), type, elements, root('APPLICATIONRECOGNITIONS'), binary base64
      );

end