USP_DATALIST_BATCHREVENUEAPPLICATIONMEMBERSHIP

                CREATE procedure dbo.USP_DATALIST_BATCHREVENUEAPPLICATIONMEMBERSHIP
                (
                    @CONSTITUENTID uniqueidentifier = null    
                )
                as
                set nocount on;

                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;                

                -- include (1) the constituent, (2) the household a constituent is a member of, (3) members of that household, (4) members of the constituent if it is a household

                with CONSTITUENTCTE as
                (
                    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))
                )
                select  --only pull primary members, due for renewal

                    MEMBERSHIP.ID,
                    CONSTITUENT.ID,
                    CONSTITUENT.NAME,
                    0,
                    MEMBERSHIPLEVELTERM.AMOUNT,
                    MEMBERSHIPLEVELTERM.AMOUNT,
                    MEMBERSHIP.EXPIRATIONDATE,
                    5, --Membership

                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
                    case when CONSTITUENT.ID = @CONSTITUENTID then 1 else 0 end
                from dbo.MEMBERSHIP
                inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                inner join CONSTITUENTCTE C on C.ID = MEMBER.CONSTITUENTID
                inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
                inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                where MEMBER.ISPRIMARY = 1
                    --and ((MEMBERSHIP.STATUSCODE = 0 

                    --and @CURRENTDATE >= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE)

                    --and @CURRENTDATE <= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE))

                    --or (MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE < @CURRENTDATE)

                    --    or (MEMBERSHIP.STATUSCODE = 2 or MEMBERSHIP.STATUSCODE = 1))

                    --and MEMBER.ISDROPPED = 0

            union all 
                select
                    MEMBERSHIP.ID,
                    CONSTITUENT.ID,
                    CONSTITUENT.NAME,
                    0,
                    MEMBERSHIPLEVELTERM.AMOUNT,
                    MEMBERSHIPLEVELTERM.AMOUNT,
                    MEMBERSHIP.EXPIRATIONDATE,
                    5, --Membership

                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
                    case when CONSTITUENT.ID = @CONSTITUENTID then 1 else 0 end
                from dbo.MEMBERSHIP
                inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                inner join CONSTITUENTCTE C on C.ID = MEMBERSHIP.GIVENBYID
                inner join dbo.CONSTITUENT on MEMBERSHIP.GIVENBYID = CONSTITUENT.ID
                inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                where MEMBERSHIP.SENDRENEWALCODE <> 1
                --((MEMBERSHIP.STATUSCODE = 0 

                --            and @CURRENTDATE >= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE)

                --            and @CURRENTDATE <= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE))

                 --           or (MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE < @CURRENTDATE)

                --        or (MEMBERSHIP.STATUSCODE = 2 or MEMBERSHIP.STATUSCODE = 1))                                        

                --    and MEMBERSHIP.SENDRENEWALCODE <> 1

            union all
                select
                    O.ID,
                    C.ID,
                    C.NAME,
                    0,
                    O.AMOUNT - dbo.UFN_OPPORTUNITY_GETAMOUNTPAID(O.ID),
                    null, -- amount due

                    null, -- date due

                    100, -- arbitrary application type code for opportunities, resolved in USP_REVENUE_APPLYTOREVENUESTREAMS

                    dbo.UFN_OPPORTUNITY_GETDESCRIPTION(O.ID),
                    case when C.ID = @CONSTITUENTID then 1 else 0 end
                from 
                    dbo.OPPORTUNITY O
                inner join 
                    dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join 
                    CONSTITUENTCTE on CONSTITUENTCTE.ID = PP.PROSPECTID
                -- CR298118-041508 Only include opportunities which have designations associated with them so that

                -- when we go to fill in a payment for them we have something to apply them to.

                inner join 
                    dbo.OPPORTUNITYDESIGNATION on OPPORTUNITYDESIGNATION.OPPORTUNITYID = O.ID
                left outer join 
                    dbo.CONSTITUENT C on C.ID = PP.PROSPECTID



            ; --Obligatory semi-colon