UFN_DECLARATIONS_GETWITHCCRN_BULK

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

create function dbo.UFN_DECLARATIONS_GETWITHCCRN_BULK()
returns table
as
return
(
    select
        TAXDECLARATION.ID,
        TAXDECLARATION.CONSTITUENTID,
        CHARITYCLAIMREFERENCENUMBER.ID as CHARITYCLAIMREFERENCENUMBERID,
        CHARITYCLAIMREFERENCENUMBER.REFERENCENUMBER as CHARITYCLAIMREFERENCENUMBER,
        TAXDECLARATION.PAYSTAXCODE,
        TAXDECLARATION.DECLARATIONSTARTS,
        coalesce(TAXDECLARATION.DECLARATIONENDS, cast('9999-12-31 23:59:59.9999' as date)) as DECLARATIONENDS,
        TAXDECLARATION.DATECHANGED as DATETAXDECLARATIONCHANGED -- returns the date the declaration was last changed

    from dbo.TAXDECLARATION
    inner join dbo.CHARITYCLAIMREFERENCENUMBER on TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
    where
        (
            TAXDECLARATION.DECLARATIONINDICATORCODE <> 1 or -- Not oral declaration

            (
                TAXDECLARATION.CONFIRMATIONSENT is not null and
                datediff(d, TAXDECLARATION.CONFIRMATIONSENT, getdate()) >= CHARITYCLAIMREFERENCENUMBER.WAITINGPERIOD
            )
        )
)