USP_DATALIST_DESIGNATIONLEVELRECIPIENTS

Displays a list of recipients for a Fundraising Purpose.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_DESIGNATIONLEVELRECIPIENTS (@ID uniqueidentifier)
                as begin
                    set nocount on

                    select
                        RECIPIENT.ID,
                        RECIPIENT.CONSTITUENTID,                                     
                        dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null) as NAME,                                         
                        RECIPIENT.DATE,
                        RECIPIENT.AMOUNT,
                        PURPOSERECIPIENTTYPECODE.DESCRIPTION as TYPE,
                        (select sum(DESIGNATIONLEVELDISBURSEMENT.AMOUNT) from dbo.DESIGNATIONLEVELDISBURSEMENT where DESIGNATIONLEVELDISBURSEMENT.CONSTITUENTID = RECIPIENT.CONSTITUENTID and DESIGNATIONLEVELDISBURSEMENT.DESIGNATIONLEVELID = @ID) as TOTALDISBURSEMENTS,
                        (select max(DESIGNATIONLEVELDISBURSEMENT.DATE) from dbo.DESIGNATIONLEVELDISBURSEMENT where DESIGNATIONLEVELDISBURSEMENT.CONSTITUENTID = RECIPIENT.CONSTITUENTID and DESIGNATIONLEVELDISBURSEMENT.DESIGNATIONLEVELID = @ID) as LASTDISBURSEMENT,
                        SCHOLARSHIPTERM.NAME as SCHOLARSHIPTERM,
                        SCHOLARSHIPTERM.STARTDATE,
                        SCHOLARSHIPTERM.ENDDATE,
                        RECIPIENT.FERPA,
                        RECIPIENT.BASECURRENCYID                                                
                    from
                        dbo.DESIGNATIONLEVELRECIPIENT as RECIPIENT
                        inner join dbo.CONSTITUENT on RECIPIENT.CONSTITUENTID = CONSTITUENT.ID
                        left outer join dbo.PURPOSERECIPIENTSTATUSCODE on RECIPIENT.STATUSCODEID = PURPOSERECIPIENTSTATUSCODE.ID
                        left outer join dbo.PURPOSERECIPIENTTYPECODE on RECIPIENT.TYPECODEID = PURPOSERECIPIENTTYPECODE.ID                        
                        left outer join dbo.SCHOLARSHIPTERM on RECIPIENT.SCHOLARSHIPTERMID = SCHOLARSHIPTERM.ID                        
                    where
                        DESIGNATIONLEVELID = @ID
                    order by
                        RECIPIENT.SEQUENCE
                end