USP_DATAFORMTEMPLATE_VIEW_EXTENDEDRELATIONSHIP_NPA

The load procedure used by the view dataform template "Extended Relationship Nonprofit Affiliation 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_NPA
                (
                    @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 @NPAID uniqueidentifier
                    declare @RELATIONSHIPID uniqueidentifier
                    declare @WPRELATIONSHIP_NPA_ID uniqueidentifier
                    declare @BBDSEXECCOUNT integer

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

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

                    select 
                        @DATALOADED = 1,
                        @SOURCE = NPA.SOURCE + ' (Nonprofit affiliation)',
                        @DESCRIPTION = NPA.DESCRIPTION,
                        @PHONE = NPA.PHONE,
                        @ORGFORMYEAR = NPA.ORGFORMYEAR,
                        @ORGADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(NPA.LINE1, '', NPA.CITY, NPA.STATE, NPA.ZIP),
                        @LOCATION = case
                            when @RELATIONSHIPID is null then dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS('', '', NPA.HCITY, NPA.HSTATE, NPA.HZIP)
                            else dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS('', '', WPNPA.CITY, WPNPA.STATE, WPNPA.ZIP)
                            end,
                        @FORMYEARS =case
                            when @RELATIONSHIPID is null then cast(NPA.FORMYEAR as nvarchar(100))
                            else WPNPA.YROBSERVE
                            end,
                        @FULLNAME = case
                            when @RELATIONSHIPID is null then NPA.FULLNAME
                            else WPNPA.FULLNAME
                            end,
                        @BBDSEXECCOUNT = WPEIN.EXECCOUNT,
                        @WPRELATIONSHIP_NPA_ID = WPEIN.ID
                    from 
                        dbo.WPNONPROFITAFFILIATION NPA
                    left join dbo.WPRELATIONSHIP_NPA_IND WPNPA
                        on WPNPA.ID = @RELATIONSHIPID
                    left join dbo.WPRELATIONSHIP_NPA WPEIN
                        on NPA.WPRELATIONSHIP_NPA_ID = WPEIN.ID
                    where NPA.ID = @NPAID

          select
            @EXECCOUNT = COUNT(ID)
          from 
            dbo.WPRELATIONSHIP_NPA_IND
          where
            WPRELATIONSHIP_NPA_ID = @WPRELATIONSHIP_NPA_ID

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

                    return 0;