UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSXML

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSXML
(
    @BATCHREVENUEID uniqueidentifier,
    @APPLICATIONRECOGNITIONS xml
)
returns xml
with execute as caller
as begin
    declare @APPLICATIONCODE tinyint
    declare @DESIGNATIONID uniqueidentifier
    declare @DECLINESGIFTAID bit
    declare @APPLICATIONID uniqueidentifier
    declare @REVENUESPLITID uniqueidentifier
    declare @RECOGNITIONS xml
   declare @SPONSORSHIPID uniqueidentifier
    declare @RECOGNITIONSTABLE table
    (
        APPLICATIONCODE tinyint,
        DESIGNATIONID uniqueidentifier,
        APPLICATIONID uniqueidentifier,
        RECOGNITIONS xml,
        DECLINESGIFTAID bit,
      SPONSORSHIPID uniqueidentifier
    )
    declare @RECOGNITION table
    (
         ID uniqueidentifier,
         CONSTITUENTID uniqueidentifier,
         AMOUNT money,
         EFFECTIVEDATE datetime,
         REVENUERECOGNITIONTYPECODEID uniqueidentifier,
         REVENUERECOGNITIONID uniqueidentifier,
         REVENUESPLITID uniqueidentifier,
         APPLICATIONCODE tinyint,
         DESIGNATIONID uniqueidentifier, 
         APPLICATIONID uniqueidentifier,
         DECLINESGIFTAID bit,
         RECOGNITIONCREDITFKID uniqueidentifier,
         DONORCHALLENGERECOGNITIONTYPECODE tinyint,
         RECOGNITIONCREDITDESIGNATIONID uniqueidentifier,
       SPONSORSHIPID uniqueidentifier
    ) 

    insert into @RECOGNITIONSTABLE(APPLICATIONCODE, DESIGNATIONID,APPLICATIONID, RECOGNITIONS, DECLINESGIFTAID, SPONSORSHIPID)
    select T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') as APPLICATIONCODE,
           T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID,
           T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
           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 DECLINESGIFTAID,
         T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
    from @APPLICATIONRECOGNITIONS.nodes('/APPLICATIONRECOGNITIONS/ITEM') T(c)

    declare RECOGNITIONSCURSOR cursor local fast_forward for
    select APPLICATIONCODE, RT.DESIGNATIONID, RT.APPLICATIONID, APPS.REVENUESPLITID, RECOGNITIONS, RT.DECLINESGIFTAID, RT.SPONSORSHIPID
    from @RECOGNITIONSTABLE RT
    left outer join dbo.BATCHREVENUEADDITIONALAPPLICATIONS APPS on 
        APPS.TYPECODE = RT.APPLICATIONCODE and 
        APPS.DESIGNATIONID = RT.DESIGNATIONID and 
        APPS.DECLINESGIFTAID = RT.DECLINESGIFTAID and 
      coalesce(APPS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(RT.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') and
        APPS.BATCHREVENUEID = @BATCHREVENUEID

    open RECOGNITIONSCURSOR

    fetch next from RECOGNITIONSCURSOR into @APPLICATIONCODE, @DESIGNATIONID, @APPLICATIONID, @REVENUESPLITID, @RECOGNITIONS, @DECLINESGIFTAID, @SPONSORSHIPID
    while @@FETCH_STATUS = 0
    begin
        insert into @RECOGNITION(ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, REVENUERECOGNITIONID, APPLICATIONCODE, DESIGNATIONID, APPLICATIONID, REVENUESPLITID, DECLINESGIFTAID, RECOGNITIONCREDITFKID, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITDESIGNATIONID, SPONSORSHIPID)
        select T.c.value('(ID)[1]','uniqueidentifier') as 'ID', 
        T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
        T.c.value('(AMOUNT)[1]','money') as 'AMOUNT',
        T.c.value('(EFFECTIVEDATE)[1]','datetime') as 'EFFECTIVEDATE',
        T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier') as 'REVENUERECOGNITIONTYPECODEID',
        T.c.value('(REVENUERECOGNITIONID)[1]','uniqueidentifier') as 'REVENUERECOGNITIONID',
        @APPLICATIONCODE as APPLICATIONCODE,
        @DESIGNATIONID as DESIGNATIONID,
        @APPLICATIONID as APPLICATIONID,
        @REVENUESPLITID as REVENUESPLITID,
        @DECLINESGIFTAID as DECLINESGIFTAID,
        T.c.value('(RECOGNITIONCREDITFKID)[1]','uniqueidentifier') as RECOGNITIONCREDITFKID,
        coalesce(T.c.value('(DONORCHALLENGERECOGNITIONTYPECODE)[1]','tinyint'), 0) as DONORCHALLENGERECOGNITIONTYPECODE,
        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') as RECOGNITIONCREDITDESIGNATIONID,
      @SPONSORSHIPID as SPONSORSHIPID
        from @RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c);

        fetch next from RECOGNITIONSCURSOR into @APPLICATIONCODE, @DESIGNATIONID, @APPLICATIONID, @REVENUESPLITID, @RECOGNITIONS, @DECLINESGIFTAID, @SPONSORSHIPID
    end

   close RECOGNITIONSCURSOR    
    deallocate RECOGNITIONSCURSOR

    set @RECOGNITIONS =
        (
      select ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, 
               REVENUERECOGNITIONID, APPLICATIONCODE, DESIGNATIONID, APPLICATIONID, REVENUESPLITID,
               DECLINESGIFTAID, 
                 RECOGNITIONCREDITFKID, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITDESIGNATIONID,
             SPONSORSHIPID
      from @RECOGNITION
      for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
    );

    return @RECOGNITIONS
end