UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT

Returns address and name formats according to contact preference settings.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@MAILTYPE	tinyint	IN
@PARAMETERSETID	uniqueidentifier	IN
@SEASONALDATE	datetime	IN
@INCLUDEHOUSEHOLDPROCESSING	bit	IN
@USEADDRESSEEFORMAT	bit	IN
@ADDRESSEEFORMATID	uniqueidentifier	IN
@ADDRESSEEFORMATISPRIMARY	bit	IN
@ALTADDRESSEEFORMATID	uniqueidentifier	IN
@ALTADDRESSEEFORMATISPRIMARY	bit	IN
@ADDRESSEEFUNCTIONID	uniqueidentifier	IN
@USESALUTATIONFORMAT	bit	IN
@SALUTATIONFORMATID	uniqueidentifier	IN
@SALUTATIONFORMATISPRIMARY	bit	IN
@ALTSALUTATIONFORMATID	uniqueidentifier	IN
@ALTSALUTATIONFORMATISPRIMARY	bit	IN
@SALUTATIONFUNCTIONID	uniqueidentifier	IN
@USECONTACTADDRESSEEFORMAT	bit	IN
@CONTACTADDRESSEEFORMATID	uniqueidentifier	IN
@CONTACTADDRESSEEFORMATISPRIMARY	bit	IN
@ALTCONTACTADDRESSEEFORMATID	uniqueidentifier	IN
@ALTCONTACTADDRESSEEFORMATISPRIMARY	bit	IN
@CONTACTADDRESSEEFUNCTIONID	uniqueidentifier	IN
@ORGSALUTATIONCODE	tinyint	IN
@USECONTACTSALUTATIONFORMAT	bit	IN
@CONTACTSALUTATIONFORMATID	uniqueidentifier	IN
@CONTACTSALUTATIONFORMATISPRIMARY	bit	IN
@ALTCONTACTSALUTATIONFORMATID	uniqueidentifier	IN
@ALTCONTACTSALUTATIONFORMATISPRIMARY	bit	IN
@CONTACTSALUTATIONFUNCTIONID	uniqueidentifier	IN
@CONTACTSALUTATIONOPTIONCODE	tinyint	IN
@CUSTOMNAME	nvarchar(100)	IN
@INDUSESEASONALADDRESS	bit	IN
@ORGMAILINGPREFERENCE	tinyint	IN
@INDALTADDRESS1TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS1ISPRIMARY	bit	IN
@INDALTADDRESS2TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS2ISPRIMARY	bit	IN
@ORGALTADDRESS1TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS1ISPRIMARY	bit	IN
@ORGALTADDRESS2TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS2ISPRIMARY	bit	IN
@INDINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOCONTACT	bit	IN
@ORGSENDTOALLCONTACTS	bit	IN
@INDUSECONSTITUENTPREFS	bit	IN
@ORGUSECONSTITUENTPREFS	bit	IN
@GROUPALTADDRESS1TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS1ISPRIMARY	bit	IN
@GROUPALTADDRESS2TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS2ISPRIMARY	bit	IN
@GROUPINCLUDEWITHNOADDRESS	bit	IN
@GROUPUSECONSTITUENTPREFS	tinyint	IN

Definition

