USP_DATAFORMTEMPLATE_ADD_RELATIONSHIPJOBINFO

The save procedure used by the add dataform template "Relationship Job Info Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@RELATIONSHIPID	uniqueidentifier	IN	Relationship
@JOBTITLE	nvarchar(100)	IN	Job title
@CAREERLEVELCODEID	uniqueidentifier	IN	Career level
@JOBCATEGORYCODEID	uniqueidentifier	IN	Category
@STARTDATE	date	IN	Start date
@ENDDATE	date	IN	End date
@JOBDIVISION	nvarchar(100)	IN	Division
@JOBDEPARTMENT	nvarchar(100)	IN	Department
@JOBSCHEDULECODEID	uniqueidentifier	IN	Schedule
@JOBRESPONSIBILITY	nvarchar(250)	IN	Responsibilities
@ISPRIVATERECORD	bit	IN	Is private
@SYNCENDDATETORELATIONSHIP	bit	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RELATIONSHIPJOBINFO
(
    @ID uniqueidentifier = null output,
    @CONTEXTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @RELATIONSHIPID uniqueidentifier = null,
    @JOBTITLE nvarchar(100) = '',
    @CAREERLEVELCODEID uniqueidentifier = null,
    @JOBCATEGORYCODEID uniqueidentifier = null,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @JOBDIVISION nvarchar(100) = '',
    @JOBDEPARTMENT nvarchar(100) = '',
    @JOBSCHEDULECODEID uniqueidentifier = null,
    @JOBRESPONSIBILITY nvarchar(250) = '',
    @ISPRIVATERECORD bit = 0,
    @SYNCENDDATETORELATIONSHIP bit = 1
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

    declare @RELATIONSHIPSETID uniqueidentifier;

    if @STARTDATE > @CURRENTDATE
        raiserror('BBERR_STARTDATEFUTURE', 13, 1)

    select @RELATIONSHIPSETID = RELATIONSHIPSETID 
    from dbo.RELATIONSHIP where ID = @RELATIONSHIPID;

    if @RELATIONSHIPSETID is null
    begin
        exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @RELATIONSHIPID
    end

    update dbo.RELATIONSHIPJOBINFO
    set 
        ENDDATE = dateadd(day, -1, @STARTDATE),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where RELATIONSHIPSETID = @RELATIONSHIPSETID
        and (ENDDATE is null or ENDDATE >= @STARTDATE)
        and (STARTDATE is null or STARTDATE < @STARTDATE);


    insert into dbo.RELATIONSHIPJOBINFO
        (ID, RELATIONSHIPSETID, JOBTITLE, CAREERLEVELCODEID, JOBCATEGORYCODEID, STARTDATE, ENDDATE, JOBDIVISION, JOBDEPARTMENT, JOBSCHEDULECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, JOBRESPONSIBILITY, ISPRIVATERECORD)
    values
        (@ID, @RELATIONSHIPSETID, @JOBTITLE, @CAREERLEVELCODEID, @JOBCATEGORYCODEID, @STARTDATE, @ENDDATE, @JOBDIVISION, @JOBDEPARTMENT, @JOBSCHEDULECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @JOBRESPONSIBILITY, @ISPRIVATERECORD)

    if @SYNCENDDATETORELATIONSHIP = 1
    begin

        update 
            dbo.RELATIONSHIP
            set ENDDATE = @ENDDATE
        where RELATIONSHIPSETID = @RELATIONSHIPSETID;

    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0