USP_SALESORDER_APPLYITEMDISCOUNTS_DETERMINEDISCOUNTCOMBINATIONS

CREATE procedure [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS_DETERMINEDISCOUNTCOMBINATIONS]
(
    @SALESORDERID uniqueidentifier,
    @ITEMCOUNTTOPROCESS integer,
    @PREVIOUSSCENARIOBATCHID uniqueidentifier,
    @DISCOUNTORDERITEMS UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM readonly,    
    @DISCOUNTSCENARIOORDERITEMQUANTITY UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOORDERITEMQUANTITY readonly,
    @DISCOUNTSCENARIOITEMCOUNT UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOITEMCOUNT readonly,    
    @CHANGEAGENTID uniqueidentifier    
)
as
    set nocount on;

    declare @CURRENTDATE datetime = getdate();

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

    --create discount scenario price type aggregations to use in creating discount scenario combinations

    declare @DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY as UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY

    insert into @DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY
    select 
        SCENARIOCOMBINATIONID, 
        ORDERITEMID, 
        sum(QUANTITY) QUANTITY
    from
    (
        select 
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID,
            SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID ORDERITEMID,        
            sum(SALESORDERITEMDISCOUNTOPTION.NumberOfDiscountedItems) QUANTITY
        from
            dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
        inner join
            dbo.SALESORDERITEMDISCOUNTOPTION on SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
        where
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SALESORDERID = @SALESORDERID and
            (SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOBATCHID = @PREVIOUSSCENARIOBATCHID or @PREVIOUSSCENARIOBATCHID is null)
        group by 
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID, 
            SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID    

        union all
        select 
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID,
            SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID ORDERITEMID,        
            sum(SALESORDERITEMDISCOUNTOPTION.NumberOfQualifyingItems) QUANTITY
        from
            dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
        inner join
            dbo.SALESORDERITEMDISCOUNTOPTION on SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
        where
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SALESORDERID = @SALESORDERID and
            SALESORDERITEMDISCOUNTOPTION.DISCOUNTTYPECODE <> 0 and
            (SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOBATCHID = @PREVIOUSSCENARIOBATCHID or @PREVIOUSSCENARIOBATCHID is null)
        group by 
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID, 
            SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID
    ) T
    group by
        SCENARIOCOMBINATIONID, 
        ORDERITEMID

    --build table of different combinations where discount scenario does not already exist in the discount scenario combination    

    declare @DISCOUNTCOMBINATIONS as UDT_APPLYDISCOUNTS_DISCOUNTCOMBINATION;

    insert into @DISCOUNTCOMBINATIONS    
    select distinct 
        SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
        SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID,
        SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOGROUPID
    from 
        dbo.SALESORDERITEMDISCOUNTOPTION
    inner join @DISCOUNTSCENARIOITEMCOUNT DISCOUNTSCENARIOITEMCOUNT on
        SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = DISCOUNTSCENARIOITEMCOUNT.DISCOUNTSCENARIOID
    inner join dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION on
        SALESORDERITEMDISCOUNTOPTION.SALESORDERID = SALESORDERDISCOUNTSCENARIOCOMBINATION.SALESORDERID and
        SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOGROUPID = SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOGROUPID 
    inner join @DISCOUNTSCENARIOITEMCOUNT DSOC on
        SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID = DSOC.DISCOUNTSCENARIOID
    left outer join (
        select
            DSC.SCENARIOCOMBINATIONID,
            SODCO.NUMBERID
        from
            dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION DSC
        inner join
            dbo.SALESORDERITEMDISCOUNTOPTION SODCO on DSC.DISCOUNTSCENARIOID = SODCO.DISCOUNTSCENARIOID
        inner join
            @DISCOUNTSCENARIOITEMCOUNT DSIC on DSC.DISCOUNTSCENARIOID = DSIC.DISCOUNTSCENARIOID
        where
            SODCO.SALESORDERID = @SALESORDERID
            and (
                DSC.SCENARIOBATCHID is not null
                or (DSC.SCENARIOBATCHID is null and @PREVIOUSSCENARIOBATCHID is null)
            )
            and DSIC.TOTALORDERITEMCOUNT = @ITEMCOUNTTOPROCESS
    ) T2 on
        SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID = T2.SCENARIOCOMBINATIONID and
        SALESORDERITEMDISCOUNTOPTION.NUMBERID >= T2.NUMBERID
    where
        SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID
        and DISCOUNTSCENARIOITEMCOUNT.TOTALORDERITEMCOUNT = @ITEMCOUNTTOPROCESS
        and SALESORDERITEMDISCOUNTOPTION.COMPLETED = 1
        and (
            SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOBATCHID = @PREVIOUSSCENARIOBATCHID
            or (
                @PREVIOUSSCENARIOBATCHID is null
                and (
                    SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOBATCHID is not null
                    or (SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOBATCHID is null and DSOC.TOTALORDERITEMCOUNT >= @ITEMCOUNTTOPROCESS)
                )
            )
        )
        and T2.SCENARIOCOMBINATIONID is null;

    if @@rowcount = 0 begin
        return 0;
    end

    declare @VALIDDISCOUNTCOMBINATIONS as UDT_APPLYDISCOUNTS_VALIDDISCOUNTCOMBINATION;

    insert into @VALIDDISCOUNTCOMBINATIONS
    select
        newid(),
        DISCOUNTCOMBINATIONS.DISCOUNTSCENARIOID,
        DISCOUNTCOMBINATIONS.SCENARIOCOMBINATIONID,
        DISCOUNTCOMBINATIONS.DISCOUNTSCENARIOGROUPID
    from
        @DISCOUNTCOMBINATIONS DISCOUNTCOMBINATIONS
    where
        not exists (
            select 
                *
            from 
                @DISCOUNTSCENARIOORDERITEMQUANTITY DISCOUNTSCENARIOORDERITEMQUANTITY
            inner join
                @DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY on DISCOUNTSCENARIOORDERITEMQUANTITY.ORDERITEMID = DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY.ORDERITEMID                    
            inner join
                @DISCOUNTORDERITEMS DISCOUNTORDERITEMS on DISCOUNTSCENARIOORDERITEMQUANTITY.ORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID
            where
                DISCOUNTSCENARIOORDERITEMQUANTITY.DISCOUNTSCENARIOID = DISCOUNTCOMBINATIONS.DISCOUNTSCENARIOID and
                DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY.SCENARIOCOMBINATIONID = DISCOUNTCOMBINATIONS.SCENARIOCOMBINATIONID and
                (DISCOUNTSCENARIOORDERITEMQUANTITY.QUANTITY + DISCOUNTSCENARIOCOMBINATIONORDERITEMQUANTITY.QUANTITY) > DISCOUNTORDERITEMS.QUANTITY
        )

    if @@rowcount = 0 begin
        return 0;
    end

    --determine if any of these valid combinations exceed the number of discounts allowed per order - checked at the discount group level    

    if exists(select * from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION where SALESORDERID = @SALESORDERID and LIMITDISCOUNTSPERORDER = 1) begin
        declare @SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT as UDT_APPLYDISCOUNTS_SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT

        insert into @SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT
        select 
            SCENARIOGROUPCOUNT.SCENARIOCOMBINATIONID,
            SCENARIOGROUPCOUNT.DISCOUNTGROUPID,                                
            sum(SCENARIOGROUPCOUNT.NUMBEROFTIMESAPPLIED)
        from (
            select distinct
                VALIDDISCOUNTCOMBINATIONS.ID SCENARIOCOMBINATIONID,
                SALESORDERITEMDISCOUNTOPTION.ID,
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,                                    
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID,
                SALESORDERITEMDISCOUNTOPTION.NUMBEROFTIMESAPPLIED    
            from
                @VALIDDISCOUNTCOMBINATIONS VALIDDISCOUNTCOMBINATIONS
            inner join dbo.SALESORDERITEMDISCOUNTOPTION on
                VALIDDISCOUNTCOMBINATIONS.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
            inner join dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION on
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID and
                SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1
            where
                SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID
                and [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION].[SALESORDERID] = @SALESORDERID

            union all
            select distinct
                VALIDDISCOUNTCOMBINATIONS.ID SCENARIOCOMBINATIONID,
                SALESORDERITEMDISCOUNTOPTION.ID,
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,                                    
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID,
                SALESORDERITEMDISCOUNTOPTION.NUMBEROFTIMESAPPLIED                
            from
                @VALIDDISCOUNTCOMBINATIONS VALIDDISCOUNTCOMBINATIONS
            inner join dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION on
                VALIDDISCOUNTCOMBINATIONS.SCENARIOCOMBINATIONID = SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID and                        
                SALESORDERDISCOUNTSCENARIOCOMBINATION.SALESORDERID = @SALESORDERID
            inner join dbo.SALESORDERITEMDISCOUNTOPTION on
                SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
            inner join dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION on
                SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID and
                SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1
            where
                [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION].[SALESORDERID] = @SALESORDERID
        ) SCENARIOGROUPCOUNT
        group by
            SCENARIOGROUPCOUNT.SCENARIOCOMBINATIONID,
            SCENARIOGROUPCOUNT.DISCOUNTGROUPID

        delete from @VALIDDISCOUNTCOMBINATIONS
        from
            @VALIDDISCOUNTCOMBINATIONS VALIDDISCOUNTCOMBINATIONS
        inner join
            @SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT on VALIDDISCOUNTCOMBINATIONS.ID = SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT.SCENARIOCOMBINATIONID
        inner join
            dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION on SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT.DISCOUNTGROUPID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID                                                                            
        where
            SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.SALESORDERID = @SALESORDERID and
            SCENARIOCOMBINATIONDISCOUNTGROUPCOUNT.[COUNT] > SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER
    end

    declare @SCENARIOBATCHID uniqueidentifier = newid();

    insert into    SALESORDERDISCOUNTSCENARIOCOMBINATION
        (ID,
        SALESORDERID,
        SCENARIOBATCHID,
        SCENARIOCOMBINATIONID,
        DISCOUNTSCENARIOGROUPID,
        DISCOUNTSCENARIOID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED)                
    select
        newid(),
        @SALESORDERID,
        @SCENARIOBATCHID,                
        VALIDDISCOUNTCOMBINATIONS.ID,
        VALIDDISCOUNTCOMBINATIONS.DISCOUNTSCENARIOGROUPID,
        VALIDDISCOUNTCOMBINATIONS.DISCOUNTSCENARIOID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE                
    from
        @VALIDDISCOUNTCOMBINATIONS VALIDDISCOUNTCOMBINATIONS

    insert into SALESORDERDISCOUNTSCENARIOCOMBINATION
        (ID,
        SALESORDERID,
        SCENARIOBATCHID,
        SCENARIOCOMBINATIONID,
        DISCOUNTSCENARIOGROUPID,
        DISCOUNTSCENARIOID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED)                
    select
        newid(),
        @SALESORDERID,
        @SCENARIOBATCHID,                
        VALIDDISCOUNTCOMBINATIONS.ID,
        SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOGROUPID,
        SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE                    
    from
        @VALIDDISCOUNTCOMBINATIONS VALIDDISCOUNTCOMBINATIONS
    inner join
        dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION on VALIDDISCOUNTCOMBINATIONS.SCENARIOCOMBINATIONID = SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID
    where
        SALESORDERDISCOUNTSCENARIOCOMBINATION.SALESORDERID = @SALESORDERID                    

    if @@NESTLEVEL<32
        exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_DETERMINEDISCOUNTCOMBINATIONS @SALESORDERID, @ITEMCOUNTTOPROCESS, @SCENARIOBATCHID, @DISCOUNTORDERITEMS, @DISCOUNTSCENARIOORDERITEMQUANTITY, @DISCOUNTSCENARIOITEMCOUNT, @CHANGEAGENTID

    return 0;