USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITORGRELATIONSHIPS

The load procedure used by the view dataform template "NetCommunity Constituent Organization Relationship Data 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(200)	INOUT	Organization Name
@RELATIONSHIPTYPE	nvarchar(200)	INOUT	Relationship Type
@RECIPROCALRELATIONSHIPTYPE	nvarchar(200)	INOUT	Reciprocal Relationship Type
@STARTDATE	datetime	INOUT	Start Date
@ENDDATE	datetime	INOUT	End Date
@ISMATCHINGGIFTRELATIONSHIP	bit	INOUT	Is Matching Gift Relationship
@ISCONTACT	bit	INOUT	Is Contact
@CONTACTTYPE	nvarchar(200)	INOUT	Contact Type
@COMMENTS	nvarchar(max)	INOUT	Comments
@POSITION	nvarchar(200)	INOUT	Position
@INDUSTRYCODE	nvarchar(200)	INOUT	Industry
@CAREERLEVEL	nvarchar(200)	INOUT	Career Level
@PROFESSION	nvarchar(200)	INOUT	Profession
@SCHEDULE	nvarchar(200)	INOUT	Schedule
@DEPARTMENT	nvarchar(200)	INOUT	Department
@DIVISION	nvarchar(200)	INOUT	Division

Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITORGRELATIONSHIPS
        (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(200) = null output,
          @RELATIONSHIPTYPE nvarchar(200) = null output,
          @RECIPROCALRELATIONSHIPTYPE nvarchar(200) = null output,
          @STARTDATE datetime = null output,
          @ENDDATE datetime = null output,
          @ISMATCHINGGIFTRELATIONSHIP bit = null output,
          @ISCONTACT bit = null output,
          @CONTACTTYPE nvarchar(200) = null output,
          @COMMENTS nvarchar(max) = null output,
          @POSITION nvarchar(200) = null output,
          @INDUSTRYCODE nvarchar(200) = null output,
          @CAREERLEVEL nvarchar(200) = null output,
          @PROFESSION nvarchar(200) = null output,
          @SCHEDULE nvarchar(200) = null output,
          @DEPARTMENT nvarchar(200) = null output,
          @DIVISION nvarchar(200) = null output
                )
            as
                    set nocount on;
                    set @DATALOADED = 0;             

      declare @RELATIONSHIPID uniqueidentifier;

      select @RELATIONSHIPID = [ID] from dbo.RELATIONSHIP where RELATIONSHIP.ID = @ID;

      if @RELATIONSHIPID is null
        select @RELATIONSHIPID = REL.ID from dbo.CONSTITUENT as CONST
                inner join dbo.RELATIONSHIP as REL ON REL.RELATIONSHIPCONSTITUENTID = CONST.ID
                inner join dbo.CONSTITUENT as EMPLOYER on REL.RECIPROCALCONSTITUENTID = EMPLOYER.ID and EMPLOYER.ISORGANIZATION = 1           
        inner join dbo.RELATIONSHIPJOBINFO RJI on RJI.RELATIONSHIPSETID = REL.RELATIONSHIPSETID
        where RJI.ID = @ID;

      declare @T table(NAME nvarchar(100),
          RELATIONSHIPTYPE nvarchar(200),
          RECIPROCALRELATIONSHIPTYPE nvarchar(200),
          STARTDATE datetime,
          ENDDATE datetime,
          ISMATCHINGGIFTRELATIONSHIP bit,
          ISCONTACT bit,
          CONTACTTYPE nvarchar(200),
          COMMENTS nvarchar(max),
          POSITION nvarchar(200),
          [INDUSTRYCODE] nvarchar(200),
          CAREERLEVEL nvarchar(200),
          PROFESSION nvarchar(200),
          SCHEDULE nvarchar(200),
          DEPARTMENT nvarchar(200),
          DIVISION nvarchar(200)
                                     );
                    insert into @T 
                    (
                        NAME,
            RELATIONSHIPTYPE,
            RECIPROCALRELATIONSHIPTYPE,
            STARTDATE,
            ENDDATE,
            ISMATCHINGGIFTRELATIONSHIP,
            ISCONTACT,
            CONTACTTYPE,
            COMMENTS,
            POSITION,
            [INDUSTRYCODE],
            CAREERLEVEL,
            PROFESSION,
            SCHEDULE,
            DEPARTMENT,
            DIVISION
                    )
                    select      
                    EMPLOYER.KEYNAME as NAME,
          [dbo].[UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION](REL.RELATIONSHIPTYPECODEID) as RELATIONSHIPTYPE,
          [dbo].[UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION](REL.RECIPROCALTYPECODEID) as RECIPROCALRELATIONSHIPTYPE,
          REL.STARTDATE,
                    REL.ENDDATE,
          REL.ISMATCHINGGIFTRELATIONSHIP,
          REL.ISCONTACT,
          [dbo].[UFN_CONTACTTYPECODE_GETDESCRIPTION](REL.CONTACTTYPECODEID) as CONTACTTYPE,
          REL.COMMENTS,
                    RJI.JOBTITLE as POSITION,
          INDUSTRYCODE.DESCRIPTION as [INDUSTRYCODE],
          CAREERCODE.DESCRIPTION as CAREERLEVEL,   
                    JOBCODE.DESCRIPTION as PROFESSION,
          SCHEDULECODE.DESCRIPTION AS SCHEDULE,
          RJI.JOBDEPARTMENT AS DEPARTMENT,
          RJI.JOBDIVISION AS DIVISION
                 FROM dbo.CONSTITUENT as CONST
                INNER JOIN dbo.RELATIONSHIP as REL ON REL.RELATIONSHIPCONSTITUENTID = CONST.ID
                INNER JOIN dbo.CONSTITUENT as EMPLOYER on REL.RECIPROCALCONSTITUENTID = EMPLOYER.ID and EMPLOYER.ISORGANIZATION = 1 
                LEFT JOIN dbo.RELATIONSHIPJOBINFO as RJI ON RJI.RELATIONSHIPSETID = REL.RELATIONSHIPSETID
        LEFT JOIN dbo.ORGANIZATIONDATA as EMPLOYERDATA on EMPLOYER.ID = EMPLOYERDATA.ID
                LEFT JOIN dbo.JOBCATEGORYCODE as JOBCODE ON JOBCODE.ID = RJI.JOBCATEGORYCODEID
                LEFT JOIN dbo.CAREERLEVELCODE as CAREERCODE ON CAREERCODE.ID = RJI.CAREERLEVELCODEID
                LEFT JOIN dbo.JOBSCHEDULECODE as SCHEDULECODE ON SCHEDULECODE.ID = RJI.JOBSCHEDULECODEID
                LEFT JOIN dbo.INDUSTRYCODE ON INDUSTRYCODE.ID = EMPLOYERDATA.INDUSTRYCODEID
        where REL.ID = @RELATIONSHIPID;

                    select top 1 
            @DATALOADED = 1,
            @NAME = [NAME],
            @RELATIONSHIPTYPE = [RELATIONSHIPTYPE],
            @RECIPROCALRELATIONSHIPTYPE = [RECIPROCALRELATIONSHIPTYPE],
            @STARTDATE = [STARTDATE],
            @ENDDATE = [ENDDATE],
            @ISMATCHINGGIFTRELATIONSHIP = [ISMATCHINGGIFTRELATIONSHIP],
            @ISCONTACT = [ISCONTACT],
            @CONTACTTYPE = [CONTACTTYPE],
            @COMMENTS = [COMMENTS],
            @POSITION = [POSITION],
            @INDUSTRYCODE = [INDUSTRYCODE],
            @CAREERLEVEL = [CAREERLEVEL],
            @PROFESSION = [PROFESSION],
            @SCHEDULE = [SCHEDULE],
            @DEPARTMENT = [DEPARTMENT],
            @DIVISION = [DIVISION]
                    from @T;

                    return 0;