UFN_REVENUEBATCH_GETMATCHINGGIFTCONDITIONS

            CREATE function dbo.UFN_REVENUEBATCH_GETMATCHINGGIFTCONDITIONS
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns @R table
            (
                RELATIONSHIPID uniqueidentifier,
                ORGANIZATIONID uniqueidentifier,
                MATCHINGGIFTCONDITIONID uniqueidentifier,
                MATCHINGFACTOR decimal(5,2),
                MINMATCHPERGIFT money,
                MAXMATCHPERGIFT money,
                MAXMATCHANNUAL money,
                MAXMATCHTOTAL money,
                MATCHTYPECODE tinyint,
                REVENUETYPECODE tinyint
            )
            as
            begin
                declare @RELATIONSHIPID uniqueidentifier;
                declare @ORGID uniqueidentifier;
                declare @EXISTINGORGID uniqueidentifier;
                declare @RELATIONSHIPTYPECODEID uniqueidentifier;
                declare @JOBSCHEDULECODEID uniqueidentifier;
                declare @CAREERLEVELCODEID uniqueidentifier;
                declare @ISBATCHRELATIONSHIP bit;

                declare @MATCHINGGIFTCONDITIONID uniqueidentifier;
                declare @MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier;
                declare @CONDITIONCOUNT int;
                declare @MATCHINGGIFTAMOUNT money;
                declare @MATCHINGGIFTMINAMOUNT money;
                declare @MATCHINGGIFTMAXAMOUNT money;
                declare @MATCHINGGIFTMAXMATCHANNUAL money;
                declare @MATCHINGGIFTMAXMATCHTOTAL money;
                declare @MATCHINGGIFTFACTOR decimal(30,2);
                declare @MATCHTYPECODE tinyint;
                declare @REVENUETYPECODE tinyint;

                declare CUR_MATCHINGGIFTRELATIONSHIPS cursor local fast_forward for
                    select    RELATIONSHIP.ID,                            --@RELATIONSHIPID

                            RELATIONSHIP.RELATIONSHIPTYPECODEID,        --@RELATIONSHIPTYPECODEID

                            RELATIONSHIP.RECIPROCALCONSTITUENTID,        --@ORGID

                            RELATIONSHIP.RECIPROCALCONSTITUENTID,        --@EXISTINGORGID

                            RELATIONSHIPJOBINFO.JOBSCHEDULECODEID,      --@JOBSCHEDULECODEID

                            RELATIONSHIPJOBINFO.CAREERLEVELCODEID,      --@CAREERLEVELCODEID

                            0                                            --@ISBATCHRELATIONSHIP

                    from dbo.RELATIONSHIP
                    left join dbo.RELATIONSHIPJOBINFO
                        on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
                        and ((RELATIONSHIPJOBINFO.STARTDATE is null or RELATIONSHIPJOBINFO.STARTDATE < getdate()) and
                            (RELATIONSHIPJOBINFO.ENDDATE is null or RELATIONSHIPJOBINFO.ENDDATE > getdate()))
                    where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID 
                        and ISMATCHINGGIFTRELATIONSHIP = 1
                        and ((RELATIONSHIP.STARTDATE is null or RELATIONSHIP.STARTDATE < getdate()) and
                            (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE > getdate()))
                    union
                    select    BATCHREVENUECONSTITUENTRELATION.ID,                        --@RELATIONSHIPID

                            BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID,    --@RELATIONSHIPTYPECODEID

                            BATCHREVENUECONSTITUENTRELATION.RELATIONID,                --@ORGID

                            [ORG].EXISTINGCONSTITUENTID,                            --@EXISTINGORGID

                            null,                                                    --@JOBSCHEDULECODEID

                            null,                                                    --@CAREERLEVELCODEID

                            1                                                        --@ISBATCHRELATIONSHIP

                    from dbo.BATCHREVENUECONSTITUENTRELATION
                    inner join dbo.BATCHREVENUECONSTITUENT as [ORG] on [ORG].ID = BATCHREVENUECONSTITUENTRELATION.RELATIONID
                    where CONSTITUENTID = @CONSTITUENTID
                        and ISMATCHINGGIFTRELATIONSHIP = 1;

                open CUR_MATCHINGGIFTRELATIONSHIPS;
                fetch next from CUR_MATCHINGGIFTRELATIONSHIPS into @RELATIONSHIPID, @RELATIONSHIPTYPECODEID, @ORGID, @EXISTINGORGID, @JOBSCHEDULECODEID, @CAREERLEVELCODEID, @ISBATCHRELATIONSHIP
                while @@FETCH_STATUS = 0
                begin

                    --check if there are any mg conditions for the current relationship

                    select @CONDITIONCOUNT = count(*) 
                    from dbo.MATCHINGGIFTCONDITION
                    inner join dbo.MATCHINGGIFTCONDITIONRELATIONSHIP
                    on MATCHINGGIFTCONDITION.ID = MATCHINGGIFTCONDITIONRELATIONSHIP.MATCHINGGIFTCONDITIONID
                    where ORGANIZATIONID = @EXISTINGORGID
                    and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
                    and (JOBSCHEDULECODEID is null or JOBSCHEDULECODEID = @JOBSCHEDULECODEID)
                    and (CAREERLEVELCODEID is null or CAREERLEVELCODEID = @CAREERLEVELCODEID);

                    if @CONDITIONCOUNT > 0 --matching gift conditions exist for the specific relationship type

                    begin

                        insert into @R(RELATIONSHIPID, ORGANIZATIONID, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHTYPECODE, REVENUETYPECODE)
                            select  
                                    @RELATIONSHIPID,
                                    @EXISTINGORGID,
                                    ID,
                                    MATCHINGFACTOR,
                                    MINMATCHPERGIFT,
                                    MAXMATCHPERGIFT,
                                    MAXMATCHANNUAL,
                                    MAXMATCHTOTAL,
                                    MATCHTYPECODE,
                                    REVENUETYPECODE
                            from (
                                select
                                    MATCHINGGIFTCONDITION.ID,
                                    MATCHINGGIFTCONDITION.MATCHINGFACTOR,
                                    MATCHINGGIFTCONDITION.MINMATCHPERGIFT,
                                    MATCHINGGIFTCONDITION.MAXMATCHPERGIFT,
                                    MATCHINGGIFTCONDITION.MAXMATCHANNUAL,
                                    MATCHINGGIFTCONDITION.MAXMATCHTOTAL,
                                    MATCHINGGIFTCONDITION.MATCHTYPECODE,
                                    MATCHINGGIFTCONDITION.REVENUETYPECODE,
                                    row_number() over(partition by MATCHINGGIFTCONDITION.REVENUETYPE, MATCHINGGIFTCONDITION.BASECURRENCYID order by MATCHINGGIFTCONDITION.SEQUENCE) as ROWNUM
                                from
                                    dbo.MATCHINGGIFTCONDITION
                                inner join dbo.MATCHINGGIFTCONDITIONRELATIONSHIP on MATCHINGGIFTCONDITIONRELATIONSHIP.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
                                where MATCHINGGIFTCONDITION.ORGANIZATIONID = @EXISTINGORGID
                                and MATCHINGGIFTCONDITIONRELATIONSHIP.RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
                                and (MATCHINGGIFTCONDITIONRELATIONSHIP.JOBSCHEDULECODEID is null or MATCHINGGIFTCONDITIONRELATIONSHIP.JOBSCHEDULECODEID = @JOBSCHEDULECODEID)
                                and (MATCHINGGIFTCONDITIONRELATIONSHIP.CAREERLEVELCODEID is null or MATCHINGGIFTCONDITIONRELATIONSHIP.CAREERLEVELCODEID = @CAREERLEVELCODEID)
                                ) as SUBQ
                            where ROWNUM = 1
                    end

                    fetch next from CUR_MATCHINGGIFTRELATIONSHIPS into @RELATIONSHIPID, @RELATIONSHIPTYPECODEID, @ORGID, @EXISTINGORGID, @JOBSCHEDULECODEID, @CAREERLEVELCODEID, @ISBATCHRELATIONSHIP
                end
                close CUR_MATCHINGGIFTRELATIONSHIPS;
                deallocate CUR_MATCHINGGIFTRELATIONSHIPS;

                return;
            end