USP_DATAFORMTEMPLATE_VIEW_VOLUNTEERPROFILESCREENPLAN

The load procedure used by the view dataform template "Volunteer Profile Screening Plan 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.
@ISVOLUNTEERPROFILE	bit	INOUT	ISVOLUNTEERPROFILE
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@VOLUNTEERTYPES	nvarchar(4000)	INOUT	Volunteer types
@ISINACTIVE	bit	INOUT	ISINACTIVE
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@PICTURE	varbinary	INOUT	PICTURE
@VOLUNTEERSTARTDATE	datetime	INOUT	Volunteer since
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@ISDECEASED	bit	INOUT	ISDECEASED
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@ISORGANIZATION	bit	INOUT	ISORGANIZATION

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_VOLUNTEERPROFILESCREENPLAN(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ISVOLUNTEERPROFILE bit = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONETYPE nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @VOLUNTEERTYPES nvarchar(4000) = null output,
    @ISINACTIVE bit = null output,
    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
    @PICTURE varbinary(max) = null output,
    @VOLUNTEERSTARTDATE datetime = null output,
    @GIVESANONYMOUSLY bit = null output,
    @ISDECEASED bit = null output,
    @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
    @DONOTMAIL bit = null output,
    @DONOTEMAIL bit = null output,
    @DONOTPHONE bit = null output,
    @PHONEISCONFIDENTIAL bit = null output,
    @ADDRESSISCONFIDENTIAL bit = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONENUMBERID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @ISORGANIZATION bit = null output
    )
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @ISVOLUNTEERPROFILE = 1,
        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
        @DONOTMAIL = ADDRESS.DONOTMAIL,
        @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
        @ADDRESSID = ADDRESS.ID,        
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
        @PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID),
        @DONOTPHONE = PHONE.DONOTCALL,
        @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
        @PHONENUMBERID = PHONE.ID,
        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
        @EMAILADDRESSID = EMAILADDRESS.ID,
        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
        @VOLUNTEERTYPES = dbo.UFN_VOLUNTEERTYPES_ACTIVEFORVOLUNTEER(CONSTITUENT.ID),
        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
        @DECEASEDDATE = DECEASEDCONSTITUENT.DECEASEDDATE,
        @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @CONSTITUENTINACTIVITYREASON = 
            case when CONSTITUENT.ISINACTIVE = 1 then 
              (select dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
              from dbo.CONSTITUENTINACTIVEDETAIL
              where ID = CONSTITUENT.ID)
            else
                null
            end
    from dbo.VOLUNTEERSCREENPLAN
    inner join dbo.CONSTITUENT
        on CONSTITUENT.ID = VOLUNTEERSCREENPLAN.VOLUNTEERID
    left join dbo.ADDRESS
        on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and
        ADDRESS.ISPRIMARY = 1
    left join dbo.PHONE
        on PHONE.CONSTITUENTID = CONSTITUENT.ID and
        PHONE.ISPRIMARY = 1
    left join dbo.EMAILADDRESS
        on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and
        EMAILADDRESS.ISPRIMARY = 1
    left join dbo.DECEASEDCONSTITUENT
        on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    where VOLUNTEERSCREENPLAN.ID = @ID;

    select
        @VOLUNTEERSTARTDATE = min(DATEFROM)
    from
        dbo.VOLUNTEERSCREENPLAN
    inner join
        dbo.VOLUNTEERDATERANGE
    on
        VOLUNTEERDATERANGE.CONSTITUENTID = VOLUNTEERSCREENPLAN.VOLUNTEERID
    where
        VOLUNTEERSCREENPLAN.ID = @ID
    group by
        VOLUNTEERDATERANGE.CONSTITUENTID;

    return 0;