UFN_QUERY_CORRESPONDENCEOUTPUT

Return

Return Type
table

Parameters

Parameter Parameter Type Mode Description
@NAMEFORMATPARAMETERID uniqueidentifier IN
@ADDRESSPROCESSINGOPTIONID uniqueidentifier IN
@PARAMETERSETID uniqueidentifier IN
@SEASONALDATE datetime IN
@INCLUDEHOUSEHOLDPROCESSING bit 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 bit IN
@INDIVIDUALADDRESSEES xml IN
@ADDRESSEEFUNCTIONID uniqueidentifier IN
@INDIVIDUALSALUTATIONS xml IN
@SALUTATIONFUNCTIONID uniqueidentifier IN
@ORGADDRESSEES xml IN
@CONTACTADDRESSEEFUNCTIONID uniqueidentifier IN
@ORGSALUTATIONCODE tinyint IN
@ORGSALUTATIONS xml IN
@CONTACTSALUTATIONFUNCTIONID uniqueidentifier IN
@CONTACTSALUTATIONOPTIONCODE tinyint IN
@CUSTOMNAME nvarchar(100) IN
@GROUPADDRESSEES xml IN
@GROUPADDRESSEEFUNCTIONID uniqueidentifier IN
@GROUPSALUTATIONCODE tinyint IN
@GROUPSALUTATIONS xml IN
@GROUPSALUTATIONFUNCTIONID uniqueidentifier IN
@GROUPNOCONTACTOPTIONCODE bit IN
@GROUPNOCONTACTCUSTOMNAME nvarchar(100) IN
@JOINTRULETYPECODE tinyint IN
@JOINTSELECTIONID uniqueidentifier IN
@JOINTSELECTIONBOTHRULETYPECODE tinyint IN
@JOINTSELECTIONNEITHERRULETYPECODE tinyint IN

Definition

