USP_FENXT_MERGEPROJECTS

Parameters

Parameter	Parameter Type	Mode
@PDACCOUNTSYSTEMID	uniqueidentifier	IN
@PROJECTID	int	IN
@DESCRIPTION	nvarchar(200)	IN
@UIPROJECTID	nvarchar(20)	IN
@TYPE	nvarchar(50)	IN
@PREVENTDATAENTRY	bit	IN
@PREVENTPOSTINGAFTER	bit	IN
@POSTINGDATE	date	IN
@DATEMODIFIED	datetime	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
Definition

Copy


create procedure dbo.USP_FENXT_MERGEPROJECTS
    (
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @PROJECTID int,
    @DESCRIPTION nvarchar(200),
    @UIPROJECTID nvarchar(20),
    @TYPE nvarchar(50) = '',
    @PREVENTDATAENTRY bit,
    @PREVENTPOSTINGAFTER bit,
    @POSTINGDATE date = null,
    @DATEMODIFIED datetime = null,
    @STARTDATE date = null,
    @ENDDATE date = null
    )
    as

        declare @CHANGEAGENTID uniqueidentifier;
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

        merge
        into dbo.FENXTPROJECT
        using (select
                @PDACCOUNTSYSTEMID as PDACCOUNTSYSTEMID,
                @PROJECTID as PROJECTID,
                @DESCRIPTION as DESCRIPTION,
                @UIPROJECTID as UIPROJECTID,
                @TYPE as TYPE,
                @PREVENTDATAENTRY as PREVENTDATAENTRY,
                @PREVENTPOSTINGAFTER as PREVENTPOSTINGAFTER,
                @POSTINGDATE as POSTINGDATE,
                @DATEMODIFIED as DATEMODIFIED,
                @STARTDATE as STARTDATE,
                @ENDDATE as ENDDATE,
                @CHANGEAGENTID as CHANGEAGENTID,
                @CURRENTDATE as CURRENTDATE) as source
        on FENXTPROJECT.PROJECTID = source.PROJECTID 
            and FENXTPROJECT.PDACCOUNTSYSTEMID = source.PDACCOUNTSYSTEMID 
        when matched then
            update
                set DESCRIPTION = source.DESCRIPTION,
                UIPROJECTID = source.UIPROJECTID,
                TYPE = source.TYPE,
                PREVENTDATAENTRY = source.PREVENTDATAENTRY,
                PREVENTPOSTINGAFTER = source.PREVENTPOSTINGAFTER,
                POSTINGDATE = source.POSTINGDATE,
                DATEMODIFIED = source.DATEMODIFIED,
                STARTDATE = source.STARTDATE,
                ENDDATE = source.ENDDATE,
                CHANGEDBYID = source.CHANGEAGENTID,
                DATECHANGED = source.CURRENTDATE
        when not matched then
            insert 
                (PDACCOUNTSYSTEMID,
                PROJECTID,
                DESCRIPTION,
                UIPROJECTID,
                TYPE,
                PREVENTDATAENTRY,
                PREVENTPOSTINGAFTER,
                POSTINGDATE,
                DATEMODIFIED,
                STARTDATE,
                ENDDATE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED)
            values
                (source.PDACCOUNTSYSTEMID,
                source.PROJECTID,
                source.DESCRIPTION,
                source.UIPROJECTID,
                source.TYPE,
                source.PREVENTDATAENTRY,
                source.PREVENTPOSTINGAFTER,
                source.POSTINGDATE,
                source.DATEMODIFIED,
                source.STARTDATE,
                source.ENDDATE,
                source.CHANGEAGENTID,
                source.CHANGEAGENTID,
                source.CURRENTDATE,
                source.CURRENTDATE);