USP_DATALIST_CAMPAIGNFROMFUNDRAISINGPURPOSE

This datalist returns all campaigns for designations in a fundraising purpose.
Parameters

Parameter	Parameter Type	Mode	Description
@PURPOSEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CAMPAIGNFROMFUNDRAISINGPURPOSE (
                    @PURPOSEID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier
                ) as
                    set nocount on;

                    select
                        CAMPAIGN.ID [CAMPAIGNID],
                        CAMPAIGN.NAME [CAMPAIGNNAME],
                        DESIGNATION.ID [DESIGNATIONID],
                        DESIGNATION.NAME [DESIGNATIONNAME],
                        CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION [SUBPRIORITYNAME],
                        DESIGNATIONCAMPAIGN.DATEFROM,
                        DESIGNATIONCAMPAIGN.DATETO
                    from dbo.DESIGNATIONCAMPAIGN
                        inner join dbo.CAMPAIGN on DESIGNATIONCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                        inner join dbo.DESIGNATION on DESIGNATION.ID = DESIGNATIONCAMPAIGN.DESIGNATIONID
                        left outer join dbo.CAMPAIGNSUBPRIORITY on DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID
                        left join dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
                    where 
                        DESIGNATIONCAMPAIGN.DESIGNATIONID in
                            (
                                select ID
                                from dbo.DESIGNATION
                                where (
                                        DESIGNATION.DESIGNATIONLEVEL1ID = @PURPOSEID 
                                            or DESIGNATION.DESIGNATIONLEVEL2ID = @PURPOSEID 
                                            or DESIGNATION.DESIGNATIONLEVEL3ID = @PURPOSEID 
                                            or DESIGNATION.DESIGNATIONLEVEL4ID = @PURPOSEID 
                                            or DESIGNATION.DESIGNATIONLEVEL5ID = @PURPOSEID
                                    )
                                    and (
                                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATION.ID))=1
                                    )
                            )
                    order by CAMPAIGNNAME, DESIGNATIONNAME, DATEFROM