USP_SALESORDER_APPLYITEMDISCOUNTS_CALCULATEITEMDISCOUNT
Builds discount scenarios and calculates the discount for each scenario.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @DISCOUNTORDERITEMS | UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM | IN | |
| @ORDERITEMDISCOUNTGROUPAVAILABLE | UDT_APPLYDISCOUNTS_ORDERITEMDISCOUNTGROUPAVAILABLE | IN | |
| @INCOMPLETEDISCOUNT | bit | IN | |
| @DISCOUNTNESTLEVEL | int | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS_CALCULATEITEMDISCOUNT]
(
@SALESORDERID uniqueidentifier,
@DISCOUNTORDERITEMS UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM readonly,
@ORDERITEMDISCOUNTGROUPAVAILABLE UDT_APPLYDISCOUNTS_ORDERITEMDISCOUNTGROUPAVAILABLE readonly,
@INCOMPLETEDISCOUNT bit = 0,
@DISCOUNTNESTLEVEL int = 0,
@CHANGEAGENTID uniqueidentifier
) as
set nocount on;
declare @CURRENTDATE datetime = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
--fills out the combinations of available disCOUNTs based on pricetype and determines how many of each combination can be applied
declare @DISCOUNTSAVAILABLE dbo.UDT_APPLYDISCOUNTS_DISCOUNTSAVAILABLE
--select '@DISCOUNTORDERITEMS', @INCOMPLETEDISCOUNT, * from @DISCOUNTORDERITEMS
--select 'SALESORDERITEMDISCOUNTOPTION', * from dbo.SALESORDERITEMDISCOUNTOPTION where SALESORDERID = @SALESORDERID
--****************************
--need to optimize so price types that have no items do not get scenarios created for them
--****************************
insert into @DISCOUNTSAVAILABLE
select
newid() ID,
DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTICKETSFORCODE,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPRICETYPECODEID,
case
when @INCOMPLETEDISCOUNT = 1 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE = 0 then
1
else
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT
-
(
select sum(SO.NUMBEROFDISCOUNTEDITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where SO.SALESORDERID = @SALESORDERID
and SO.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
)
end
else
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGPRICETYPECODEID,
case
when @INCOMPLETEDISCOUNT = 1 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE = 0 then
1
else
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE
-
(
select sum(SO.NUMBEROFQUALIFYINGITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where SO.SALESORDERID = @SALESORDERID
and SO.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
)
end
else
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE
end as NUMBERTOPURCHASE,
case
when @INCOMPLETEDISCOUNT = 1 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE = 0 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER >
(
select sum(SO.NUMBEROFDISCOUNTEDITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where SO.SALESORDERID = @SALESORDERID
and SO.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
) then
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER
-
(
select sum(SO.NUMBEROFDISCOUNTEDITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where SO.SALESORDERID = @SALESORDERID
and SO.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
)
else
0
end
else
1
end
else
1
end
else
case
when DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID = QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID and SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE = 1 then
case
when floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE + SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT)) = 0 then
1
else
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1 then
case
when floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE + SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT)) >= SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER then
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER
else
floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE + SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT))
end
else
floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE + SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT))
end
end
when floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT) < floor(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE) then
case
when floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT) = 0 then
1
else
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1 then
case
when floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT) >= SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER then
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER
else
floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT)
end
else
floor(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT)
end
end
else
case
when floor(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE) = 0 then
1
else
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1 then
case
when floor(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE) >= SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER then
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER
else
floor(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE)
end
else
floor(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY/SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE)
end
end
end
end as NUMBEROFDISCOUNTSTOAPPLY
from
--left join for incomplete discounts when there are no more discounted items,
--but still need to qualify, null items are removed below if necessary
dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION left outer join @DISCOUNTORDERITEMS DISCOUNTEDDISCOUNTORDERITEMS on
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID and
(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIODISCOUNTGROUPID or
DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIODISCOUNTGROUPID is null)
--left join for incomplete discounts when there are no more qualifying items,
--but still need may be able to discount, null items are removed below if necessary
left outer join @DISCOUNTORDERITEMS QUALIFYINGDISCOUNTORDERITEMS on
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID = QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID and
QUALIFYINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID and
(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID = QUALIFYINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIODISCOUNTGROUPID or
QUALIFYINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIODISCOUNTGROUPID is null)
--and not exists option with same discountgroup, qualifying item and quantity, discounted item and quantity as currently attemptin
left outer join dbo.SALESORDERITEMDISCOUNTOPTION on
@INCOMPLETEDISCOUNT = 0 and
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.SALESORDERID = SALESORDERITEMDISCOUNTOPTION.SALESORDERID and
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID and
QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID = SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID and
QUALIFYINGDISCOUNTORDERITEMS.QUANTITY = SALESORDERITEMDISCOUNTOPTION.TOTALQUALIFYINGQUANTITY and
DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID and
DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY = SALESORDERITEMDISCOUNTOPTION.TOTALDISCOUNTEDQUANTITY
where
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.SALESORDERID = @SALESORDERID and
--after the first set of discounts has been calculated all of the limited discounts have been calculated, so there is no need to calculate
--incomplete discounts only look to complete the discount that has not been completed, so it does not need this constraint
(@DISCOUNTNESTLEVEL = 1 or @INCOMPLETEDISCOUNT = 1 or (@DISCOUNTNESTLEVEL > 1 and SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 0)) and
SALESORDERITEMDISCOUNTOPTION.ID is null and
((@INCOMPLETEDISCOUNT = 0 and
not DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID is null and
not QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID is null)
or
--for incomplete discounts, need to maximize the price of the qualifying items for the given scenario
--(do not look at an option where the qualifying price could be less than a disCOUNTed price)
--so disCOUNT options where the qualifying price type unit price is greater than anything that has qualified before and
--where the qualifying price type unit price is less than an already included discounted price type unit price
--i.e. if scenario has already qualified with a child, then don't look at options from the discount group with adult qualifying price types and
--if scenario has already discounted an adult, then don't look at option from the discount group with child qualifying price types
(@INCOMPLETEDISCOUNT = 1 and
--include scenarios where qualifying is done, but still can discount however no qualifying items are in result order items
--(left join above allows this to happen, but don't need to look at these if qualifying is not already complete)
(
QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID is not null
or (
QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID is null
and (
select min(NUMBEROFQUALIFYINGITEMSSTILLNEEDED)
from dbo.SALESORDERITEMDISCOUNTOPTION
where SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID
and SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
) = 0
)
) and
(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE = 0 or
(not exists(select
*
from
dbo.SALESORDERITEMDISCOUNTOPTION inner join dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION ADGC on
SALESORDERITEMDISCOUNTOPTION.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID = ADGC.ID
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID and
(ADGC.QUALIFYINGUNITPRICE < SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGUNITPRICE or
ADGC.DISCOUNTEDUNITPRICE > SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGUNITPRICE)
))) and
--for incomplete also need to make sure to only include disCOUNTs for the same disCOUNT group id that
--was used in the initial run
exists(select
*
from
dbo.SALESORDERITEMDISCOUNTOPTION
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID and
SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID and
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGGROUPITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGGROUPITEMID and
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDGROUPITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDGROUPITEMID)))
--select '@DISCOUNTSAVAILABLE', * from @DISCOUNTSAVAILABLE
--uses information from the above DISCOUNTSAVAILABLE table to calculate the number of used items for each qualifying
--disCOUNT price type and each disCOUNTed disCOUNT price type.
declare @DISCOUNTSAPPLIED as UDT_APPLYDISCOUNTS_DISCOUNTSAPPLIED
insert into @DISCOUNTSAPPLIED
select
DISCOUNTSAVAILABLE.ID,
DISCOUNTSAVAILABLE.PARENTDISCOUNTSCENARIOID,
newid(),
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID,
DISCOUNTSAVAILABLE.DISCOUNTGROUPID,
@SALESORDERID,
coalesce(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY, 0) DISCOUNTEDQUANTITY,
coalesce(DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID, SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID) DISCOUNTEDORDERITEMID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDGROUPITEMID,
DISCOUNTSAVAILABLE.DISCOUNTEDPRICETYPECODEID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE,
DISCOUNTSAVAILABLE.DISCOUNTTICKETSFORCODE,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.CALCULATIONTYPECODE,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.UNITDISCOUNTAMOUNT,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDUNITPRICE,
coalesce(QUALIFYINGDISCOUNTORDERITEMS.QUANTITY, 0) QUALIFYINGQUANTITY,
coalesce(QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID, SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID) QUALIFYINGORDERITEMID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGGROUPITEMID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGPRICETYPECODEID,
case
when (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) > QUALIFYINGDISCOUNTORDERITEMS.QUANTITY then
QUALIFYINGDISCOUNTORDERITEMS.QUANTITY
else
(DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)
end as NUMBEROFQUALIFYINGITEMS,
case
when DISCOUNTSAVAILABLE.DISCOUNTTYPECODE = 0 then
0
when (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) > QUALIFYINGDISCOUNTORDERITEMS.QUANTITY then
(DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) - QUALIFYINGDISCOUNTORDERITEMS.QUANTITY
else
0
end as NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
case
when DISCOUNTSAVAILABLE.DISCOUNTTYPECODE = 1 and SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID then
case
when (DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)) < (DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) then
case
when (DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)) <= 0 then
0
else
(DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY))
end
else
(DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)
end
else
case
when (DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) > DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY then
DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY
else
(DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)
end
end as NUMBEROFDISCOUNTEDITEMS,
case
when DISCOUNTSAVAILABLE.DISCOUNTTYPECODE = 0 then
case
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 1 then
case
when (SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER - DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) > 0 then
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBEROFDISCOUNTSPERORDER - DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY
else
0
end
else
0
end
when SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID then
case
when (DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)) < (DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) then
case
when (DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)) <= 0 then
(DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)
else
(DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) - ((DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAVAILABLE.NUMBERTOPURCHASE * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY)))
end
else
0
end
else
case
when (DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY) > DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY then
(DISCOUNTSAVAILABLE.NUMBERTODISCOUNT * NUMBEROFDISCOUNTSTOAPPLY) - DISCOUNTEDDISCOUNTORDERITEMS.QUANTITY
else
0
end
end as NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.APPLIEDMANUALLY
from
@DISCOUNTSAVAILABLE DISCOUNTSAVAILABLE inner join dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION on
DISCOUNTSAVAILABLE.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID
left outer join @DISCOUNTORDERITEMS DISCOUNTEDDISCOUNTORDERITEMS on
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = DISCOUNTEDDISCOUNTORDERITEMS.ORDERITEMID and
DISCOUNTSAVAILABLE.PARENTDISCOUNTSCENARIOID = DISCOUNTEDDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
left outer join @DISCOUNTORDERITEMS QUALIFYINGDISCOUNTORDERITEMS on
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID = QUALIFYINGDISCOUNTORDERITEMS.ORDERITEMID and
DISCOUNTSAVAILABLE.PARENTDISCOUNTSCENARIOID = QUALIFYINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
where
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.SALESORDERID = @SALESORDERID and
DISCOUNTSAVAILABLE.NUMBEROFDISCOUNTSTOAPPLY > 0
--select '@DISCOUNTSAVAILABLE', * from @DISCOUNTSAVAILABLE
--select '@DISCOUNTSAPPLIED', * from @DISCOUNTSAPPLIED
--insert DISCOUNTSAPPLIED information into a table off SALESORDERITEMDISCOUNTOPTION with discount calculated
declare @MAXID as int;
select @MAXID = coalesce(max(NUMBERID), 0) from dbo.SALESORDERITEMDISCOUNTOPTION where SALESORDERID = @SALESORDERID
insert into dbo.SALESORDERITEMDISCOUNTOPTION (
ID,
NUMBERID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID,
DISCOUNTSCENARIOGROUPID,
DISCOUNTGROUPID,
DISCOUNTTYPECODE,
DISCOUNTTICKETSFORCODE,
DISCOUNTSCENARIOID,
APPLIEDMANUALLY,
NUMBEROFTIMESAPPLIED,
SALESORDERID,
QUALIFYINGORDERITEMID,
QUALIFYINGGROUPITEMID,
QUALIFYINGPRICETYPECODEID,
NUMBEROFQUALIFYINGITEMS,
NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
TOTALQUALIFYINGQUANTITY,
DISCOUNTEDORDERITEMID,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPRICETYPECODEID,
NUMBEROFDISCOUNTEDITEMS,
NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
TOTALDISCOUNTEDQUANTITY,
DISCOUNTAMOUNT,
COMPLETED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
DISCOUNTSAPPLIED.ID,
DISCOUNTSAPPLIED.NUMBERID + @MAXID,
DISCOUNTSAPPLIED.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID,
DISCOUNTSAPPLIED.DISCOUNTSCENARIOGROUPID,
DISCOUNTSAPPLIED.DISCOUNTGROUPID,
DISCOUNTSAPPLIED.DISCOUNTTYPECODE,
DISCOUNTSAPPLIED.DISCOUNTTICKETSFORCODE,
DISCOUNTSAPPLIED.ID,
DISCOUNTSAPPLIED.APPLIEDMANUALLY,
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTSTOAPPLY,
DISCOUNTSAPPLIED.SALESORDERID,
DISCOUNTSAPPLIED.QUALIFYINGORDERITEMID,
DISCOUNTSAPPLIED.QUALIFYINGGROUPITEMID,
DISCOUNTSAPPLIED.QUALIFYINGPRICETYPECODEID,
DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMS,
DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
DISCOUNTSAPPLIED.QUALIFYINGQUANTITY,
DISCOUNTSAPPLIED.DISCOUNTEDORDERITEMID,
DISCOUNTSAPPLIED.DISCOUNTEDGROUPITEMID,
DISCOUNTSAPPLIED.DISCOUNTEDPRICETYPECODEID,
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS,
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
DISCOUNTSAPPLIED.DISCOUNTEDQUANTITY,
case
when DISCOUNTSAPPLIED.CALCULATIONTYPECODE = 0 then
case
when DISCOUNTSAPPLIED.UNITDISCOUNTAMOUNT > DISCOUNTSAPPLIED.UNITPRICE then
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS * DISCOUNTSAPPLIED.UNITPRICE
else
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS * DISCOUNTSAPPLIED.UNITDISCOUNTAMOUNT
end
else
DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS * (DISCOUNTSAPPLIED.UNITPRICE * (DISCOUNTSAPPLIED.UNITDISCOUNTAMOUNT/100))
end,
case
when DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMSSTILLNEEDED = 0 then
case
when DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMSSTILLOPEN = 0 or
isnull((select top 1 SALESORDERITEMDISCOUNTOPTION.NUMBEROFDISCOUNTEDITEMSSTILLOPEN
from dbo.SALESORDERITEMDISCOUNTOPTION
where DISCOUNTSCENARIOID = DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID), 0) = DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMSSTILLOPEN then
1 --scenario COMPLETED
else
0 --scenario incomplete
end
else
case
when isnull((select top 1 SALESORDERITEMDISCOUNTOPTION.NUMBEROFQUALIFYINGITEMSSTILLNEEDED
from dbo.SALESORDERITEMDISCOUNTOPTION
where DISCOUNTSCENARIOID = DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID), 0) = DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMSSTILLNEEDED then
2 --scenario failed
else
0 --scenario incomplete
end
end as COMPLETED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DISCOUNTSAPPLIED DISCOUNTSAPPLIED
union
select
newid(),
DISCOUNTSAPPLIED.NUMBERID + @MAXID,
SALESORDERITEMDISCOUNTOPTION.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOGROUPID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTTYPECODE,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTTICKETSFORCODE,
DISCOUNTSAPPLIED.ID,
0 APPLIEDMANUALLY, --only one in each scenario should have this set to 1 as it is used when ordering as a sum across scenarios - each scenario should only count as one
SALESORDERITEMDISCOUNTOPTION.NUMBEROFTIMESAPPLIED,
SALESORDERITEMDISCOUNTOPTION.SALESORDERID,
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID,
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGGROUPITEMID,
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGPRICETYPECODEID,
SALESORDERITEMDISCOUNTOPTION.NUMBEROFQUALIFYINGITEMS,
SALESORDERITEMDISCOUNTOPTION.NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
SALESORDERITEMDISCOUNTOPTION.TOTALQUALIFYINGQUANTITY,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDGROUPITEMID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDPRICETYPECODEID,
SALESORDERITEMDISCOUNTOPTION.NUMBEROFDISCOUNTEDITEMS,
SALESORDERITEMDISCOUNTOPTION.NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
SALESORDERITEMDISCOUNTOPTION.TOTALDISCOUNTEDQUANTITY,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTAMOUNT,
case
when DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMSSTILLNEEDED = 0 then
case
when DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMSSTILLOPEN = 0 or
isnull((select top 1 SALESORDERITEMDISCOUNTOPTION.NUMBEROFDISCOUNTEDITEMSSTILLOPEN
from dbo.SALESORDERITEMDISCOUNTOPTION
where DISCOUNTSCENARIOID = DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID), 0) = DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMSSTILLOPEN then
1 --scenario COMPLETED
else
0
end
else
case
when isnull((select top 1 SALESORDERITEMDISCOUNTOPTION.NUMBEROFQUALIFYINGITEMSSTILLNEEDED
from dbo.SALESORDERITEMDISCOUNTOPTION
where DISCOUNTSCENARIOID = DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID), 0) = DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMSSTILLNEEDED then
2 --scenario failed
else
0
end
end as COMPLETED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DISCOUNTSAPPLIED DISCOUNTSAPPLIED
inner join
dbo.SALESORDERITEMDISCOUNTOPTION on DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.COMPLETED = 0
--complete incomplete discounts
declare @INCOMPLETEDISCOUNTORDERITEMS as UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM
--select '@DISCOUNTORDERITEMS', * from @DISCOUNTORDERITEMS
--incomplete discounts will not include standard item disCOUNTs so don't need to take these into account
insert into @INCOMPLETEDISCOUNTORDERITEMS
select
DISCOUNTSAPPLIED.ID,
DISCOUNTSAPPLIED.ID,
DISCOUNTSAPPLIED.DISCOUNTGROUPID,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTORDERITEMS.SALESORDERID,
DISCOUNTORDERITEMS.TYPECODE,
DISCOUNTORDERITEMS.[DESCRIPTION],
DISCOUNTORDERITEMS.PROGRAMID,
DISCOUNTORDERITEMS.EVENTID,
DISCOUNTORDERITEMS.PRICETYPECODEID,
case
when DISCOUNTSAPPLIED.QUALIFYINGORDERITEMID = DISCOUNTSAPPLIED.DISCOUNTEDORDERITEMID then
case
when DISCOUNTORDERITEMS.ORDERITEMID = DISCOUNTSAPPLIED.QUALIFYINGORDERITEMID then
DISCOUNTORDERITEMS.QUANTITY - (DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS + DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMS)
else
DISCOUNTORDERITEMS.QUANTITY
end
when DISCOUNTORDERITEMS.ORDERITEMID = DISCOUNTSAPPLIED.QUALIFYINGORDERITEMID then
DISCOUNTORDERITEMS.QUANTITY - DISCOUNTSAPPLIED.NUMBEROFQUALIFYINGITEMS
when DISCOUNTORDERITEMS.ORDERITEMID = DISCOUNTSAPPLIED.DISCOUNTEDORDERITEMID then
DISCOUNTORDERITEMS.QUANTITY - DISCOUNTSAPPLIED.NUMBEROFDISCOUNTEDITEMS
else
DISCOUNTORDERITEMS.QUANTITY
end as QUANTITY,
DISCOUNTORDERITEMS.UNITPRICE
from @DISCOUNTORDERITEMS DISCOUNTORDERITEMS
inner join @DISCOUNTSAPPLIED as DISCOUNTSAPPLIED on DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID = DISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
inner join dbo.SALESORDERITEMDISCOUNTOPTION on DISCOUNTSAPPLIED.ID = SALESORDERITEMDISCOUNTOPTION.ID
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.COMPLETED = 0
--select 'SALESORDERITEMDISCOUNTOPTION', * from SALESORDERITEMDISCOUNTOPTION where SALESORDERID = @SALESORDERID
--select '@INCOMPLETEDISCOUNTORDERITEMS - with 0', * from @INCOMPLETEDISCOUNTORDERITEMS
delete from @INCOMPLETEDISCOUNTORDERITEMS where QUANTITY <= 0;
--set qualified disCOUNTs to COMPLETED if there is nothing left in the order to go through to incomplete
--and there are disCOUNTed items even if there are disCOUNTed items that still coulb be filled
update dbo.SALESORDERITEMDISCOUNTOPTION set
COMPLETED = 1,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.SALESORDERITEMDISCOUNTOPTION
inner join (
select
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
from
dbo.SALESORDERITEMDISCOUNTOPTION left outer join @INCOMPLETEDISCOUNTORDERITEMS INCOMPLETEDISCOUNTORDERITEMS on
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = INCOMPLETEDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
where
SALESORDERITEMDISCOUNTOPTION.COMPLETED = 0 and
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
INCOMPLETEDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID is null
group by
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
having
min(NUMBEROFQUALIFYINGITEMSSTILLNEEDED) = 0 and max(NUMBEROFDISCOUNTEDITEMS) > 0
) T on SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = T.DISCOUNTSCENARIOID
--delete incomplete options if there are no items left to go through the incomplete order process
delete from dbo.SALESORDERITEMDISCOUNTOPTION
from dbo.SALESORDERITEMDISCOUNTOPTION left outer join @INCOMPLETEDISCOUNTORDERITEMS INCOMPLETEDISCOUNTORDERITEMS on
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = INCOMPLETEDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.COMPLETED = 0 and
INCOMPLETEDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID is null
--select '@INCOMPLETEDISCOUNTORDERITEMS', count(*), @INCOMPLETEDISCOUNT INCOMPLETEDISCOUNT from @INCOMPLETEDISCOUNTORDERITEMS
if exists (select * from @INCOMPLETEDISCOUNTORDERITEMS) begin
--recursive call to complete incomplete discounts
if @@NESTLEVEL < 30 begin
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_CALCULATEITEMDISCOUNT @SALESORDERID, @INCOMPLETEDISCOUNTORDERITEMS, @ORDERITEMDISCOUNTGROUPAVAILABLE, 1, @DISCOUNTNESTLEVEL, @CHANGEAGENTID
end
end
--determine disCOUNTs for remaining qualifying or disCOUNTed price type items
--build this off of the SALESORDERITEMDISCOUNTOPTION table once all disCOUNTs for this run
--are COMPLETED
declare @RESULTINGDISCOUNTORDERITEMS as UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM;
insert into @RESULTINGDISCOUNTORDERITEMS
select
DISCOUNTSAPPLIED.RESULTINGDISCOUNTSCENARIOID,
DISCOUNTSAPPLIED.ID,
null,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTORDERITEMS.SALESORDERID,
DISCOUNTORDERITEMS.TYPECODE,
DISCOUNTORDERITEMS.[DESCRIPTION],
DISCOUNTORDERITEMS.PROGRAMID,
DISCOUNTORDERITEMS.EVENTID,
DISCOUNTORDERITEMS.PRICETYPECODEID,
DISCOUNTORDERITEMS.QUANTITY -
(
coalesce((
select sum(SO.NUMBEROFDISCOUNTEDITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where
SO.SALESORDERID = @SALESORDERID and
SO.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID and
SO.DISCOUNTEDORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID
), 0)
+ coalesce((
select sum(SO.NUMBEROFQUALIFYINGITEMS)
from dbo.SALESORDERITEMDISCOUNTOPTION SO
where
SO.SALESORDERID = @SALESORDERID and
SO.DISCOUNTTYPECODE = 1 and
SO.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID and
SO.QUALIFYINGORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID
), 0)
) as QUANTITY,
DISCOUNTORDERITEMS.UNITPRICE
from
@DISCOUNTORDERITEMS DISCOUNTORDERITEMS
inner join
@DISCOUNTSAPPLIED DISCOUNTSAPPLIED on DISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID = DISCOUNTSAPPLIED.PARENTDISCOUNTSCENARIOID
inner join
dbo.SALESORDERITEMDISCOUNTOPTION on DISCOUNTSAPPLIED.ID = SALESORDERITEMDISCOUNTOPTION.ID
where
SALESORDERITEMDISCOUNTOPTION.SALESORDERID = @SALESORDERID and
SALESORDERITEMDISCOUNTOPTION.COMPLETED = 1 and
--only need to include items which have discount options with the items that were discounted in this parent scenario
--ie if Imax-Shark adult tickets were used to qualify and discounted, then only items for SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION records need to be included
--that contain Imax-Shark because the remaining items will be processed as part of a separate scenario
exists (
select
*
from
@ORDERITEMDISCOUNTGROUPAVAILABLE ORDERITEMDISCOUNTGROUPAVAILABLE_1
inner join
@ORDERITEMDISCOUNTGROUPAVAILABLE ORDERITEMDISCOUNTGROUPAVAILABLE_2 on
ORDERITEMDISCOUNTGROUPAVAILABLE_1.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID = ORDERITEMDISCOUNTGROUPAVAILABLE_2.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID
where
ORDERITEMDISCOUNTGROUPAVAILABLE_1.SalesORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID and
(ORDERITEMDISCOUNTGROUPAVAILABLE_2.SalesORDERITEMID = SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID or
ORDERITEMDISCOUNTGROUPAVAILABLE_2.SalesORDERITEMID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID)
)
--can be less than 0 when coming through an incomplete run
delete from @RESULTINGDISCOUNTORDERITEMS where QUANTITY <= 0;
declare @SCENARIOMATCH as UDT_APPLYDISCOUNTS_SCENARIOMATCH;
insert into @SCENARIOMATCH
select
RDOI_1.PARENTDISCOUNTSCENARIOID,
RDOI_2.PARENTDISCOUNTSCENARIOID,
count(RDOI_2.PARENTDISCOUNTSCENARIOID)
from
@RESULTINGDISCOUNTORDERITEMS RDOI_1
inner join
@RESULTINGDISCOUNTORDERITEMS RDOI_2 on
RDOI_1.ORDERITEMID = RDOI_2.ORDERITEMID and
RDOI_1.QUANTITY = RDOI_2.QUANTITY
where
RDOI_1.PARENTDISCOUNTSCENARIOID <> RDOI_2.PARENTDISCOUNTSCENARIOID
group by
RDOI_1.PARENTDISCOUNTSCENARIOID, RDOI_2.PARENTDISCOUNTSCENARIOID
delete from @RESULTINGDISCOUNTORDERITEMS
from
@RESULTINGDISCOUNTORDERITEMS RESULTINGDISCOUNTORDERITEMS
inner join
@SCENARIOMATCH SCENARIOMATCH
on RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID and
SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT = (select count(*) from @RESULTINGDISCOUNTORDERITEMS where PARENTDISCOUNTSCENARIOID = RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID)
where
RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID > SCENARIOMATCH.CURRENTDISCOUNTSCENARIOID
delete from @SCENARIOMATCH
insert into @SCENARIOMATCH
select
RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID,
SALESORDERITEMSCENARIOSDONE.DISCOUNTSCENARIOID,
count(SALESORDERITEMSCENARIOSDONE.DISCOUNTSCENARIOID)
from
@RESULTINGDISCOUNTORDERITEMS RESULTINGDISCOUNTORDERITEMS
inner join
dbo.SALESORDERITEMSCENARIOSDONE on
RESULTINGDISCOUNTORDERITEMS.SALESORDERID = SALESORDERITEMSCENARIOSDONE.SALESORDERID and
RESULTINGDISCOUNTORDERITEMS.ORDERITEMID = SALESORDERITEMSCENARIOSDONE.SALESORDERITEMID and
RESULTINGDISCOUNTORDERITEMS.QUANTITY = SALESORDERITEMSCENARIOSDONE.QUANTITY
group by
RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID,
SALESORDERITEMSCENARIOSDONE.DISCOUNTSCENARIOID
delete from @RESULTINGDISCOUNTORDERITEMS
from
@RESULTINGDISCOUNTORDERITEMS RESULTINGDISCOUNTORDERITEMS
inner join
@SCENARIOMATCH SCENARIOMATCH
on RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID and
SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT = (select count(*) from @RESULTINGDISCOUNTORDERITEMS where PARENTDISCOUNTSCENARIOID = RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID)
where
RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID > SCENARIOMATCH.CURRENTDISCOUNTSCENARIOID
insert into dbo.SALESORDERITEMSCENARIOSDONE (
ID,
SALESORDERID,
DISCOUNTSCENARIOID,
SALESORDERITEMID,
QUANTITY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
RESULTINGDISCOUNTORDERITEMS.SALESORDERID,
RESULTINGDISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID,
RESULTINGDISCOUNTORDERITEMS.ORDERITEMID,
RESULTINGDISCOUNTORDERITEMS.QUANTITY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @RESULTINGDISCOUNTORDERITEMS RESULTINGDISCOUNTORDERITEMS
--select @INCOMPLETEDISCOUNT, '@RESULTINGDISCOUNTORDERITEMS', * from @RESULTINGDISCOUNTORDERITEMS
if exists (select 1 from @RESULTINGDISCOUNTORDERITEMS) begin
if @@NESTLEVEL < 30 begin
set @DISCOUNTNESTLEVEL += 1
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_CALCULATEITEMDISCOUNT @SALESORDERID, @RESULTINGDISCOUNTORDERITEMS, @ORDERITEMDISCOUNTGROUPAVAILABLE, 0, @DISCOUNTNESTLEVEL, @CHANGEAGENTID
end
end
return 0;