USP_DATAFORMTEMPLATE_VIEW_FEPSUBMITPROCESS

The load procedure used by the view dataform template "FEP Submit Process 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(100)	INOUT	NAME
@BUSINESSPROCESSCATALOGID	uniqueidentifier	INOUT	BUSINESSPROCESSCATALOGID
@PARAMETERSETID	uniqueidentifier	INOUT	PARAMETERSETID
@DATELASTRUN	datetime	INOUT	Last run on
@ORGANIZATIONNAME	nvarchar(100)	INOUT	Organization
@IDNUMBER	nvarchar(15)	INOUT	Identification number
@COUNTRY	nvarchar(100)	INOUT	Country
@POSTCODE	nvarchar(12)	INOUT	Zip
@CONTACTNAME	nvarchar(250)	INOUT	Contact person
@CONTACTEMAIL	nvarchar(100)	INOUT	Email address
@CONTACTPHONE	nvarchar(20)	INOUT	Phone
@AFFILIATIONS	nvarchar(100)	INOUT	Affiliations
@COMMENTS	nvarchar(250)	INOUT	Comments
@PERIOD1DATERANGE	nvarchar(50)	INOUT	Previous period
@PERIOD2DATERANGE	nvarchar(50)	INOUT	Current period

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FEPSUBMITPROCESS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
--    @DESCRIPTION nvarchar(250) = null output,

    @BUSINESSPROCESSCATALOGID uniqueidentifier = null output,
    @PARAMETERSETID uniqueidentifier = null output,
    @DATELASTRUN datetime = null output,    
    @ORGANIZATIONNAME nvarchar(100) = null output,
    @IDNUMBER nvarchar(15) = null output,
    @COUNTRY nvarchar(100) = null output,
    @POSTCODE nvarchar(12) = null output,
    @CONTACTNAME nvarchar(250) = null output,
    @CONTACTEMAIL nvarchar(100) = null output,
    @CONTACTPHONE nvarchar(20) = null output,
    @AFFILIATIONS nvarchar(100) = null output,
    @COMMENTS nvarchar(250) = null output,    
    @PERIOD1DATERANGE nvarchar(50) = null output,
    @PERIOD2DATERANGE nvarchar(50) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @AFP bit = 0;
    declare @CASE bit = 0;
    declare @AHP bit = 0;
    declare @ALDE bit = 0;
    declare @NCPG bit = 0;
    declare @CRD bit = 0;
    declare @OTHER bit = 0;
    declare @OTHERAFFILIATION nvarchar(50) = '';

    select 
        @DATALOADED = 1,
        @BUSINESSPROCESSCATALOGID = 'cad79b29-5309-4650-b452-d03f379bdc60',
        @PARAMETERSETID = FEPSUBMITPROCESS.ID,
        @DATELASTRUN = FEPPROCESS.DATELASTRUN,
        @NAME = coalesce(SITE.[NAME] + ' - ', '') + coalesce(FEPPROCESS.[NAME], ''),    
        @ORGANIZATIONNAME = FEPPROCESS.ORGANIZATIONNAME,
        @IDNUMBER = FEPPROCESS.IDNUMBER,
        @COUNTRY = dbo.UFN_COUNTRY_GETDESCRIPTION(FEPPROCESS.COUNTRYID),
        @POSTCODE = FEPPROCESS.POSTCODE,
        @CONTACTNAME = FEPPROCESS.CONTACTNAME,
        @CONTACTEMAIL = FEPPROCESS.CONTACTEMAIL,
        @CONTACTPHONE = FEPPROCESS.CONTACTPHONE,
        @AFP = FEPPROCESS.AFP,
        @CASE = FEPPROCESS.[CASE],
        @AHP = FEPPROCESS.AHP,
        @ALDE = FEPPROCESS.ALDE,
        @NCPG = FEPPROCESS.NCPG,
        @CRD = FEPPROCESS.CRD,
        @OTHER = FEPPROCESS.OTHER,
        @OTHERAFFILIATION = FEPPROCESS.OTHERAFFILIATION,
        @COMMENTS = FEPPROCESS.COMMENTS,    
        @PERIOD1DATERANGE = coalesce(convert(nvarchar(10), FEPSUBMITPROCESS.PERIOD1STARTDATE, 101) + ' - ', '') + coalesce(convert(nvarchar(10), FEPSUBMITPROCESS.PERIOD1ENDDATE, 101), ''),
        @PERIOD2DATERANGE = coalesce(convert(nvarchar(10), FEPSUBMITPROCESS.PERIOD2STARTDATE, 101) + ' - ', '') + coalesce(convert(nvarchar(10), FEPSUBMITPROCESS.PERIOD2ENDDATE, 101), '')
    from dbo.FEPSUBMITPROCESS
    inner join dbo.FEPPROCESS 
        on FEPSUBMITPROCESS.FEPPROCESSID = FEPPROCESS.ID
    left outer join dbo.SITE
        on FEPPROCESS.SITEID = SITE.ID
    where
        FEPSUBMITPROCESS.ID = @ID;

    if @AFP = 1
        set @AFFILIATIONS = 'AFP, ';

    if @CASE = 1
        set @AFFILIATIONS = @AFFILIATIONS + 'CASE, ';

    if @AHP = 1
        set @AFFILIATIONS = @AFFILIATIONS + 'AHP, ';

    if @ALDE = 1
        set @AFFILIATIONS = @AFFILIATIONS + 'ALDE, ';

    if @NCPG = 1
        set @AFFILIATIONS = @AFFILIATIONS + 'NCPG, ';

    if @CRD = 1
        set @AFFILIATIONS = @AFFILIATIONS + 'CRD, ';

    if @OTHER = 1 and len(@OTHERAFFILIATION) > 0
        set @AFFILIATIONS = @AFFILIATIONS + @OTHERAFFILIATION + ', '

    if len(@AFFILIATIONS) > 2
        set @AFFILIATIONS = left(@AFFILIATIONS, len(@AFFILIATIONS) - 2)

    return 0;