USP_PROGRAMDISCOUNTDELETE_DISCOUNTGROUP

CREATE procedure dbo.USP_PROGRAMDISCOUNTDELETE_DISCOUNTGROUP
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin
    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if not exists (
        select DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
        from dbo.DISCOUNTGROUPDETAILPROGRAM
        inner join dbo.DISCOUNTGROUPDETAIL on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
        where DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = @ID
    )
    begin
        raiserror('BBERR_NOTAPROGRAMDISCOUNT', 13, 1);
        return 1;
    end

    declare @DISCOUNTID uniqueidentifier;
    declare @PROGRAMID uniqueidentifier;
    select
        @DISCOUNTID = DISCOUNTGROUP.DISCOUNTID,
        @PROGRAMID = DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
    from dbo.DISCOUNTGROUP
    inner join dbo.DISCOUNTGROUPDETAIL on DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = DISCOUNTGROUP.ID
    inner join dbo.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAILPROGRAM.ID = DISCOUNTGROUPDETAIL.ID
    where DISCOUNTGROUP.ID = @ID;

    declare @CLONEID uniqueidentifier;
    exec dbo.USP_DISCOUNT_CLONE @DISCOUNTID, @CLONEID output;

    -- delete cloned version instead.

    set @ID = (
        select top 1 DISCOUNTGROUP.ID
        from dbo.DISCOUNTGROUP
        inner join dbo.DISCOUNTGROUPDETAIL on DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = DISCOUNTGROUP.ID
        inner join dbo.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAILPROGRAM.ID = DISCOUNTGROUPDETAIL.ID
        where DISCOUNTGROUP.DISCOUNTID = @CLONEID
            and DISCOUNTGROUPDETAILPROGRAM.PROGRAMID = @PROGRAMID
    );

    exec USP_DISCOUNTGROUP_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    -- Update the DISCOUNT record to invalidate the cache for sales.

    update dbo.DISCOUNT
    set CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = getdate()
    where ID = @DISCOUNTID;

    return 0;
end