Copy



        CREATE function dbo.UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT
        (
            @CONSTITUENTID uniqueidentifier,  
            @NAMEFORMATPARAMETERID uniqueidentifier,      
            @ADDRESSPROCESSINGOPTIONID uniqueidentifier,      
            @MAILTYPE tinyint,      
            @PARAMETERSETID uniqueidentifier,      
            @SEASONALDATE datetime,      
            @INCLUDEHOUSEHOLDPROCESSING bit,    
            @USEADDRESSEEFORMAT bit,    
            @ADDRESSEEFORMATID uniqueidentifier,    
            @ADDRESSEEFORMATISPRIMARY bit,    
            @ALTADDRESSEEFORMATID uniqueidentifier,    
            @ALTADDRESSEEFORMATISPRIMARY bit,    
            @ADDRESSEEFUNCTIONID uniqueidentifier,    
            @USESALUTATIONFORMAT bit,    
            @SALUTATIONFORMATID uniqueidentifier,    
            @SALUTATIONFORMATISPRIMARY bit,    
            @ALTSALUTATIONFORMATID uniqueidentifier,    
            @ALTSALUTATIONFORMATISPRIMARY bit,    
            @SALUTATIONFUNCTIONID uniqueidentifier,    
            @USECONTACTADDRESSEEFORMAT bit,    
            @CONTACTADDRESSEEFORMATID uniqueidentifier,    
            @CONTACTADDRESSEEFORMATISPRIMARY bit,    
            @ALTCONTACTADDRESSEEFORMATID uniqueidentifier,    
            @ALTCONTACTADDRESSEEFORMATISPRIMARY bit,    
            @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,    
            @ORGSALUTATIONCODE tinyint,    
            @USECONTACTSALUTATIONFORMAT bit,    
            @CONTACTSALUTATIONFORMATID uniqueidentifier,    
            @CONTACTSALUTATIONFORMATISPRIMARY bit,    
            @ALTCONTACTSALUTATIONFORMATID uniqueidentifier,    
            @ALTCONTACTSALUTATIONFORMATISPRIMARY bit,    
            @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,    
            @CONTACTSALUTATIONOPTIONCODE tinyint,    
            @CUSTOMNAME nvarchar(100),    
            @INDUSESEASONALADDRESS bit,    
            @ORGMAILINGPREFERENCE tinyint,    
            @INDALTADDRESS1TYPECODEID uniqueidentifier,    
            @INDALTADDRESS1ISPRIMARY bit,    
            @INDALTADDRESS2TYPECODEID uniqueidentifier,    
            @INDALTADDRESS2ISPRIMARY bit,    
            @ORGALTADDRESS1TYPECODEID uniqueidentifier,    
            @ORGALTADDRESS1ISPRIMARY bit,    
            @ORGALTADDRESS2TYPECODEID uniqueidentifier,    
            @ORGALTADDRESS2ISPRIMARY bit,    
            @INDINCLUDEWITHNOADDRESS bit,    
            @ORGINCLUDEWITHNOADDRESS bit,    
            @ORGINCLUDEWITHNOCONTACT bit,    
            @ORGSENDTOALLCONTACTS bit,    
            @INDUSECONSTITUENTPREFS bit,    
            @ORGUSECONSTITUENTPREFS bit,    
            @GROUPALTADDRESS1TYPECODEID uniqueidentifier,    
            @GROUPALTADDRESS1ISPRIMARY bit,    
            @GROUPALTADDRESS2TYPECODEID uniqueidentifier,    
            @GROUPALTADDRESS2ISPRIMARY bit,    
            @GROUPINCLUDEWITHNOADDRESS bit,    
            @GROUPUSECONSTITUENTPREFS tinyint    
        )      
         returns table 
         as
         return
         (
            --This existing function is inline, so alter function won't convert it into a non-inline function.

            --So all the real work is now done in UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT_WORKER which is too complex to

            --easily be an inline function. Almost any change should be done in UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT_WORKER. 

            --Sorry for the confusion.

            select
                CONSTITUENTID,
                ADDRESSEE,
                SALUTATION,
                CONTACT,
                POSITION,
                ADDRESSBLOCK,
                CITY,
                STATE,
                POSTCODE,
                COUNTRY,
                CONTACTID,
                HOUSEHOLDID,
                RETURNEDASHOUSEHOLDMEMBER,
                GROUPCONTACTID,
                CONTACTORCONSTITUENTID
            from dbo.UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT_WORKER
            (
                @CONSTITUENTID,  
                @NAMEFORMATPARAMETERID,      
                @ADDRESSPROCESSINGOPTIONID,      
                @MAILTYPE,      
                @PARAMETERSETID,      
                @SEASONALDATE,      
                @INCLUDEHOUSEHOLDPROCESSING,    
                @USEADDRESSEEFORMAT,    
                @ADDRESSEEFORMATID,    
                @ADDRESSEEFORMATISPRIMARY,    
                @ALTADDRESSEEFORMATID,    
                @ALTADDRESSEEFORMATISPRIMARY,    
                @ADDRESSEEFUNCTIONID,    
                @USESALUTATIONFORMAT,    
                @SALUTATIONFORMATID,    
                @SALUTATIONFORMATISPRIMARY,    
                @ALTSALUTATIONFORMATID,    
                @ALTSALUTATIONFORMATISPRIMARY,    
                @SALUTATIONFUNCTIONID,    
                @USECONTACTADDRESSEEFORMAT,    
                @CONTACTADDRESSEEFORMATID,    
                @CONTACTADDRESSEEFORMATISPRIMARY,    
                @ALTCONTACTADDRESSEEFORMATID,    
                @ALTCONTACTADDRESSEEFORMATISPRIMARY,    
                @CONTACTADDRESSEEFUNCTIONID,    
                @ORGSALUTATIONCODE,    
                @USECONTACTSALUTATIONFORMAT,    
                @CONTACTSALUTATIONFORMATID,    
                @CONTACTSALUTATIONFORMATISPRIMARY,    
                @ALTCONTACTSALUTATIONFORMATID,    
                @ALTCONTACTSALUTATIONFORMATISPRIMARY,    
                @CONTACTSALUTATIONFUNCTIONID,    
                @CONTACTSALUTATIONOPTIONCODE,    
                @CUSTOMNAME,    
                @INDUSESEASONALADDRESS,    
                @ORGMAILINGPREFERENCE,    
                @INDALTADDRESS1TYPECODEID,    
                @INDALTADDRESS1ISPRIMARY,    
                @INDALTADDRESS2TYPECODEID,    
                @INDALTADDRESS2ISPRIMARY,    
                @ORGALTADDRESS1TYPECODEID,    
                @ORGALTADDRESS1ISPRIMARY,    
                @ORGALTADDRESS2TYPECODEID,    
                @ORGALTADDRESS2ISPRIMARY,    
                @INDINCLUDEWITHNOADDRESS,    
                @ORGINCLUDEWITHNOADDRESS,    
                @ORGINCLUDEWITHNOCONTACT,    
                @ORGSENDTOALLCONTACTS,    
                @INDUSECONSTITUENTPREFS,    
                @ORGUSECONSTITUENTPREFS,    
                @GROUPALTADDRESS1TYPECODEID,    
                @GROUPALTADDRESS1ISPRIMARY,    
                @GROUPALTADDRESS2TYPECODEID,    
                @GROUPALTADDRESS2ISPRIMARY,    
                @GROUPINCLUDEWITHNOADDRESS,    
                @GROUPUSECONSTITUENTPREFS
            )
         )