USP_DATALIST_MAILPREFERENCE

      CREATE procedure dbo.USP_DATALIST_MAILPREFERENCE
      (
        @CONSTITUENTID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @MAILTYPECODE int = -1,
        @SITEFILTERMODE tinyint = 0,
        @SITESSELECTED xml = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @ONLYSHOWACTIVESOLICITCODES bit = 0
      )
      as
        set nocount on;

        set @MAILTYPECODE = coalesce(@MAILTYPECODE, -1);

        declare @ENTERPRISE bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117D2C8-7F46-42F2-ABEB-B654F2F63046');
        declare @ADVANCEDDEV bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('A84DF439-5A75-4A3B-B3F5-B47035DDD3FF');
        declare @CURRENTDATE date = getdate();

        select    
          MAILPREFERENCE.ID, 
          MAILPREFERENCE.MAILTYPE, 
          ACKNOWLEDGEMENTPROCESS.NAME,
          CORRESPONDENCEPROCESS.NAME,
          PLEDGEREMINDERPROCESS.NAME,
          SITE.NAME,
          BUSINESSUNITCODE.DESCRIPTION, 
          APPEALCATEGORYCODE.DESCRIPTION,
          MAILPREFERENCE.RECEIPTTYPE, 
          MAILPREFERENCE.SENDMAIL, 
          MAILPREFERENCE.DELIVERYMETHOD,
          ADDRESSTYPECODE.DESCRIPTION, 
          ADDRESS.ISPRIMARY, 
          dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK,ADDRESS.CITY,ADDRESS.STATEID,ADDRESS.POSTCODE,ADDRESS.COUNTRYID),
          EMAILADDRESSTYPECODE.DESCRIPTION, 
          EMAILADDRESS.ISPRIMARY, 
          EMAILADDRESS.EMAILADDRESS, 
          MAILPREFERENCE.USESEASONALADDRESS,
          MAILPREFERENCE.COMMENTS, 
          CONSTITUENT.ISORGANIZATION,
          CORRESPONDENCECODE.NAME,
          ADDRESS.DONOTMAIL, 
          EVENTCATEGORYCODE.DESCRIPTION,
          MAILPREFERENCE.USEPRIMARYADDRESS,
          MAILPREFERENCE.USEPRIMARYEMAIL,
          EMAILADDRESS.DONOTEMAIL,
          CONSTITUENT.ISGROUP,
          DESIGNATIONLEVEL.NAME,
          case
            when MAILPREFERENCE.MAILTYPECODE in (1,2) and SITE.ID is null and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,null) = 0 
              then 0
            else 1
          end ENABLEACTIONS,
          SOLICITCODE.DESCRIPTION as LINKEDSOLICITCODE,
          case
            when MAILPREFERENCE.CONSTITUENTSOLICITCODEID is not null
              and @CURRENTDATE not between CONSTITUENTSOLICITCODE.STARTDATE and isnull(CONSTITUENTSOLICITCODE.ENDDATE, @CURRENTDATE)
              then 'RES:warning'
            else null
          end as IMAGEKEY
        from dbo.MAILPREFERENCE
          left join dbo.ACKNOWLEDGEMENTPROCESS on ACKNOWLEDGEMENTPROCESS.ID = MAILPREFERENCE.ACKNOWLEDGEMENTID
          left join dbo.CORRESPONDENCEPROCESS on CORRESPONDENCEPROCESS.ID = MAILPREFERENCE.CORRESPONDENCEID
          left join dbo.PLEDGEREMINDERPROCESS on PLEDGEREMINDERPROCESS.ID = MAILPREFERENCE.PLEDGEREMINDERID
          left join dbo.SITE on SITE.ID = MAILPREFERENCE.SITEID
          left join dbo.BUSINESSUNITCODE on BUSINESSUNITCODE.ID = MAILPREFERENCE.BUSINESSUNITCODEID
          left join dbo.APPEALCATEGORYCODE on APPEALCATEGORYCODE.ID = MAILPREFERENCE.CATEGORYCODEID
          left join dbo.EVENTCATEGORYCODE on EVENTCATEGORYCODE.ID = MAILPREFERENCE.EVENTCATEGORYCODEID
          left join dbo.CONSTITUENT on CONSTITUENT.ID = MAILPREFERENCE.CONSTITUENTID
          left join dbo.ADDRESS on ADDRESS.ID = MAILPREFERENCE.ADDRESSID
          left join dbo.ADDRESSTYPECODE on ADDRESSTYPECODE.ID = ADDRESS.ADDRESSTYPECODEID
          left join dbo.EMAILADDRESS on EMAILADDRESS.ID = MAILPREFERENCE.EMAILADDRESSID
          left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESSTYPECODE.ID = EMAILADDRESS.EMAILADDRESSTYPECODEID
          left join dbo.STATE on STATE.ID = ADDRESS.STATEID
          left join dbo.CORRESPONDENCECODE on MAILPREFERENCE.CORRESPONDENCECODEID = CORRESPONDENCECODE.ID
          left join dbo.DESIGNATIONLEVEL on MAILPREFERENCE.PURPOSEID = DESIGNATIONLEVEL.ID
          left join dbo.CONSTITUENTSOLICITCODE on MAILPREFERENCE.CONSTITUENTSOLICITCODEID = CONSTITUENTSOLICITCODE.ID
          left join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
        where 
          MAILPREFERENCE.CONSTITUENTID = @CONSTITUENTID
          and
            (
              dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,MAILPREFERENCE.SITEID) = 1
              or MAILPREFERENCE.SITEID is null
              or MAILPREFERENCE.MAILTYPECODE not in (1, 2)
            ) 
          and 
            (
              @SITEFILTERMODE = 0
              or MAILPREFERENCE.MAILTYPECODE not in (1, 2)
              or MAILPREFERENCE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
            )
          and (MAILPREFERENCE.PURPOSEID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,DESIGNATIONLEVEL.SITEID) = 1)
          and (case
            when MAILPREFERENCE.MAILTYPECODE = 3 then @ENTERPRISE  --General correspondence

            when MAILPREFERENCE.MAILTYPECODE = 5 then @ENTERPRISE  -- Receipts

            when MAILPREFERENCE.MAILTYPECODE = 6 then (@ENTERPRISE | @ADVANCEDDEV)  --Planned gift acknowledgements

            when MAILPREFERENCE.MAILTYPECODE = 8 then @ENTERPRISE  --Stewardship

            else 1
            end ) = 1
          and (
            (@MAILTYPECODE = -1) or (@MAILTYPECODE = MAILPREFERENCE.MAILTYPECODE)
          )
          and (
            @ONLYSHOWACTIVESOLICITCODES = 0
            or (
              @ONLYSHOWACTIVESOLICITCODES = 1
              and @CURRENTDATE between CONSTITUENTSOLICITCODE.STARTDATE and isnull(CONSTITUENTSOLICITCODE.ENDDATE, @CURRENTDATE)
            )
          );