USP_CONSTITUENTPROFILE_MAILPREFERENCE

            CREATE procedure dbo.USP_CONSTITUENTPROFILE_MAILPREFERENCE(
                @CONSTITUENTID uniqueidentifier,
                @ISVISIBLE bit = 1 
            )
            as
                set nocount on;

                if @ISVISIBLE = 1 
                begin
                    select    
                        mp.ID as ID, 
                        mp.MAILTYPE as MAILTYPE, 
                        aproc.NAME as ACKNOWLEDGEMENTPROCESSNAME,
                        cproc.NAME as CORRESPONDENCEPROCESSNAME,
                        prproc.NAME as PLEDGEREMINDERPROCESSNAME,
                        s.NAME as SITENAME,
                        buc.DESCRIPTION as BUSINESSUNITCODEDESCRIPTION, 
                        apc.DESCRIPTION as APPEALCATEGORYCODEDESCRIPTION,
                        mp.RECEIPTTYPE as RECEIPTTYPE, 
                        mp.SENDMAIL as SENDMAIL, 
                        mp.DELIVERYMETHOD as DELIVERYMETHOD,
                        atc.DESCRIPTION as ADDRESSTYPECODEDESCRIPTION, 
                        a.ISPRIMARY as ADDRESSISPRIMARY, 
                        case a.ADDRESSBLOCK when '' then ''
                                else coalesce(a.ADDRESSBLOCK + ', ','') end + 
                            case a.CITY when '' then ''
                                else coalesce(a.CITY + ', ','') end + 
                            case st.ABBREVIATION when '' then ''
                                else coalesce(st.ABBREVIATION + ', ','') end + 
                            case a.POSTCODE when '' then ''
                                else coalesce(a.POSTCODE,'') end as ADDRESS,
                        eatc.DESCRIPTION as EMAILADDRESSTYPECODEDESCRIPTION, 
                        ea.ISPRIMARY as EMAILADDRESSISPRIMARY, 
                        ea.EMAILADDRESS as EMAILADDRESS, 
                        mp.USESEASONALADDRESS as USESEASONALADDRESS,
                        c.ISORGANIZATION as ISORGANIZATION,
                        cc.NAME as CORRESPONDENCECODENAME,
                        a.DONOTMAIL as DONOTMAIL, 
                        ec.DESCRIPTION as EVENTCATEGORYCODEDESCRIPTION,
                        mp.USEPRIMARYADDRESS as USEPRIMARYADDRESS,
                        mp.USEPRIMARYEMAIL as USEPRIMARYEMAIL,
                        ea.DONOTEMAIL as DONOTEMAIL,
            case 
              when c.ISORGANIZATION = 1 then dbo.UFN_CONSTITUENTPROFILE_MAILPREFERENCE_CONTACTS(mp.ID)
              else ''
            end as CONTACTS
                    from MAILPREFERENCE mp
                        left join ACKNOWLEDGEMENTPROCESS aproc on aproc.ID=mp.ACKNOWLEDGEMENTID
                        left join CORRESPONDENCEPROCESS cproc on cproc.ID=mp.CORRESPONDENCEID
                        left join PLEDGEREMINDERPROCESS prproc on prproc.ID=mp.PLEDGEREMINDERID
                        left join SITE s on s.ID=mp.SITEID
                        left join BUSINESSUNITCODE buc on buc.ID=mp.BUSINESSUNITCODEID
                        left join APPEALCATEGORYCODE apc on apc.ID=mp.CATEGORYCODEID
                        left join EVENTCATEGORYCODE ec on ec.ID=mp.EVENTCATEGORYCODEID
                        left join CONSTITUENT c on c.ID=mp.CONSTITUENTID
                        left join ADDRESS a on a.ID = mp.ADDRESSID
                        left join ADDRESSTYPECODE atc on atc.ID=a.ADDRESSTYPECODEID
                        left join EMAILADDRESS ea on ea.ID = mp.EMAILADDRESSID
                        left join EMAILADDRESSTYPECODE eatc on eatc.ID=ea.EMAILADDRESSTYPECODEID
                        left join STATE st on st.ID=a.STATEID
                        left join CORRESPONDENCECODE cc on mp.CORRESPONDENCECODEID=cc.ID
                    where mp.CONSTITUENTID=@CONSTITUENTID
                end