USP_DATAFORMTEMPLATE_VIEW_WEALTHINFORMATION_SUMMARY

The load procedure used by the view dataform template "Wealth Information Summary View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@EMAILADDRESS	nvarchar(100)	INOUT	EMAILADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@LOOKUPID	nvarchar(20)	INOUT	Lookup ID
@ISINACTIVE	bit	INOUT	ISINACTIVE
@PICTURE	varbinary	INOUT	PICTURE
@SPOUSE	nvarchar(100)	INOUT	Spouse
@SPOUSEID	uniqueidentifier	INOUT	SPOUSEID
@PRIMARYBUSINESS	nvarchar(100)	INOUT	Primary business
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	PRIMARYBUSINESSID
@PROSPECTMANAGER	nvarchar(100)	INOUT	Prospect manager
@PROSPECTMANAGERID	uniqueidentifier	INOUT	PROSPECTMANAGERID
@RESEARCHSTATUSCONFIRMED	bit	INOUT	RESEARCHSTATUSCONFIRMED
@WEALTHPOINTUPDATEPENDING	bit	INOUT	WEALTHPOINTUPDATEPENDING
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@ISHOUSEHOLD	bit	INOUT	Is Household
@ISGROUP	bit	INOUT	ISGROUP
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@HOUSEHOLD	nvarchar(100)	INOUT	Household
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@EDUCATIONATTRIBUTEDEFINED	bit	INOUT	EDUCATIONATTRIBUTEDEFINED
@PRIMARYEDUCATION	nvarchar(100)	INOUT	Primary education
@PRIMARYEDUCATIONID	uniqueidentifier	INOUT	PRIMARYEDUCATIONID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATELASTRESEARCHREQUESTCOMPLETED	datetime	INOUT	Last research request completed
@ISDECEASED	bit	INOUT	Is deceased
@ISSPOUSEDECEASED	bit	INOUT	Is spouse deceased
@EMAILADDRESSWEBUI	UDT_EMAILADDRESS	INOUT	EMAILADDRESSWEBUI
@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

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_WEALTHINFORMATION_SUMMARY]
(
    @ID                        uniqueidentifier,
    @ADDRESS                   nvarchar(300) =     null output,
    @EMAILADDRESS              nvarchar(100) =     null output,
    @PHONENUMBER               nvarchar(100) =     null output,
    @LOOKUPID                  nvarchar(20) =      null output,
    @ISINACTIVE                bit =               null output,
    @PICTURE                   varbinary(max) =    null output,    
    @SPOUSE                    nvarchar(100) =     null output,
    @SPOUSEID                   uniqueidentifier =  null output,
    @PRIMARYBUSINESS           nvarchar(100) =     null output,
    @PRIMARYBUSINESSID         uniqueidentifier =  null output,
    @PROSPECTMANAGER           nvarchar(100) =     null output,
    @PROSPECTMANAGERID         uniqueidentifier =  null output,
    @RESEARCHSTATUSCONFIRMED   bit =               null output,
    @WEALTHPOINTUPDATEPENDING  bit =               null output,
    @DATALOADED                bit =               0    output,
    @DECEASEDDATE               dbo.UDT_FUZZYDATE = null output,
    @ISORGANIZATION               bit =               null output,
    @ISHOUSEHOLD               bit =               null output,
    @ISGROUP                   bit =               null output,
    @GIVESANONYMOUSLY           bit =               null output,
    @HOUSEHOLD                 nvarchar(100) =     null output,
    @HOUSEHOLDID               uniqueidentifier =  null output,
    @EDUCATIONATTRIBUTEDEFINED bit =               null output,
    @PRIMARYEDUCATION          nvarchar(100) =     null output,
    @PRIMARYEDUCATIONID        uniqueidentifier =  null output,
    @CURRENTAPPUSERID           uniqueidentifier =  null,
    @DATELASTRESEARCHREQUESTCOMPLETED datetime =   null output,
    @ISDECEASED                   bit =               null output,
    @ISSPOUSEDECEASED           bit =               null output,
    @EMAILADDRESSWEBUI           dbo.UDT_EMAILADDRESS = null output, --Add new field since changing datatypes of parameter is a breaking change
    @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
)
as

    set NOCOUNT on;
    set @DATALOADED = 0;

    --CONSTITUENT FIELDS
    select
        @DATALOADED =        1,
        @PICTURE =            CONSTITUENT.PICTURETHUMBNAIL,
        @ISINACTIVE =        CONSTITUENT.ISINACTIVE,
        @LOOKUPID =            CONSTITUENT.LOOKUPID,
        @ISORGANIZATION =    CONSTITUENT.ISORGANIZATION,
        @ISGROUP =            CONSTITUENT.ISGROUP,
        @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY
    from
        dbo.CONSTITUENT
    where
        CONSTITUENT.ID = @ID;

    if @ISINACTIVE = 1
        select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
        from dbo.CONSTITUENTINACTIVEDETAIL
        where ID = @ID

    select 
        @ISHOUSEHOLD = case when G.GROUPTYPECODE = 0 then 1 else 0 end
    from dbo.GROUPDATA G
    where G.ID = @ID;

    --EMAIL FIELDS
    select
        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @EMAILADDRESSWEBUI = EMAILADDRESS.EMAILADDRESS,
        @DONOTEMAIL = DONOTEMAIL,
        @EMAILADDRESSID = ID
    from
        dbo.EMAILADDRESS
    where
        EMAILADDRESS.CONSTITUENTID = @ID and 
        EMAILADDRESS.ISPRIMARY = 1;

    --PHONE FIELDS
    select
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
        @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
        @DONOTPHONE = PHONE.DONOTCALL,
        @PHONENUMBERID = PHONE.ID
    from
        dbo.PHONE
    where 
        PHONE.CONSTITUENTID = @ID and
        PHONE.ISPRIMARY = 1;

    --ADDRESS FIELDS
    select 
        @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
    from
        dbo.ADDRESS
    where
        ADDRESS.CONSTITUENTID = @ID and
        ADDRESS.ISPRIMARY = 1;                    

    --SPOUSE FIELDS
    set @ISSPOUSEDECEASED = 0;

    select
        @SPOUSE =   NF_SPOUSE.[NAME],
        @SPOUSEID = SPOUSE.ID,
        @ISSPOUSEDECEASED = case when dbo.DECEASEDCONSTITUENT.ID is null then 0 else 1 end
    from
        dbo.RELATIONSHIP
    left outer join
        dbo.CONSTITUENT as SPOUSE
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
    on
        SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    left join
      dbo.DECEASEDCONSTITUENT
    on
      SPOUSE.ID = dbo.DECEASEDCONSTITUENT.ID
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
        RELATIONSHIP.ISSPOUSE = 1;

    --HOUSEHOLD FIELDS
    if @ISORGANIZATION = 0 and @ISGROUP = 0
    begin
        select top(1)
            @HOUSEHOLDID = CG.ID,
            @HOUSEHOLD = CG.NAME
        from dbo.GROUPMEMBER as GM
        join dbo.CONSTITUENT as CG on GM.GROUPID = CG.ID
        join dbo.GROUPDATA as GD on GD.ID = CG.ID
        where GM.MEMBERID = @ID
          and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
          and GD.GROUPTYPECODE = 0;
    end

    --PRIMARY EDUCATION FIELDS
    if @ISORGANIZATION = 0 and @ISGROUP = 0
    begin
        select
            @PRIMARYEDUCATIONID = EDUCATIONALHISTORY.ID,
            @PRIMARYEDUCATION = EDUCATIONALINSTITUTION.NAME,
            @EDUCATIONATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('EDUCATIONAL HISTORY',@CURRENTAPPUSERID)
        from
            dbo.EDUCATIONALHISTORY 
        inner join dbo.EDUCATIONALINSTITUTION on
            EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
        where EDUCATIONALHISTORY.ISPRIMARYRECORD = 1 and EDUCATIONALHISTORY.CONSTITUENTID = @ID;
    end

    --BUSINESS FIELDS
    if @ISORGANIZATION = 1
    begin
        select 
            @PRIMARYBUSINESS =   NF_CONTACT.[NAME],
            @PRIMARYBUSINESSID = CONTACT.ID
        from
            dbo.RELATIONSHIP
        left outer join
            dbo.CONSTITUENT as CONTACT
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONTACT.ID) NF_CONTACT
        on
            CONTACT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
            RELATIONSHIP.ISPRIMARYCONTACT = 1;


    end
    else 
    begin
        select 
            @PRIMARYBUSINESS =   BUSINESS.[NAME],
            @PRIMARYBUSINESSID = BUSINESS.ID
        from
            dbo.RELATIONSHIP
        left outer join
            dbo.CONSTITUENT as BUSINESS
        on
            BUSINESS.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
            RELATIONSHIP.ISPRIMARYBUSINESS = 1;
    end

    --PROSPECT STATUS FIELDS
    select
        @PROSPECTMANAGER =            NF_MANAGER.[NAME],
        @PROSPECTMANAGERID =          MANAGER.[ID],
        @RESEARCHSTATUSCONFIRMED =    PROSPECT.[RESEARCHSTATUSCONFIRMED]
    from
        dbo.[PROSPECT]
    left outer join
        dbo.[CONSTITUENT] as MANAGER
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MANAGER.ID) NF_MANAGER
    on  
        MANAGER.ID = PROSPECT.[PROSPECTMANAGERFUNDRAISERID]
    left outer join
        dbo.PROSPECTSTATUSCODE
    on    
        PROSPECTSTATUSCODE.ID = PROSPECT.[PROSPECTSTATUSCODEID]
    where 
        PROSPECT.ID = @ID

    --WEALTHPOINT STATUS FIELD
    if exists(select 1 from dbo.WEALTH where ID=@ID and PENDINGSEARCH=1) begin
        set @WEALTHPOINTUPDATEPENDING = 1;
    end else begin
        set @WEALTHPOINTUPDATEPENDING = 0;
    end

    --DECEASED
    SELECT 
    @DECEASEDDATE=DECEASEDDATE,
    @ISDECEASED = 1
    FROM
    dbo.DECEASEDCONSTITUENT
    WHERE ID=@ID;

    --LAST PROSPECT RESEARCH REQUEST COMPLETED DATE
    select top(1)
        @DATELASTRESEARCHREQUESTCOMPLETED = PRRC.DATECOMPLETED
    from
        dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC
    where    
        PRRC.CONSTITUENTID = @ID
    order by DATECOMPLETED desc

    return 0;