UFN_REVENUEUPDATEBATCH_GETPAYMENTRECOGNITIONS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETPAYMENTRECOGNITIONS
(
    @BATCHREVENUEID uniqueidentifier,
    @USESPLITS bit
)
returns xml
as 
begin

    declare @APPLICATIONRECOGNITIONS xml
    declare @APPLICATIONRECOGNITIONSTABLE table
    (
        ADDITIONALAPPLICATIONTYPECODE tinyint,
        ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
        APPLICATIONID uniqueidentifier,
        ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
        ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier
    )

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

    if @USESPLITS = 1
        set @APPLICATIONRECOGNITIONS = (
            select 
                APPLICATIONID, 
                ADDITIONALAPPLICATIONTYPECODE, 
                ADDITIONALAPPLICATIONDESIGNATIONID, 
                BATCHREVENUESPLIT.AMOUNT as APPLICATIONAMOUNT,
                '' as APPLICATIONDESCRIPTION,
                dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
                (
                    select 
                        AMOUNT, 
                        CONSTITUENTID, 
                        EFFECTIVEDATE, 
                        BATCHREVENUERECOGNITION.ID, 
                        REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE, 
                        REVENUERECOGNITIONTYPECODEID, REVENUERECOGNITIONID
                    from dbo.BATCHREVENUERECOGNITION
                        left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
                    where BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONTYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
                        and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
                        and (BATCHREVENUERECOGNITION.APPLICATIONID = ART.APPLICATIONID or ART.APPLICATIONID is null)
                        and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
                        and coalesce(BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                        and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
                    order by BATCHREVENUERECOGNITION.EFFECTIVEDATE
                    for xml raw('ITEM'),type,elements,BINARY BASE64
                ) as RECOGNITIONS,
                ADDITIONALAPPLICATIONDECLINESGIFTAID,
                ADDITIONALAPPLICATIONSPONSORSHIPID
            from @APPLICATIONRECOGNITIONSTABLE ART
                inner join dbo.DESIGNATION on ART.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
                inner join dbo.BATCHREVENUESPLIT on 
                    BATCHREVENUESPLIT.DESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID 
                    and BATCHREVENUESPLIT.DECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID 
                    and (BATCHREVENUESPLIT.REVENUESPLITID = ART.APPLICATIONID  or ART.APPLICATIONID is null)
                    and BATCHREVENUEID = @BATCHREVENUEID 
   for xml raw('ITEM'),type,elements,root('APPLICATIONRECOGNITIONS'),BINARY BASE64
        )
    else
        set @APPLICATIONRECOGNITIONS = (
            select
                APPLICATIONID, 
                ADDITIONALAPPLICATIONTYPECODE, 
                ADDITIONALAPPLICATIONDESIGNATIONID, 
                APPLICATIONAMOUNT, 
                COLLECTIONDESCRIPTION, 
                RECOGNITIONS, 
                ADDITIONALAPPLICATIONDECLINESGIFTAID, 
                ADDITIONALAPPLICATIONSPONSORSHIPID 
            from (
                select
                    BATCHREVENUEADDITIONALAPPLICATIONS.REVENUESPLITID as APPLICATIONID, 
                    BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE as ADDITIONALAPPLICATIONTYPECODE, 
                    BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID as ADDITIONALAPPLICATIONDESIGNATIONID, 
                    BATCHREVENUEADDITIONALAPPLICATIONS.APPLIED as APPLICATIONAMOUNT,
                    '' as APPLICATIONDESCRIPTION,
                    coalesce(dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE, BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID, @BATCHREVENUEID, 1, BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID, BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID), '') as COLLECTIONDESCRIPTION,
                    (
                        select 
                            BATCHREVENUERECOGNITION.AMOUNT, 
                            BATCHREVENUERECOGNITION.CONSTITUENTID, 
                            BATCHREVENUERECOGNITION.EFFECTIVEDATE, 
                            BATCHREVENUERECOGNITION.ID, 
                            REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE, 
                            BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID, 
                            BATCHREVENUERECOGNITION.REVENUERECOGNITIONID,
                            BATCHREVENUERECOGNITION.RECOGNITIONCREDITFKID, 
                            BATCHREVENUERECOGNITION.RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID, 
                            coalesce(BATCHREVENUERECOGNITION.DONORCHALLENGERECOGNITIONTYPECODE, 0) as DONORCHALLENGERECOGNITIONTYPECODE
                        from dbo.BATCHREVENUERECOGNITION
                            left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
                        where BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONTYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
                          and (BATCHREVENUERECOGNITION.APPLICATIONID = ART.APPLICATIONID or ART.APPLICATIONID is null)
                          and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
                          and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
                          and coalesce(BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                          and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
                        order by BATCHREVENUERECOGNITION.EFFECTIVEDATE
                        for xml raw('ITEM'),type,elements,BINARY BASE64
                    ) as RECOGNITIONS,
                    BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID as ADDITIONALAPPLICATIONDECLINESGIFTAID,
                    BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID as ADDITIONALAPPLICATIONSPONSORSHIPID
                from BATCHREVENUEADDITIONALAPPLICATIONS
                    left join @APPLICATIONRECOGNITIONSTABLE ART on 
                        BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
                        and BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
                        and  (BATCHREVENUEADDITIONALAPPLICATIONS.REVENUESPLITID = ART.APPLICATIONID or ART.APPLICATIONID is null)
                        and BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
                        and coalesce(BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')  
                where
                    BATCHREVENUEID = @BATCHREVENUEID

            union all

                select APPLICATIONID, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, BATCHREVENUEAPPLICATION.APPLIED as APPLICATIONAMOUNT,
               '' as APPLICATIONDESCRIPTION,
               dbo.UFN_REVENUEUPDATEBATCH_GETCOLLECTIONDESCRIPTION(ART.APPLICATIONID, 1) as COLLECTIONDESCRIPTION,
               (select BATCHREVENUERECOGNITION.AMOUNT, 
                        BATCHREVENUERECOGNITION.CONSTITUENTID, 
                        BATCHREVENUERECOGNITION.EFFECTIVEDATE, 
                        BATCHREVENUERECOGNITION.ID, 
                        REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE, 
                        BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID, 
                        BATCHREVENUERECOGNITION.REVENUERECOGNITIONID,
                        BATCHREVENUERECOGNITION.RECOGNITIONCREDITFKID, 
                        BATCHREVENUERECOGNITION.RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID, 
                        coalesce(BATCHREVENUERECOGNITION.DONORCHALLENGERECOGNITIONTYPECODE, 0) as DONORCHALLENGERECOGNITIONTYPECODE
                from dbo.BATCHREVENUERECOGNITION
                left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
                where BATCHREVENUERECOGNITION.APPLICATIONID =  ART.APPLICATIONID
                  and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
                         order by BATCHREVENUERECOGNITION.EFFECTIVEDATE
                for xml raw('ITEM'),type,elements,BINARY BASE64) as RECOGNITIONS,
                ADDITIONALAPPLICATIONDECLINESGIFTAID,
           ADDITIONALAPPLICATIONSPONSORSHIPID
              from @APPLICATIONRECOGNITIONSTABLE ART
              --inner join dbo.DESIGNATION on ART.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID

              inner join dbo.BATCHREVENUEAPPLICATION on coalesce(BATCHREVENUEAPPLICATION.REVENUESPLITID, BATCHREVENUEAPPLICATION.REVENUEID, BATCHREVENUEAPPLICATION.REGISTRANTID) = ART.APPLICATIONID
                and BATCHREVENUEID = @BATCHREVENUEID                
                 ) as RECOGNITIONSSET                                      
              for xml raw('ITEM'),type,elements,root('APPLICATIONRECOGNITIONS'),BINARY BASE64)

    return @APPLICATIONRECOGNITIONS
end