USP_REVENUEBATCH_REGISTRANT_DELETE

create procedure dbo.USP_REVENUEBATCH_REGISTRANT_DELETE
(
    @BATCHREVENUEREGISTRANTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null output
)
as
begin
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();

    declare @CONTEXTCACHE varbinary(128);
    set @CONTEXTCACHE = CONTEXT_INFO();

    set CONTEXT_INFO @CHANGEAGENTID;


    /* Delete guests */

    declare @CREATEDGUESTS table (ID uniqueidentifier);
    insert into @CREATEDGUESTS (ID)
    select
        BATCHREVENUEREGISTRANT.ID
    from
        dbo.BATCHREVENUEREGISTRANT
    where
        BATCHREVENUEREGISTRANT.GUESTOFREGISTRANTID = @BATCHREVENUEREGISTRANTID;

    --Delete records with foreign keys that do not cascade

    delete dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP where REGISTRANTID in (select ID from @CREATEDGUESTS);

    --BATCHREVENUEAPPLICATION table does not need to be changed for guests because the application will only ever be for the host registrant


    --Delete the guest records

    delete dbo.BATCHREVENUEREGISTRANT where ID in (select ID from @CREATEDGUESTS);

    /* Done deleting guests */


    /* Delete host */

    --Delete records with foreign keys that do not cascade


    delete dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP where REGISTRANTID = @BATCHREVENUEREGISTRANTID;

    --Update records that reference both REGISTRANT and BATCHREVENUEREGISTRANT so that they are only referencing REGISTRANT


    update dbo.BATCHREVENUEAPPLICATION
    set
        REGISTRANTID = @BATCHREVENUEREGISTRANTID,
        BATCHREVENUEREGISTRANTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where
        BATCHREVENUEREGISTRANTID = @BATCHREVENUEREGISTRANTID;

    --Delete the record


    exec dbo.USP_BATCHREVENUEREGISTRANT_DELETEBYID_WITHCHANGEAGENTID @BATCHREVENUEREGISTRANTID, @CHANGEAGENTID;

    /* Done deleting host */

    if not @CONTEXTCACHE is null
        set CONTEXT_INFO @CONTEXTCACHE;

end