Copy 


                CREATE function [dbo].UFN_QUERY_CORRESPONDENCEOUTPUT   
                (
                    @NAMEFORMATPARAMETERID uniqueidentifier,          
                    @ADDRESSPROCESSINGOPTIONID uniqueidentifier,          
                    @PARAMETERSETID uniqueidentifier = null,          
                    @SEASONALDATE datetime = null,          
                    @INCLUDEHOUSEHOLDPROCESSING bit = null,        
                    @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 bit,
                    @INDIVIDUALADDRESSEES xml,
                    @ADDRESSEEFUNCTIONID uniqueidentifier,
                    @INDIVIDUALSALUTATIONS xml,
                    @SALUTATIONFUNCTIONID uniqueidentifier,
                    @ORGADDRESSEES xml,
                    @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,
                    @ORGSALUTATIONCODE tinyint,
                    @ORGSALUTATIONS xml,
                    @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,
                    @CONTACTSALUTATIONOPTIONCODE tinyint,
                    @CUSTOMNAME nvarchar(100),
                    @GROUPADDRESSEES xml,
                    @GROUPADDRESSEEFUNCTIONID uniqueidentifier,
                    @GROUPSALUTATIONCODE tinyint,
                    @GROUPSALUTATIONS xml,
                    @GROUPSALUTATIONFUNCTIONID uniqueidentifier,
                    @GROUPNOCONTACTOPTIONCODE bit,
                    @GROUPNOCONTACTCUSTOMNAME nvarchar(100),
                    @JOINTRULETYPECODE As tinyint,
                    @JOINTSELECTIONID As uniqueidentifier,
                    @JOINTSELECTIONBOTHRULETYPECODE As tinyint,
                    @JOINTSELECTIONNEITHERRULETYPECODE As tinyint
                )    
                returns table          
                as          
                return          
                (          
                    select           
                        NF.CONSTITUENTID as CONSTITUENTID,               
                        (select CONSTITUENT.NAME from dbo.CONSTITUENT where NF.CONTACTORCONSTITUENTID = CONSTITUENT.ID) as CONSTITUENTNAME,    
                        NF.ADDRESSEE,           
                        NF.SALUTATION,               
                        (select CONSTITUENT.NICKNAME from dbo.CONSTITUENT where NF.CONTACTORCONSTITUENTID = CONSTITUENT.ID) as NICKNAME,    
                        NF.CONTACT as CONTACTADDRESSEE,          
                        NF.ADDRESSBLOCK,          
                        NF.CITY,          
                        cast(NF.STATE as nvarchar(50)) as STATE,
                        NF.POSTCODE,
                        NF.COUNTRY,
                        (select PHONE.NUMBER from dbo.PHONE where PHONE.CONSTITUENTID = NF.MAILTOCONSTITUENTID and PHONE.ISPRIMARY = 1) as NUMBER,
                        CONSTITUENT.WEBADDRESS,  
                        dbo.UFN_CONSTITUENT_BUILDNAME(NF.SPOUSEID) as SPOUSE,  
                        (case when (select CONSTITUENT.ISORGANIZATION from dbo.CONSTITUENT where CONSTITUENT.ID = NF.MAILTOCONSTITUENTID) = 0 then  
                            (select top 1 dbo.UFN_CONSTITUENT_BUILDNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID)
                                from dbo.RELATIONSHIP  
                                where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = NF.MAILTOCONSTITUENTID  
                                    and RELATIONSHIP.ISPRIMARYBUSINESS = 1)  
                        else null end) as PRIMARYBUSINESS,  
                        (select top 1 SCHOOL.NAME 
                            from dbo.EDUCATIONALHISTORY HISTORY  
                                inner join dbo.EDUCATIONALINSTITUTION SCHOOL on HISTORY.EDUCATIONALINSTITUTIONID = SCHOOL.ID  
                            where HISTORY.ISPRIMARYRECORD = 1 and HISTORY.CONSTITUENTID = NF.MAILTOCONSTITUENTID
                        ) as PRIMARYEDUCATION,
                        NF.POSITION,
                        NF.CONTACTID,
                        NF.HOUSEHOLDID,
                        NF.RETURNEDASHOUSEHOLDMEMBER,
                        NF.GROUPCONTACTID
                    from dbo.CONSTITUENT
                    inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT(  
                            @NAMEFORMATPARAMETERID,
                            @ADDRESSPROCESSINGOPTIONID,
                            3,
                            @PARAMETERSETID,
                            @SEASONALDATE,
                            @INCLUDEHOUSEHOLDPROCESSING ,
                            @INDUSESEASONALADDRESS ,
                            @ORGMAILINGPREFERENCE ,
                            @INDALTADDRESS1TYPECODEID,
                            @INDALTADDRESS1ISPRIMARY ,
                            @INDALTADDRESS2TYPECODEID,
                            @INDALTADDRESS2ISPRIMARY ,
                            @ORGALTADDRESS1TYPECODEID,
                            @ORGALTADDRESS1ISPRIMARY ,
                            @ORGALTADDRESS2TYPECODEID,
                            @ORGALTADDRESS2ISPRIMARY ,
                            @INDINCLUDEWITHNOADDRESS ,
                            @ORGINCLUDEWITHNOADDRESS ,
                            @ORGINCLUDEWITHNOCONTACT ,
                            @ORGSENDTOALLCONTACTS ,
                            @INDUSECONSTITUENTPREFS ,
                            @ORGUSECONSTITUENTPREFS ,
                            @GROUPALTADDRESS1TYPECODEID,
                            @GROUPALTADDRESS1ISPRIMARY ,
                            @GROUPALTADDRESS2TYPECODEID,
                            @GROUPALTADDRESS2ISPRIMARY ,
                            @GROUPINCLUDEWITHNOADDRESS ,
                            @GROUPUSECONSTITUENTPREFS ,
                            @INDIVIDUALADDRESSEES ,
                            @ADDRESSEEFUNCTIONID,
                            @INDIVIDUALSALUTATIONS ,
                            @SALUTATIONFUNCTIONID,
                            @ORGADDRESSEES ,
                            @CONTACTADDRESSEEFUNCTIONID,
                            @ORGSALUTATIONCODE,
                            @ORGSALUTATIONS ,
                            @CONTACTSALUTATIONFUNCTIONID,
                            @CONTACTSALUTATIONOPTIONCODE ,
                            @CUSTOMNAME ,
                            @GROUPADDRESSEES ,
                            @GROUPADDRESSEEFUNCTIONID,
                            @GROUPSALUTATIONCODE,
                            @GROUPSALUTATIONS ,
                            @GROUPSALUTATIONFUNCTIONID,
                            @GROUPNOCONTACTOPTIONCODE ,
                            @GROUPNOCONTACTCUSTOMNAME,
                            @JOINTRULETYPECODE,
                            @JOINTSELECTIONID,
                            @JOINTSELECTIONBOTHRULETYPECODE,
                            @JOINTSELECTIONNEITHERRULETYPECODE, 
                            0 /*place holder for future excludespouse */) NF
                        on NF.CONSTITUENTID = CONSTITUENT.ID 
                )