USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPLOCATION

The load procedure used by the view dataform template "Sponsorship Location Details 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.
@NAME	nvarchar(100)	INOUT	Location
@COMMENT	nvarchar(255)	INOUT	Comments
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@DESIGNATIONID	uniqueidentifier	INOUT	Designation
@DATECHANGED	date	INOUT	Date changed
@CHANGEDBY	nvarchar(128)	INOUT	Changed by
@FIELDOFFICE	nvarchar(154)	INOUT	Field office
@DISPLAYONLINE	bit	INOUT	Show this location to online users

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPLOCATION
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    --@STATUS nvarchar(100) = null output,

                    @COMMENT nvarchar(255) = null output,
                    --@PARENTID uniqueidentifier = null output,

                    --@SPONSORSHIPLOCATIONID uniqueidentifier = null output,

                    @LOOKUPID nvarchar(100) = null output,
                    --,@PARENTLOCATION nvarchar(100) = null output

                    @DESIGNATIONID uniqueidentifier = null output,
                    @DATECHANGED date = null output,
                    @CHANGEDBY nvarchar(128) = null output,
                    @FIELDOFFICE nvarchar(154) = null output,
                    @DISPLAYONLINE bit = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                select 
                    @DATALOADED = 1,
                    --@PARENTLOCATION = (select PARENTLOCATION.NAME from dbo.SPONSORSHIPLOCATION as PARENTLOCATION where PARENTLOCATION.HIERARCHYPATH = SPONSORSHIPLOCATION.HIERARCHYPATH.GetAncestor(1)),

                    --@SPONSORSHIPLOCATIONID = SPONSORSHIPLOCATION.ID,

                    @NAME = SPONSORSHIPLOCATION.NAME,--dbo.UFN_SPONSORSHIPLOCATION_FULLSTRING(@ID,' > ',0,0),

                    --@STATUS = SPONSORSHIPLOCATION.STATUS,

                    @COMMENT = SPONSORSHIPLOCATION.COMMENT,
                    --@PARENTID = (select PARENTLOCATION.ID from dbo.SPONSORSHIPLOCATION as PARENTLOCATION where PARENTLOCATION.HIERARCHYPATH = SPONSORSHIPLOCATION.HIERARCHYPATH.GetAncestor(1)),

                    @LOOKUPID = SPONSORSHIPLOCATION.LOOKUPID,
                    @DESIGNATIONID = SPONSORSHIPLOCATION.DESIGNATIONID,
                    @DATECHANGED = SPONSORSHIPLOCATION.DATECHANGED,
                    @CHANGEDBY = (select USERNAME from CHANGEAGENT where ID = SPONSORSHIPLOCATION.CHANGEDBYID),
                    @FIELDOFFICE = (select NAME from dbo.CONSTITUENT c where SPONSORSHIPLOCATION.FIELDOFFICEID = c.ID),
                    @DISPLAYONLINE = SPONSORSHIPLOCATION.DISPLAYONLINE
                from
                    dbo.SPONSORSHIPLOCATION
                inner join
                    dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
                inner join
                    dbo.SPONSORSHIPLOCATION as ROOT on ROOT.HIERARCHYPATH = SPONSORSHIPLOCATION.HIERARCHYPATH.GetAncestor(SPONSORSHIPLOCATION.HIERARCHYPATH.GetLevel()-1)
                left outer join
                    dbo.DESIGNATION on DESIGNATION.ID = SPONSORSHIPLOCATION.DESIGNATIONID
                left outer join
                    dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = SPONSORSHIPLOCATION.SPONSORSHIPREASONID
                    WHERE SPONSORSHIPLOCATION.ID = @ID;

                return 0;