USP_RECORDOPERATION_RESERVATIONDELETE

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

    declare @STATUSCODE tinyint;

    begin try

        select @STATUSCODE = SALESORDER.STATUSCODE
        from dbo.SALESORDER
        inner join dbo.RESERVATION on SALESORDER.ID = RESERVATION.ID
        where RESERVATION.ID = @ID

        if @STATUSCODE <> 5
            raiserror('BBERR_INVALIDSTATUS', 13, 1);

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

        if exists (select 1 from dbo.DOCUMENTPRINTINGHISTORY where SALESORDERID = @ID)
            raiserror('BBERR_RESERVATION_PAYMENTSPRINTED', 13, 1);

        declare @e int;
        declare @contextCache varbinary(128);

        set @contextCache = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID

        delete from dbo.DOCUMENTPRINTINGHISTORY
        where SALESORDERID = @ID

        -- Remove taxes before removing fees and tickets

        delete from dbo.SALESORDERITEMTAX
        where TAXITEMID in
        (select ID from dbo.SALESORDERITEM where SALESORDERID = @ID)

        -- Fees need to be deleted before the item they are linked to  

        delete from dbo.SALESORDERITEM
        where SALESORDERID = @ID and TYPECODE = 3;

        -- Drop the discounts

        delete from dbo.SALESORDERMANUALDISCOUNT
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL 
        where SALESORDERITEMID in 
        (
            select ID
            from dbo.SALESORDERITEM
            where SALESORDERITEM.SALESORDERID = @ID
        )

        -- Drop auto apply discount information                    

        delete from dbo.SALESORDERITEMDISCOUNTOPTION
        where SALESORDERID = @ID;            

        delete from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERITEMSCENARIOSDONE
        where SALESORDERID = @ID;

        -- Clear all facilities properly.

        exec dbo.USP_RESERVATION_REMOVEFACILITIES @ID;    

        -- Delete the attendees to trigger sync logic

        delete from dbo.ITINERARYATTENDEE
        from dbo.ITINERARYATTENDEE
            inner join dbo.ITINERARY on ITINERARYATTENDEE.ITINERARYID = ITINERARY.ID
        where ITINERARY.RESERVATIONID = @ID

        if not @contextCache is null
            set CONTEXT_INFO @contextCache

        select @e=@@error;

        if @e<>0 return -456; --always return non-zero sp result if an error occurs    


        exec USP_SALESORDER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch
    return 0;