UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR

Returns constituents with address and name format processing rules applied.
Return

Return Type
uniqueidentifier
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@SPOUSEID	uniqueidentifier	IN
@CONSTITUENTGENDERCODE	tinyint	IN
@SPOUSEGENDERCODE	tinyint	IN
@JOINTRULETYPECODE	tinyint	IN
@JOINTSELECTIONID	uniqueidentifier	IN
@JOINTSELECTIONBOTHRULETYPECODE	tinyint	IN
@JOINTSELECTIONNEITHERRULETYPECODE	tinyint	IN
@CONSTITUENTGENDERCODEID	uniqueidentifier	IN
@SPOUSEGENDERCODEID	uniqueidentifier	IN
Definition

Copy


CREATE function dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(
  @CONSTITUENTID uniqueidentifier,
  @SPOUSEID uniqueidentifier,
  @CONSTITUENTGENDERCODE tinyint,
  @SPOUSEGENDERCODE tinyint,
  @JOINTRULETYPECODE As tinyint,    
  @JOINTSELECTIONID As uniqueidentifier,    
  @JOINTSELECTIONBOTHRULETYPECODE As tinyint,    
  @JOINTSELECTIONNEITHERRULETYPECODE As tinyint,
  @CONSTITUENTGENDERCODEID uniqueidentifier,
  @SPOUSEGENDERCODEID uniqueidentifier)
returns uniqueidentifier
as
begin

    declare @MALEGENDERCODEID uniqueidentifier = dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(1);
    declare @FEMALEGENDERCODEID uniqueidentifier = dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(2);
    declare @UNKNOWNGENDERCODEID uniqueidentifier = dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0);

    if @SPOUSEID is null 
        return @CONSTITUENTID;

    if @JOINTRULETYPECODE = 1 /*default*/
        return @CONSTITUENTID;

    if @JOINTRULETYPECODE = 2 /*primary*/
        if dbo.UFN_CONSTITUENT_ISPRIMARYHOUSEHOLDMEMBER(@SPOUSEID) = 1 
            return @SPOUSEID
        else 
            return @CONSTITUENTID

    if @JOINTRULETYPECODE = 3 /* male */ 
        if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @MALEGENDERCODEID
        and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @MALEGENDERCODEID 
            return @SPOUSEID
        else
            return @CONSTITUENTID

    if @JOINTRULETYPECODE = 4 /* female */
        if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @FEMALEGENDERCODEID 
        and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @FEMALEGENDERCODEID
            return @SPOUSEID
        else
            return @CONSTITUENTID

    if @JOINTRULETYPECODE = 5 /* selection */ 
    begin
        declare @CONSTITUENTEXISTS bit = 0;
        declare @SPOUSEEXISTS bit = 0;

        set @CONSTITUENTEXISTS = dbo.UFN_IDSETREADER_EXISTSIN_GUID(@JOINTSELECTIONID, @CONSTITUENTID);

        set @SPOUSEEXISTS = dbo.UFN_IDSETREADER_EXISTSIN_GUID(@JOINTSELECTIONID, @SPOUSEID);

        if @CONSTITUENTEXISTS = 1 and @SPOUSEEXISTS = 0
            return @CONSTITUENTID;

        if @CONSTITUENTEXISTS = 0 and @SPOUSEEXISTS = 1
            return @SPOUSEID;

        if @CONSTITUENTEXISTS = 1 and @SPOUSEEXISTS = 1
        begin
            if @JOINTSELECTIONBOTHRULETYPECODE = 1 
                return @CONSTITUENTID;

            if @JOINTSELECTIONBOTHRULETYPECODE = 2 /*primary*/
                if dbo.UFN_CONSTITUENT_ISPRIMARYHOUSEHOLDMEMBER(@SPOUSEID) = 1 
                    return @SPOUSEID
                else 
                    return @CONSTITUENTID


            if @JOINTSELECTIONBOTHRULETYPECODE = 3 /* male */ 
                if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @MALEGENDERCODEID and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @MALEGENDERCODEID
                    return @SPOUSEID
                else
                    return @CONSTITUENTID


            if @JOINTSELECTIONBOTHRULETYPECODE = 4 /* female */ 
                if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @FEMALEGENDERCODEID and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @FEMALEGENDERCODEID
                    return @SPOUSEID
                else
                    return @CONSTITUENTID
        end

        if @CONSTITUENTEXISTS = 1 and @SPOUSEEXISTS = 1
        begin
            if @JOINTSELECTIONNEITHERRULETYPECODE = 1 
                return @CONSTITUENTID;

            if @JOINTSELECTIONNEITHERRULETYPECODE = 2 /*primary*/
                if dbo.UFN_CONSTITUENT_ISPRIMARYHOUSEHOLDMEMBER(@SPOUSEID) = 1 
                    return @SPOUSEID
                else 
                    return @CONSTITUENTID


            if @JOINTSELECTIONNEITHERRULETYPECODE = 3 /* male */ 
                if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @MALEGENDERCODEID and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @MALEGENDERCODEID
                    return @SPOUSEID
                else
                    return @CONSTITUENTID


            if @JOINTSELECTIONNEITHERRULETYPECODE = 4 /* female */ 
                if isnull(@SPOUSEGENDERCODEID, @UNKNOWNGENDERCODEID) = @FEMALEGENDERCODEID and isnull(@CONSTITUENTGENDERCODEID, @UNKNOWNGENDERCODEID) <> @FEMALEGENDERCODEID
                    return @SPOUSEID
                else
                    return @CONSTITUENTID
        end                    


    end

    return @CONSTITUENTID;
end