USP_DATAFORMTEMPLATE_VIEW_PHONE

The load procedure used by the view dataform template "Phone 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.
@PHONETYPECODE	nvarchar(100)	INOUT	Type
@PHONENUMBER	nvarchar(100)	INOUT	Number
@PRIMARY	bit	INOUT	Primary
@DONOTCALL	bit	INOUT	Do not call
@DONOTCALLREASONCODE	nvarchar(100)	INOUT	Reason
@STARTTIME	UDT_HOURMINUTE	INOUT	Call after
@ENDTIME	UDT_HOURMINUTE	INOUT	Call before
@INFOSOURCECODE	nvarchar(100)	INOUT	Information source
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@STARTDATE	date	INOUT	Start date
@ENDDATE	date	INOUT	End date
@DATEADDED	date	INOUT	Date added
@ISCONFIDENTIAL	bit	INOUT	Confidential
@COUNTRY	nvarchar(100)	INOUT	Country
@ISCONTACT	bit	INOUT	ISCONTACT
@ORGNAME	nvarchar(154)	INOUT	Organization
@JOBPOSITION	nvarchar(100)	INOUT	Position
@JOBDEPARTMENT	nvarchar(100)	INOUT	Department
@JOBDIVISION	nvarchar(100)	INOUT	Division
@ORIGINCODE	tinyint	INOUT	Origin
@SEASONALSTARTDATE	UDT_MONTHDAY	INOUT	Start date
@SEASONALENDDATE	UDT_MONTHDAY	INOUT	End date
@DONOTTEXT	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PHONE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PHONETYPECODE nvarchar(100) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PRIMARY bit = null output,
    @DONOTCALL bit = null output,
    @DONOTCALLREASONCODE nvarchar(100) = null output,
    @STARTTIME dbo.UDT_HOURMINUTE = null output,
    @ENDTIME dbo.UDT_HOURMINUTE = null output,
    @INFOSOURCECODE nvarchar(100) = null output,
    @INFOSOURCECOMMENTS nvarchar(256) = null output,
    @STARTDATE date = null output,
    @ENDDATE date = null output,
    @DATEADDED date = null output,
    @ISCONFIDENTIAL bit = null output,
    @COUNTRY nvarchar(100) = null output,
    @ISCONTACT bit = null output,
    @ORGNAME nvarchar(154) = null output,
    @JOBPOSITION nvarchar(100) = null output,
    @JOBDEPARTMENT nvarchar(100) = null output,
    @JOBDIVISION nvarchar(100) = null output,
    @ORIGINCODE tinyint = null output,
    @SEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
    @SEASONALENDDATE dbo.UDT_MONTHDAY = null output,
    @DONOTTEXT bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @ISCONTACT = 0;

    declare @RELATIONSHIPID uniqueidentifier;

    select 
        @DATALOADED = 1,
        @PHONETYPECODE = (select DESCRIPTION from dbo.PHONETYPECODE where ID = PHONETYPECODEID),
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(COUNTRYID, NUMBER),
        @PRIMARY = ISPRIMARY,
        @DONOTCALL = DONOTCALL,
        @DONOTCALLREASONCODE = '(' + (select DESCRIPTION from dbo.DONOTCALLREASONCODE where ID = DONOTCALLREASONCODEID) + ')',
        @STARTTIME = STARTTIME,
        @ENDTIME = ENDTIME,
        @INFOSOURCECODE = (select DESCRIPTION from dbo.INFOSOURCECODE where ID = INFOSOURCECODEID),
        @INFOSOURCECOMMENTS = INFOSOURCECOMMENTS,
        @STARTDATE = STARTDATE,
        @ENDDATE = ENDDATE,
        @DATEADDED = DATEADDED,
        @ISCONFIDENTIAL = ISCONFIDENTIAL,
        @COUNTRY = dbo.UFN_COUNTRY_GETDESCRIPTION(COUNTRYID),
        @RELATIONSHIPID = RELATIONSHIPID,
        @ORIGINCODE = ORIGINCODE,
        @SEASONALSTARTDATE = SEASONALSTARTDATE,
        @SEASONALENDDATE = SEASONALENDDATE,
        @DONOTTEXT = DONOTTEXT
    from dbo.PHONE
    where ID = @ID;

    if @RELATIONSHIPID is not null
    begin
        select 
            @ISCONTACT = r.ISCONTACT,
            @ORGNAME = org.NAME,
            @JOBPOSITION = job.JOBTITLE,
            @JOBDEPARTMENT = job.JOBDEPARTMENT,
            @JOBDIVISION = job.JOBDIVISION
        from CONSTITUENT org
        inner join RELATIONSHIP r 
            on r.RELATIONSHIPCONSTITUENTID = org.ID
        left outer join dbo.RELATIONSHIPJOBINFO job
            on r.RELATIONSHIPSETID = job.RELATIONSHIPSETID
        where
            r.ID=@RELATIONSHIPID and
            r.ISCONTACT=1 and
            org.ISORGANIZATION=1;
    end

    return 0;