USP_DATAFORMTEMPLATE_VIEW_EXTENDEDRELATIONSHIP_PF

The load procedure used by the view dataform template "Extended Relationship Private Foundation View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(72)	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.
@SOURCE	nvarchar(100)	INOUT	Source
@DESCRIPTION	nvarchar(200)	INOUT	Description
@PHONE	nvarchar(20)	INOUT	Phone
@ORGFORMYEAR	UDT_YEAR	INOUT	Org. form year
@ORGADDRESS	nvarchar(300)	INOUT	Organization address
@LOCATION	nvarchar(300)	INOUT	Relationship's location
@FORMYEARS	nvarchar(100)	INOUT	Relationship's form year(s)
@FULLNAME	nvarchar(300)	INOUT	Relationship's name
@EXECCOUNT	int	INOUT
@MOREEXEC	bit	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EXTENDEDRELATIONSHIP_PF
                (
                    @ID nvarchar(72),
                    @DATALOADED bit = 0 output,
                    @SOURCE nvarchar(100) = null output,
                    @DESCRIPTION nvarchar(200) = null output,
                    @PHONE nvarchar(20) = null output,
                    @ORGFORMYEAR dbo.UDT_YEAR = null output,
                    @ORGADDRESS nvarchar(300) = null output,
                    @LOCATION nvarchar(300) = null output,
                    @FORMYEARS nvarchar(100) = null output,
                    @FULLNAME nvarchar(300) = null output,
                    @EXECCOUNT int = null output,
                    @MOREEXEC bit = null output
                )
                as
                    set nocount on;

                    declare @PFID uniqueidentifier
                    declare @RELATIONSHIPID uniqueidentifier
                    declare @WPRELATIONSHIP_PF_ID uniqueidentifier
                    declare @BBDSEXECCOUNT integer

                    set @DATALOADED = 0;
                    set @BBDSEXECCOUNT = 0;
                    set @EXECCOUNT = 0;
                    set @MOREEXEC = 0;

                    if LEN(@ID) = 36
                        select @PFID = substring(@ID, 1, 36)
                    else if LEN(@ID) = 72
                        select
                            @PFID = substring(@ID, 1, 36),
                            @RELATIONSHIPID = substring(@ID, 37, 36)

                    select 
                        @DATALOADED = 1,
                        @SOURCE = PF.SOURCE + ' (Foundation affiliation)',
                        @DESCRIPTION = PF.DESCRIPTION,
                        @PHONE = PF.PHONE,
                        @ORGFORMYEAR = PF.ORGFORMYEAR,
                        @ORGADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PF.LINE1, '', PF.CITY, PF.STATE, PF.ZIP),
                        @LOCATION = case
                            when @RELATIONSHIPID is null then dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS('', '', PF.HCITY, PF.HSTATE, PF.HZIP)
                            else dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS('', '', WPPF.CITY, WPPF.STATE, WPPF.ZIP)
                            end,
                        @FORMYEARS = case
                            when @RELATIONSHIPID is null then cast(PF.FORMYEAR as nvarchar(100))
                            else WPPF.YROBSERVE
                            end,
                        @FULLNAME = case
                            when @RELATIONSHIPID is null then PF.FULLNAME
                            else WPPF.FULLNAME
                            end,
                        @BBDSEXECCOUNT = WPEIN.EXECCOUNT,
                        @WPRELATIONSHIP_PF_ID = WPEIN.ID
                    from 
                        dbo.WPPRIVATEFOUNDATION PF
                    left join dbo.WPRELATIONSHIP_PF_IND WPPF
                        on WPPF.ID = @RELATIONSHIPID
                    left join dbo.WPRELATIONSHIP_PF WPEIN
                        on PF.WPRELATIONSHIP_PF_ID = WPEIN.ID
                    where PF.ID = @PFID

          select
            @EXECCOUNT = COUNT(ID)
          from 
            dbo.WPRELATIONSHIP_PF_IND
          where
            WPRELATIONSHIP_PF_ID = @WPRELATIONSHIP_PF_ID

          set @MOREEXEC = case when @EXECCOUNT < @BBDSEXECCOUNT then 1 else 0 end;

                    return 0;