USP_BBWORKFLOW_ALERT_ADD

create procedure dbo.USP_BBWORKFLOW_ALERT_ADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @BBWORKFLOWINSTANCEINFOID uniqueidentifier ,
    @WORKFLOWINBOXID uniqueidentifier,
    @SUBJECT nvarchar(255),
    @DESCRIPTION nvarchar(max) = '',
    @DATEISSUED datetimeoffset = null,
    @CATEGORY nvarchar(50) = '',
    @ALERTPAGEID uniqueidentifier = null,
    @BBWORKFLOWBUSINESSACTIVITYID uniqueidentifier = null
)
as

    /*
    Used by the workflow service to add a workflow task to a workflow inbox
    */

    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();

    if @DATEISSUED is null
      set @DATEISSUED = getdate();

    if @BBWORKFLOWBUSINESSACTIVITYID = '00000000-0000-0000-0000-000000000000' 
        set @BBWORKFLOWBUSINESSACTIVITYID = null;

    if @BBWORKFLOWBUSINESSACTIVITYID is not null
        begin
            declare @BIZACTIVITY_PARENTWORKFLOWINSTANCEINFOID uniqueidentifier;
            select @BIZACTIVITY_PARENTWORKFLOWINSTANCEINFOID = BBWORKFLOWINSTANCEINFOID from dbo.BBWORKFLOWBUSINESSACTIVITY where ID = @BBWORKFLOWBUSINESSACTIVITYID;
            if @BIZACTIVITY_PARENTWORKFLOWINSTANCEINFOID <> @BBWORKFLOWINSTANCEINFOID
                begin
                    raiserror('The @BBWORKFLOWBUSINESSACTIVITYID value is not a valid ID for the BBWORKFLOWINSTANCEINFO record',16,10);
                    return 500;
                end
        end

    begin try
        insert into dbo.BBWORKFLOWALERT
            (ID, BBWORKFLOWINSTANCEINFOID, BBWORKFLOWINBOXID, SUBJECT, DESCRIPTION, DATEISSUED, CATEGORY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ALERTPAGEID, BBWORKFLOWBUSINESSACTIVITYID)
        values
            (@ID, @BBWORKFLOWINSTANCEINFOID, @WORKFLOWINBOXID, @SUBJECT, @DESCRIPTION, @DATEISSUED, @CATEGORY, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ALERTPAGEID, @BBWORKFLOWBUSINESSACTIVITYID)

        exec dbo.USP_WORKFLOWALERT_ISSUED_ALERT_SEND @ID;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;