UFN_DECLARATIONS_GET

Returns tax declarations for a site and date range.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@DATE	datetime	IN
@SITEID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
Definition

Copy


            -- NOTE:  Any changes here should also be made in UFN_VALIDDECLARATION

            CREATE function dbo.UFN_DECLARATIONS_GET
            (
                @DATE datetime,
                @SITEID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier
            )
            returns table
            as
            return
            (
                select
                    TAXDECLARATION.ID,
                    PAYSTAXCODE,
                    TAXDECLARATION.DATECHANGED as DATETAXDECLARATIONCHANGED -- returns the date the declaration was last changed

                from dbo.TAXDECLARATION
                inner join dbo.CHARITYCLAIMREFERENCENUMBER on TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
                left join dbo.CHARITYCLAIMREFERENCENUMBERSITE on CHARITYCLAIMREFERENCENUMBER.ID = CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID
                where
                    TAXDECLARATION.CONSTITUENTID = @CONSTITUENTID and
                    (
                        (
                            TAXDECLARATION.DECLARATIONSTARTS <= @DATE and
                            TAXDECLARATION.DECLARATIONENDS >= @DATE
                        ) or
                        (
                            TAXDECLARATION.DECLARATIONSTARTS <= @DATE and
                            TAXDECLARATION.DECLARATIONENDS is null
                        )
                    ) and
                    (
                        TAXDECLARATION.DECLARATIONINDICATORCODE <> 1 or -- Not oral declaration

                        (
                            TAXDECLARATION.CONFIRMATIONSENT is not null and
                            datediff(d, TAXDECLARATION.CONFIRMATIONSENT, getdate()) >= CHARITYCLAIMREFERENCENUMBER.WAITINGPERIOD
                        )
                    ) and
                    (
                        CHARITYCLAIMREFERENCENUMBERSITE.SITEID = @SITEID or
                        (
                            CHARITYCLAIMREFERENCENUMBERSITE.SITEID is null and
                            @SITEID is null
                        ) or
                        -- Handle sites that use the default CCRN

                        (
                            CHARITYCLAIMREFERENCENUMBERSITE.SITEID is null and
                            not exists
                            (
                                select 1
                                from dbo.CHARITYCLAIMREFERENCENUMBERSITE
                                where CHARITYCLAIMREFERENCENUMBERSITE.SITEID = @SITEID
                            )
                        )
                    )
            )