UFN_STANDINGORDERSIMPORTPROCESS_GETCOMMITMENTS

            CREATE function dbo.UFN_STANDINGORDERSIMPORTPROCESS_GETCOMMITMENTS
            (
                @CONSTITUENTID uniqueidentifier,
                @REVENUEID uniqueidentifier,
                @APPLICATIONAMOUNT money
            )
            returns @retval table
            (
                ID uniqueidentifier,
                FORMID uniqueidentifier,
                APPLICATIONCODE tinyint,
                APPLIED money
            )
            as
            begin

                    declare @constits as table(ID uniqueidentifier);

                    --Get all relevant constits

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());            

                    declare @HOUSEHOLDSCANBEDONORS bit;
                    set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

                    declare @HOUSEHOLDID uniqueidentifier;
                    select top(1) @HOUSEHOLDID = GM.GROUPID
                    from dbo.GROUPMEMBER as GM
                    left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
                    where GM.MEMBERID = @CONSTITUENTID
                    and GD.GROUPTYPECODE = 0
                    and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
                    and @HOUSEHOLDSCANBEDONORS = 1;        

                    insert into @constits(ID)
                        select 
                            @HOUSEHOLDID as ID
                        union
                        select 
                            case when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 then 
                                (case when @HOUSEHOLDSCANBEDONORS = 1 then @CONSTITUENTID else null end)
                                else @CONSTITUENTID
                            end
                        union
                        select 
                            GM.MEMBERID
                        from 
                            dbo.GROUPMEMBER GM
                        left outer join 
                            dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                        where 
                        (    GM.GROUPID = @HOUSEHOLDID
                        or
                            GM.GROUPID = @CONSTITUENTID
                        )
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))

                --Pledge

                insert into @retval(ID, FORMID, APPLICATIONCODE)
                    select
                        REVENUE.ID, 
                        '6C4958A9-7A6C-4C23-A1BB-8EA0CFA267B4',
                        2
                    from dbo.REVENUE                
                    inner join @constits C on C.ID = REVENUE.CONSTITUENTID
                    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                    where REVENUE.TRANSACTIONTYPECODE = 1 and
                        dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
                        and (--Check site security

                                select count(*) 
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
                                /*#SITEEXTENSION*/
                            ) > 0

                --RG

                insert into @retval(ID, FORMID, APPLICATIONCODE)
                    select
                        REVENUE.ID, 
                        '84D79558-F2BC-449C-A806-EA29AC9B4983',
                        3
                    from dbo.REVENUE                
                    inner join @constits C on C.ID = REVENUE.CONSTITUENTID
                    inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                    where REVENUE.TRANSACTIONTYPECODE = 2        --Recurring Gift

                        and REVENUESCHEDULE.STATUSCODE = 0        --Active

                        and REVENUESCHEDULE.ISPENDING = 0        --Isn't pending

                        and REVENUE.AMOUNT > 0                    --Has Value

                        and (--Check site security

                                select count(*) 
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
                                /*#SITEEXTENSION*/
                            ) > 0

                update @retval
                    set APPLIED = (
                                    case 
                                        when (ID = @REVENUEID) then @APPLICATIONAMOUNT
                                        else 0
                                    end
                                  );
                return;
            end