USP_STEWARDSHIPPACKAGEPROCESSSTATUS_DELETE

Executes the "Stewardship Package Process Status: Delete" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.
Definition

Copy


                    CREATE procedure dbo.USP_STEWARDSHIPPACKAGEPROCESSSTATUS_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as begin
                        set nocount on;

                        if exists (select ID from dbo.BUSINESSPROCESSSTATUS    where ID = @ID and STATUSCODE = 1)
                            raiserror('Business process is still running',13,1);    

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

                        --remove history tables create by the business process

                        declare @STATUSID uniqueidentifier;
                        select @STATUSID = BUSINESSPROCESSSTATUSGUID from dbo.STEWARDSHIPPACKAGEPROCESSHISTORY where ID = @ID;

                        declare @SQL nvarchar(300);
                        declare @TABLENAME nvarchar(300);

                        if len(@STATUSID) > 0        
                        begin
                            set @TABLENAME = 'dbo.[STEWARDSHIPPACKAGE_' + replace(cast(@STATUSID as nvarchar(36)), '-', '_') + ']'
                            set @SQL = 'if OBJECT_ID(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME; 
                            exec (@SQL);
                            set @TABLENAME = 'dbo.[STEWARDSHIPPACKAGEGIVINGSUMMARY_' + replace(cast(@STATUSID as nvarchar(36)), '-', '_') + ']'
                            set @SQL = 'if OBJECT_ID(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME; 
                            exec (@SQL);
                            set @TABLENAME = 'dbo.[STEWARDSHIPPACKAGEFINANCIALINFO_' + replace(cast(@STATUSID as nvarchar(36)), '-', '_') + ']'
                            set @SQL = 'if OBJECT_ID(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME; 
                            exec (@SQL);
                            set @TABLENAME = 'dbo.[STEWARDSHIPPACKAGERECIPIENTS_' + replace(cast(@STATUSID as nvarchar(36)), '-', '_') + ']'
                            set @SQL = 'if OBJECT_ID(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME; 
                            exec (@SQL);
                            set @TABLENAME = 'dbo.[STEWARDSHIPPACKAGESTEWARDSHIP_' + replace(cast(@STATUSID as nvarchar(36)), '-', '_') + ']'
                            set @SQL = 'if OBJECT_ID(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME; 
                            exec (@SQL);
                        end

                        exec dbo.USP_STEWARDSHIPPACKAGEPROCESSHISTORY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
                        exec dbo.USP_STEWARDSHIPPACKAGEPROCESSSTATUS_DELETEBYID_WITHCHANGEAGENTID @STATUSID, @CHANGEAGENTID
                        exec dbo.USP_BUSINESSPROCESSSTATUS_DELETEBYID_WITHCHANGEAGENTID @STATUSID, @CHANGEAGENTID
                        return 0;
                    end