USP_PROGRAM_COPYDISCOUNTS

CREATE procedure dbo.USP_PROGRAM_COPYDISCOUNTS
(
    @OLDPROGRAMID uniqueidentifier,
    @NEWPROGRAMID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @PROGRAMDISCOUNTS table
    (
        DISCOUNTGROUPID uniqueidentifier,
        DISCOUNTGROUPDETAILID uniqueidentifier,
        DISCOUNTID uniqueidentifier,
        UNDISCOUNTEDGROUPDETAILID uniqueidentifier
    )

    insert into @PROGRAMDISCOUNTS
    select
        newid() as DISCOUNTGROUPID,
        newid() as DISCOUNTGROUPDETAILID,
        DISCOUNT.ID as DISCOUNTID,
        case (DISCOUNT.DISCOUNTTYPECODE)
            when 1 then newid()
            else null
        end as UNDISCOUNTEDGROUPDETAILID
    from
        dbo.DISCOUNT
        inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
        inner join dbo.DISCOUNTGROUPDETAIL on DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = DISCOUNTGROUP.ID
        inner join dbo.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
    where
        DISCOUNT.SUPERSEDEDBYID is null and
        DISCOUNTGROUPDETAILPROGRAM.PROGRAMID = @OLDPROGRAMID and
        DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1

    insert into dbo.DISCOUNTGROUP (
        ID,
        DISCOUNTID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        DISCOUNTGROUPID,
        DISCOUNTID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @PROGRAMDISCOUNTS

    insert into dbo.DISCOUNTGROUPDETAIL (
        ID,
        DISCOUNTGROUPID,
        DISCOUNTEDITEM,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        DISCOUNTGROUPDETAILID,
        DISCOUNTGROUPID,
        1,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @PROGRAMDISCOUNTS

    insert into dbo.DISCOUNTGROUPDETAILPROGRAM (
        ID,
        PROGRAMID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        DISCOUNTGROUPDETAILID,
        @NEWPROGRAMID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @PROGRAMDISCOUNTS

    -- take care of any bogo discounts


    insert into dbo.DISCOUNTGROUPDETAIL (
        ID,
        DISCOUNTGROUPID,
        DISCOUNTEDITEM,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        UNDISCOUNTEDGROUPDETAILID,
        DISCOUNTGROUPID,
        0,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @PROGRAMDISCOUNTS
        where UNDISCOUNTEDGROUPDETAILID is not null

    insert into dbo.DISCOUNTGROUPDETAILPROGRAM (
        ID,
        PROGRAMID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        UNDISCOUNTEDGROUPDETAILID,
        @NEWPROGRAMID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @PROGRAMDISCOUNTS
        where UNDISCOUNTEDGROUPDETAILID is not null

end