USP_DATAFORMTEMPLATE_EDIT_PROGRAMREGISTRATIONINFORMATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROGRAMREGISTRATIONINFORMATION 
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,

    @REGISTRATIONINFORMATIONTYPECODE tinyint,
    @REGISTRATIONSECTIONCODEID uniqueidentifier,
    @REGISTRATIONINFORMATIONID uniqueidentifier,
    @REQUIRED bit
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        if @REGISTRATIONINFORMATIONTYPECODE = 0 --Announcement
            set @REQUIRED = 0

        --determine the program ID to see if the section exists for it
        declare @PROGRAMID uniqueidentifier
        declare @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID uniqueidentifier        
        select 
            @PROGRAMID = pers.PROGRAMID,
            @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID = pers.ID
        from dbo.PROGRAMEVENTREGISTRATIONSECTION as pers
        inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION as persri
            on pers.ID = persri.PROGRAMEVENTREGISTRATIONSECTIONID
        where persri.ID = @ID 

        declare @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID uniqueidentifier = (select top 1 ID from dbo.PROGRAMEVENTREGISTRATIONSECTION where (PROGRAMID = @PROGRAMID) and (REGISTRATIONSECTIONCODEID = @REGISTRATIONSECTIONCODEID))

        if @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID is null begin
            declare @MAXSECTIONSEQUENCE int = (select isnull(max(SEQUENCE), 0) from dbo.PROGRAMEVENTREGISTRATIONSECTION where (PROGRAMID = @PROGRAMID))

            set @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID = newid()
            insert into dbo.PROGRAMEVENTREGISTRATIONSECTION (ID, PROGRAMID, REGISTRATIONSECTIONCODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
            values (@DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID, @PROGRAMID, @REGISTRATIONSECTIONCODEID, @MAXSECTIONSEQUENCE + 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)                
        end

        declare @MAXITEMSEQUENCE int = (select isnull(max(SEQUENCE), 0) from dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION where (PROGRAMEVENTREGISTRATIONSECTIONID = @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID))

        update dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION set
            PROGRAMEVENTREGISTRATIONSECTIONID = @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID,
            REGISTRATIONINFORMATIONID = @REGISTRATIONINFORMATIONID,
            SEQUENCE = case when (@DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID = @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID) then SEQUENCE else @MAXITEMSEQUENCE + 1 end,
            [REQUIRED] = @REQUIRED,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID

        --clean up the original section if the item was moved out of it and there are no items which now refer to it...
        if @DESTINATIONPROGRAMEVENTREGISTRATIONSECTIONID <> @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID begin
            if not exists (select 1 from dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION where PROGRAMEVENTREGISTRATIONSECTIONID = @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID) begin
                exec dbo.USP_PROGRAMEVENTREGISTRATIONSECTION_DELETEBYID_WITHCHANGEAGENTID @ORIGINALPROGRAMEVENTREGISTRATIONSECTIONID, @CHANGEAGENTID;
            end
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;