USP_DATAFORMTEMPLATE_VIEW_DESIGNATION

The load procedure used by the view dataform template "Designation 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.
@DESIGNATIONREPORTCODE1	nvarchar(100)	INOUT	Report code 1
@DESIGNATIONREPORTCODE2	nvarchar(100)	INOUT	Report code 2
@USERID	nvarchar(512)	INOUT	Lookup ID
@VANITYNAME	nvarchar(512)	INOUT	Public name
@VSECATEGORY	nvarchar(1024)	INOUT	VSE category
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@DESIGNATIONUSECODE	nvarchar(100)	INOUT	Use code
@GIFTAIDQUALIFICATIONSTATUS	nvarchar(25)	INOUT	Gift Aid status
@VSESUBCATEGORY	nvarchar(1024)	INOUT	VSE subcategory
@REVENUEDESIGNATION	bit	INOUT	Revenue designation
@ISBASICCMS	bit	INOUT	Approved for website
@SITE	nvarchar(1024)	INOUT	Site

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DESIGNATION
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @DESIGNATIONREPORTCODE1 nvarchar(100) = null output,
                    @DESIGNATIONREPORTCODE2 nvarchar(100) = null output,
                    @USERID nvarchar(512) = null output,
                    @VANITYNAME nvarchar(512) = null output,
                    @VSECATEGORY nvarchar(1024) = null output,
                    @STARTDATE datetime = null output,
                    @ENDDATE datetime = null output,
                    @DESIGNATIONUSECODE nvarchar(100) = null output,
                    @GIFTAIDQUALIFICATIONSTATUS nvarchar(25) = null output,
                    @VSESUBCATEGORY nvarchar(1024) = null output,
                    @REVENUEDESIGNATION bit = null output,
                    @ISBASICCMS bit = null output,
                    @SITE nvarchar(1024) = null output
                )
                as
                begin
                    set nocount on;

                    set @DATALOADED = 0;

              declare @DESIGNATIONLEVELID uniqueidentifier;

                    select
                        @DATALOADED = 1,
                        @DESIGNATIONREPORTCODE1 = DR1.DESCRIPTION,
                        @DESIGNATIONREPORTCODE2 = DR2.DESCRIPTION,
                        @USERID = DESIGNATION.USERID,
                        @VANITYNAME = DESIGNATION.VANITYNAME,
                        @VSECATEGORY = dbo.UFN_TRANSLATIONFUNCTION_VSECATEGORY_GETNAME(DESIGNATION.VSECATEGORYID),
                        @STARTDATE = DESIGNATION.STARTDATE,
                        @ENDDATE = DESIGNATION.ENDDATE,
                @DESIGNATIONUSECODE = DUC.DESCRIPTION,
                @VSESUBCATEGORY = dbo.UFN_TRANSLATIONFUNCTION_VSESUBCATEGORY_GETNAME(DESIGNATION.VSESUBCATEGORYID),
                @REVENUEDESIGNATION = DESIGNATION.ISREVENUEDESIGNATION,
                @ISBASICCMS = ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0),
                @DESIGNATIONLEVELID = case
                        when DESIGNATION.DESIGNATIONLEVEL2ID is null then DESIGNATION.DESIGNATIONLEVEL1ID
                        when DESIGNATION.DESIGNATIONLEVEL3ID is null then DESIGNATION.DESIGNATIONLEVEL2ID
                        when DESIGNATION.DESIGNATIONLEVEL4ID is null then DESIGNATION.DESIGNATIONLEVEL3ID
                        when DESIGNATION.DESIGNATIONLEVEL5ID is null then DESIGNATION.DESIGNATIONLEVEL4ID
                        else DESIGNATION.DESIGNATIONLEVEL5ID
                    end
                    from dbo.DESIGNATION
                        left join dbo.DESIGNATIONREPORT1CODE DR1 on DESIGNATION.DESIGNATIONREPORT1CODEID = DR1.ID
                        left join dbo.DESIGNATIONREPORT2CODE DR2 on DESIGNATION.DESIGNATIONREPORT2CODEID = DR2.ID
                        left join dbo.DESIGNATIONUSECODE DUC on DESIGNATION.DESIGNATIONUSECODEID = DUC.ID
                        left join dbo.MICROSITEPAGE 
                            on (MICROSITEPAGE.OBJECTID = DESIGNATION.ID)
                            and (MICROSITEPAGE.EXCLUDED = 0)
                    where DESIGNATION.ID = @ID;        

                    --Gift Aid is for UK only

                    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                    begin
                      set @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_DESIGNATIONQUALIFICATIONSTATUS(@ID)
                    end

              select
                @SITE = COALESCE((select [NAME] from dbo.SITE where ID=DESIGNATIONLEVEL.SITEID), '')
                from
                    dbo.DESIGNATIONLEVEL
                where
                    ID = @DESIGNATIONLEVELID;

                    return 0;
                end