USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDECLARATION

The load procedure used by the view dataform template "Constituent Declaration View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SITE	nvarchar(1024)	INOUT	Site
@DECLARATIONMADE	datetime	INOUT	Made
@DECLARATIONSTARTS	datetime	INOUT	Start date
@DECLARATIONENDS	datetime	INOUT	End date
@DECLARATIONINDICATOR	nvarchar(100)	INOUT	Indicator
@DECLARATIONSOURCECODE	nvarchar(100)	INOUT	Source
@CONFIRMATIONSENT	datetime	INOUT	Confirmation sent
@CONFIRMATIONRETURNED	datetime	INOUT	Confirmation returned
@SCANNEDDOCSEXIST	bit	INOUT	Scanned documents exist
@PAYSTAX	nvarchar(10)	INOUT	Pays tax
@TAXSTATUSCODE	nvarchar(100)	INOUT	Status
@COMMENTS	nvarchar(255)	INOUT	Comment
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@DESCRIPTION	nvarchar(50)	INOUT	Description
@NAMEOPTIONCODE	tinyint	INOUT
@ALIASTYPE	nvarchar(100)	INOUT
@NAME	nvarchar(200)	INOUT
@ADDRESSOPTIONCODE	tinyint	INOUT
@ADDRESSTYPE	nvarchar(100)	INOUT
@ADDRESS	nvarchar(300)	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDECLARATION (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @SITE nvarchar(1024) = null output,
                    @DECLARATIONMADE datetime = null output,
                    @DECLARATIONSTARTS datetime = null output,
                    @DECLARATIONENDS datetime = null output,
                    @DECLARATIONINDICATOR nvarchar(100) = null output,
                    @DECLARATIONSOURCECODE nvarchar(100) = null output,
                    @CONFIRMATIONSENT datetime = null output,
                    @CONFIRMATIONRETURNED datetime = null output,
                    @SCANNEDDOCSEXIST bit = null output,
                    @PAYSTAX nvarchar(10) = null output,
                    @TAXSTATUSCODE nvarchar(100) = null output,
                    @COMMENTS nvarchar(255) = null output,
                    @REFERENCENUMBER nvarchar(20) = null output,
                    @DESCRIPTION nvarchar(50) = null output,
                    @NAMEOPTIONCODE tinyint = null output,
                    @ALIASTYPE nvarchar(100) = null output,
                    @NAME nvarchar(200) = null output,
                    @ADDRESSOPTIONCODE tinyint = null output,
                    @ADDRESSTYPE nvarchar(100) = null output,
                    @ADDRESS nvarchar(300) = null output
                ) as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    select
                        @DATALOADED = 1,
                        @SITE = '',
                        @DECLARATIONMADE = dbo.TAXDECLARATION.DECLARATIONMADE,
                        @DECLARATIONSTARTS = dbo.TAXDECLARATION.DECLARATIONSTARTS,
                        @DECLARATIONENDS = dbo.TAXDECLARATION.DECLARATIONENDS,
                        @DECLARATIONINDICATOR = dbo.TAXDECLARATION.DECLARATIONINDICATOR,
                        @DECLARATIONSOURCECODE = dbo.DECLARATIONSOURCECODE.DESCRIPTION,
                        @CONFIRMATIONSENT = dbo.TAXDECLARATION.CONFIRMATIONSENT,
                        @CONFIRMATIONRETURNED = dbo.TAXDECLARATION.CONFIRMATIONRETURNED,
                        @SCANNEDDOCSEXIST = dbo.TAXDECLARATION.SCANNEDDOCSEXIST,
                        @PAYSTAX = dbo.TAXDECLARATION.PAYSTAX,
                        @TAXSTATUSCODE = dbo.TAXSTATUSCODE.DESCRIPTION,
                        @COMMENTS = dbo.TAXDECLARATION.COMMENTS,
                        @REFERENCENUMBER = CHARITYCLAIMREFERENCENUMBER.REFERENCENUMBER,
                        @DESCRIPTION = CHARITYCLAIMREFERENCENUMBER.DESCRIPTION,
                        @NAMEOPTIONCODE = case when TAXDECLARATION.ALIASID is null then 0 else 1 end,
                        @ALIASTYPE = ALIASTYPECODE.DESCRIPTION,
                        @NAME = case when TAXDECLARATION.ALIASID is null then CONSTITUENT.NAME else ALIAS.NAME end,
                        @ADDRESSTYPE = case
                            when ((ADDRESS.HISTORICALENDDATE is null) or (ADDRESS.HISTORICALENDDATE > @CURRENTDATE))
                                then coalesce(ADDRESSTYPECODE.DESCRIPTION, N'') + N' (Current)'
                            else
                                coalesce(ADDRESSTYPECODE.DESCRIPTION, N'') + N' (Former)'
                        end,
                        @ADDRESSOPTIONCODE = case when TAXDECLARATION.ADDRESSID is null then 0 else 1 end,
                        @ADDRESS = dbo.UFN_ADDRESS_TRANSLATE(ADDRESS.ID)
                    from
                        dbo.TAXDECLARATION
                    inner join dbo.CHARITYCLAIMREFERENCENUMBER on CHARITYCLAIMREFERENCENUMBER.ID = TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID
                    left join dbo.DECLARATIONSOURCECODE on DECLARATIONSOURCECODE.ID = TAXDECLARATION.DECLARATIONSOURCECODEID
                    left join dbo.TAXSTATUSCODE on TAXSTATUSCODE.ID = TAXDECLARATION.TAXSTATUSCODEID
                    left join dbo.CONSTITUENT on CONSTITUENT.ID = TAXDECLARATION.CONSTITUENTID
                    left join dbo.ALIAS on ALIAS.ID = TAXDECLARATION.ALIASID
                    left join dbo.ALIASTYPECODE on ALIASTYPECODE.ID = ALIAS.ALIASTYPECODEID
                    left join dbo.ADDRESS on
                        (ADDRESS.ID = TAXDECLARATION.ADDRESSID) or
                        (TAXDECLARATION.ADDRESSID is null and ADDRESS.CONSTITUENTID = TAXDECLARATION.CONSTITUENTID and ADDRESS.ISPRIMARY = 1)
                    left join dbo.ADDRESSTYPECODE on ADDRESSTYPECODE.ID = ADDRESS.ADDRESSTYPECODEID
                    where
                        TAXDECLARATION.ID = @ID;

                    return 0;