USP_BATCH_VALIDATETAXDECLARATIONS

            CREATE procedure dbo.USP_BATCH_VALIDATETAXDECLARATIONS
            (
                @TAXDECLARATIONS xml,
                @BATCHTYPE tinyint, -- 0 for Constituent, 1 for Revenue

                @CONSTITUENTID uniqueidentifier = null  -- Only applicable for Revenue batch.  Used to include constituent's existing records.

            )
            as
                set nocount on

                -- Validate tax declarations fields.  Using a table variable so the XML doesn't have to

                -- to be deserialized twice.

                declare @TAXDECLARATIONSFORVALIDATION table
                (
                    ID uniqueidentifier,
                    DECLARATIONSTARTS datetime,
                    DECLARATIONENDS datetime,
                    CHARITYCLAIMREFERENCENUMBERID uniqueidentifier,
                    PAYSTAXCODE tinyint
                )

                if @BATCHTYPE = 0 -- Constituent batch

                    insert into @TAXDECLARATIONSFORVALIDATION (ID, DECLARATIONSTARTS, DECLARATIONENDS, CHARITYCLAIMREFERENCENUMBERID, PAYSTAXCODE)
                    select newid(), DECLARATIONSTARTS, DECLARATIONENDS, CHARITYCLAIMREFERENCENUMBERID, PAYSTAXCODE
                    from dbo.UFN_CONSTITUENTBATCH_GETTAXDECLARATIONS_FROMITEMLISTXML(@TAXDECLARATIONS)
                else -- Revenue batch

                    insert into @TAXDECLARATIONSFORVALIDATION (ID, DECLARATIONSTARTS, DECLARATIONENDS, CHARITYCLAIMREFERENCENUMBERID, PAYSTAXCODE)
                    select newid(), DECLARATIONSTARTS, DECLARATIONENDS, CHARITYCLAIMREFERENCENUMBERID, PAYSTAXCODE
                    from dbo.UFN_REVENUEBATCH_GETTAXDECLARATIONS_FROMITEMLISTXML(@TAXDECLARATIONS)

                if exists(    select 1 
                            from @TAXDECLARATIONSFORVALIDATION 
                            where
                                DECLARATIONSTARTS is not null and
                                DECLARATIONENDS is not null and
                                DECLARATIONENDS < DECLARATIONSTARTS)
                    raiserror('BBERR_INVALIDDECLARATIONSSTARTANDENDDATE', 13, 1)

                if exists ( select 1 from @TAXDECLARATIONSFORVALIDATION TOPTABLE
                            inner join 
                            (
                                select 
                                    DECLARATIONSTARTS,
                                    DECLARATIONENDS,
                                    CHARITYCLAIMREFERENCENUMBERID, 
                                    PAYSTAXCODE
                                from @TAXDECLARATIONSFORVALIDATION
                                union all
                                select
                                    DECLARATIONSTARTS,
                                    DECLARATIONENDS,
                                    CHARITYCLAIMREFERENCENUMBERID, 
                                    PAYSTAXCODE
                                from dbo.TAXDECLARATION
                                where
                                    @BATCHTYPE = 1 and -- Revenue batch

                                    CONSTITUENTID = @CONSTITUENTID
                            ) as SUBTABLE
                            on
                                (
                                    (
                                        TOPTABLE.CHARITYCLAIMREFERENCENUMBERID is null and 
                                        SUBTABLE.CHARITYCLAIMREFERENCENUMBERID is null
                                    ) or 
                                    TOPTABLE.CHARITYCLAIMREFERENCENUMBERID = SUBTABLE.CHARITYCLAIMREFERENCENUMBERID
                                ) and
                                TOPTABLE.PAYSTAXCODE <> SUBTABLE.PAYSTAXCODE and
                                dbo.UFN_DECLARATIONS_AREDATESOVERLAPPING(TOPTABLE.DECLARATIONSTARTS, TOPTABLE.DECLARATIONENDS, SUBTABLE.DECLARATIONSTARTS, SUBTABLE.DECLARATIONENDS) = 1
                            )
                    raiserror('BBERR_CONFLICTINGTAXDECLARATION', 13, 1)