USP_DATAFORMTEMPLATE_EDIT_RESERVATIONPRICING

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RESERVATIONPRICING (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @PRICINGCODE tinyint,
    @RATESCALEID uniqueidentifier
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();

    begin try
        -- Validation for flat rate
        if @PRICINGCODE = 1 and @RATESCALEID is null begin
            raiserror('ERR_RATESCALE',13,1);
        end

        update dbo.RESERVATION set
            PRICINGCODE = @PRICINGCODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @ID;

        if @PRICINGCODE = 1 begin
            delete from dbo.SALESORDERITEMITEMDISCOUNT
            from
                dbo.SALESORDERITEMITEMDISCOUNT
            inner join
                dbo.SALESORDERITEM on SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
            where
                SALESORDERITEM.SALESORDERID = @ID;

            delete from dbo.SALESORDERITEMSCENARIOSDONE where SALESORDERID = @ID;
            delete from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION where SALESORDERID = @ID;
            delete from dbo.SALESORDERITEMDISCOUNTOPTION where SALESORDERID = @ID;
            delete from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION where SALESORDERID = @ID;
            delete from dbo.SALESORDERITEM where SALESORDERID = @ID and TYPECODE = 5;

            delete from dbo.SALESORDERITEMORDERDISCOUNT
            from
                dbo.SALESORDERITEMORDERDISCOUNT
            inner join
                dbo.SALESORDERITEM on SALESORDERITEMORDERDISCOUNT.ID = SALESORDERITEM.ID
            where
                SALESORDERITEM.SALESORDERID = @ID;

            delete from dbo.SALESORDERMANUALDISCOUNT where SALESORDERID = @ID;

            declare @OLDRATESCALEID uniqueidentifier = null
            declare @RATESCALEEXISTS bit = 0
            select
                @RATESCALEEXISTS = 1,
                @OLDRATESCALEID = RATESCALEID
            from dbo.RESERVATIONRATESCALE
            where ID = @ID

            if @OLDRATESCALEID is null or @OLDRATESCALEID <> @RATESCALEID begin
                exec dbo.USP_RESERVATION_UPDATERATESCALE @ID, @RATESCALEID, @CHANGEAGENTID, @CURRENTDATE;
            end
        end

        exec dbo.USP_RESERVATION_UPDATEFLATRATE @ID, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_RESERVATION_UPDATESTATUSANDHISTORY @ID, @CHANGEAGENTID;
    end try

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

    return 0;