UFN_VALIDDECLARATION

            -- NOTE:  Any changes here should also be made in UFN_DECLARATIONS_GET or (depending on which

            -- section is changed) UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE.

            -- TODO:  At some point, this function should be changed to leverage one of those functions

            -- so the logic is consolidated but the change would be too disruptive to apply during 

            -- regression.

            CREATE function dbo.UFN_VALIDDECLARATION(
                @DATE as datetime = null, 
                @CONSTITUENTID as uniqueidentifier = null,
                @DESIGNATIONID as uniqueidentifier = null,
                @REGISTRANTID as uniqueidentifier = null,
                @MEMBERSHIPID as uniqueidentifier = null
            )
            returns tinyint
            with execute as caller
            as begin

                declare @SITEID as uniqueidentifier;
                declare @EVENTID as uniqueidentifier;
                declare @INDICATOR as integer;
                declare @PAYSTAXCODE as integer;
                declare @CONFIRMATIONSENT as datetime;
                declare @FOUND as integer;
                declare @SITE as table(SITEID uniqueidentifier);
                declare @WAITINGPERIOD as integer;

                set @FOUND = 0;

                if not @DESIGNATIONID is null
                    set @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID);

                if not @REGISTRANTID is null
                    insert into @SITE(SITEID)
                    select SITEID
                    from dbo.UFN_SITEID_MAPFROM_REGISTRANTID(@REGISTRANTID);

                if not @MEMBERSHIPID is null
                    set @SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPPROGRAMID(@MEMBERSHIPID);

        --check if @SITEID is a site associated with a CCRN on a declaration for this constituent

        --    or if @SITE contains a site associated with a CCRN on a declaration for this constituent

        select top 1
              @FOUND = 1,
          @CONFIRMATIONSENT = CONFIRMATIONSENT,
                    @PAYSTAXCODE = PAYSTAXCODE,
                    @INDICATOR = DECLARATIONINDICATORCODE,
                    @WAITINGPERIOD = WAITINGPERIOD
          from dbo.TAXDECLARATION TD
              inner join dbo.CHARITYCLAIMREFERENCENUMBER CCRN on CCRN.ID = TD.CHARITYCLAIMREFERENCENUMBERID
              left join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS on CCRNS.CHARITYCLAIMREFERENCENUMBERID = CCRN.ID
          where CONSTITUENTID = @CONSTITUENTID
                and (DECLARATIONSTARTS <= @DATE and DECLARATIONENDS >= @DATE
                or DECLARATIONSTARTS <= @DATE and DECLARATIONENDS is null)
                and PAYSTAXCODE <> 2
                and (
                        (CCRNS.SITEID = @SITEID)
                        or (CCRNS.SITEID in (select SITEID from @SITE))
                    ) 
          order by DECLARATIONINDICATORCODE asc;

        --one of either @SITEID or @SITE has a value for a site or sites that are not associated with any CCRN

        --  on a declaration for the constituent

          if @FOUND = 0 and (@SITEID is not null or ((select COUNT(*) from @SITE) != 0))
          begin
          --check to see if there are any CCRNs that are associated with the site or sites specified.

              declare @CCRNSEXIST integer = 0;
              select top 1 @CCRNSEXIST = 1
                  from dbo.CHARITYCLAIMREFERENCENUMBER CCRN
                      inner join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS on CCRNS.CHARITYCLAIMREFERENCENUMBERID = CCRN.ID
                  where CCRNS.SITEID = @SITEID or CCRNS.SITEID in (select SITEID from @SITE);

          --since there are no declarations with the specified site, we must check to see if the constituent has a

          --  declaration that is associated with a CCRN with "blank" site. A declaration with a CCRN associated with 

          --  "blank" site can only be used if the site or sites in question are not associated with any other CCRNs.

              select top 1
                  @FOUND = 1,
            @CONFIRMATIONSENT = CONFIRMATIONSENT,
                      @PAYSTAXCODE = PAYSTAXCODE,
                      @INDICATOR = DECLARATIONINDICATORCODE,
                      @WAITINGPERIOD = WAITINGPERIOD
              from dbo.TAXDECLARATION TD
                  inner join dbo.CHARITYCLAIMREFERENCENUMBER CCRN on CCRN.ID = TD.CHARITYCLAIMREFERENCENUMBERID
              where CONSTITUENTID = @CONSTITUENTID
                  and PAYSTAXCODE <> 2
                  and @CCRNSEXIST = 0
                  and ( CCRN.ID not in (select CHARITYCLAIMREFERENCENUMBERID from dbo.CHARITYCLAIMREFERENCENUMBERSITE))
                  and (DECLARATIONSTARTS <= @DATE and DECLARATIONENDS >= @DATE
                  or DECLARATIONSTARTS <= @DATE and DECLARATIONENDS is null)
              order by DECLARATIONINDICATORCODE asc;
          end

        --all records specified do not have a site associated with them, so check if the constituent has a declaration

        --  on file that has a CCRN associated with "blank" site, which is the only kind of declaration that can be valid

        --  when no site has been specified

          if @FOUND = 0 and (@SITEID is null and ((select count(*) from @SITE) = 0))
          begin
              select top 1
                  @FOUND = 1,
            @CONFIRMATIONSENT = CONFIRMATIONSENT,
                      @PAYSTAXCODE = PAYSTAXCODE,
                      @INDICATOR = DECLARATIONINDICATORCODE,
                      @WAITINGPERIOD = WAITINGPERIOD
              from dbo.TAXDECLARATION TD
                  inner join dbo.CHARITYCLAIMREFERENCENUMBER CCRN on CCRN.ID = TD.CHARITYCLAIMREFERENCENUMBERID
              where CONSTITUENTID = @CONSTITUENTID
                  and PAYSTAXCODE <> 2
                  and ( CCRN.ID not in (select CHARITYCLAIMREFERENCENUMBERID from dbo.CHARITYCLAIMREFERENCENUMBERSITE))
                  and (DECLARATIONSTARTS <= @DATE and DECLARATIONENDS >= @DATE
                  or DECLARATIONSTARTS <= @DATE and DECLARATIONENDS is null)
              order by DECLARATIONINDICATORCODE asc;
          end

                if @FOUND = 1
                    begin
                        if @PAYSTAXCODE = 1
                            if (@INDICATOR = 1) /* Oral */
                                if @CONFIRMATIONSENT is null 
                                    return 0    /* No Valid declaration */
                                else
                                    if DATEDIFF(d,@CONFIRMATIONSENT,getdate())<@WAITINGPERIOD
                                        return 0    /* No Valid declaration */
                                    else
                                        return 2    /* Valid "yes" declaration */
                            else    /* Written or Internet */
                                return 2        /* Valid "yes" declaration */    
                        else
                            if @PAYSTAXCODE = 0
                                return 1            /* Valid "no" declaration */
                    end
                else                
                    return 0

                return 0                    
            end