USP_SALESORDER_APPLYITEMDISCOUNTS
Applies item discounts to a sales order.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS]
(
@SALESORDERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @CURRENTDATE datetime = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SALESORDERITEMITEMDISCOUNTOPTION xml;
declare @DISCOUNTORDERITEMS_BYGROUP dbo.UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM;
declare @SOIITEMDISCOUNTOPTION table (
ID uniqueidentifier not null,
SALESORDERITEMID uniqueidentifier not null,
DISCOUNTID uniqueidentifier not null,
DISCOUNTNAME nvarchar(255) not null,
DISCOUNTAMOUNT money not null,
OPTIONID uniqueidentifier not null,
NUMBEROFDISCOUNTEDITEMS int not null
);
-- Save existing, superseded discounts so they don't magically disappear and confuse people.
declare @EXISTINGDISCOUNTS table (DISCOUNTID uniqueidentifier, ORIGINALID uniqueidentifier, APPLICATIONTYPECODE tinyint, ORIGINALNAME nvarchar(100));
if exists (
select 1
from dbo.SALESORDERITEMITEMDISCOUNT with (nolock)
inner join dbo.SALESORDERITEM with (nolock) on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
) begin
insert into @EXISTINGDISCOUNTS
select
DISCOUNT.ID,
DISCOUNT.ORIGINALDISCOUNTID,
DISCOUNT.APPLICATIONTYPECODE,
ITEMDISCOUNT.DISCOUNTNAME
from dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = ITEMDISCOUNT.SALESORDERITEMID
inner join dbo.DISCOUNT on DISCOUNT.ID = ITEMDISCOUNT.DISCOUNTID
where
SALESORDERITEM.SALESORDERID = @SALESORDERID
and DISCOUNT.SUPERSEDEDBYID is not null;
delete from [dbo].[SALESORDERITEMITEMDISCOUNT] with (rowlock)
where SALESORDERITEMID in (select ID from dbo.SALESORDERITEM with (nolock) where SALESORDERID = @SALESORDERID);
end
declare @PARENTDISCOUNTSCENARIOID uniqueidentifier = newid();
declare @DISCOUNTORDERITEMSWITHMERCHANDISE table (
PARENTDISCOUNTSCENARIOID uniqueidentifier,
PARENTID uniqueidentifier,
PARENTDISCOUNTSCENARIODISCOUNTGROUPID uniqueidentifier,
ORDERITEMID uniqueidentifier,
TYPECODE tinyint,
DESCRIPTION nvarchar(255),
PROGRAMID uniqueidentifier,
EVENTID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
QUANTITY decimal(20,4),
UNITPRICE money,
MERCHANDISEDEPARTMENTID uniqueidentifier,
MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
MERCHANDISEPRODUCTID uniqueidentifier,
ISDISCOUNTABLE bit
)
insert into @DISCOUNTORDERITEMSWITHMERCHANDISE
select
@PARENTDISCOUNTSCENARIOID as PARENTDISCOUNTSCENARIOID,
@PARENTDISCOUNTSCENARIOID as PARENTID,
null as PARENTDISCOUNTSCENARIODISCOUNTGROUPID,
SALESORDERITEM.ID as ORDERITEMID,
SALESORDERITEM.TYPECODE as TYPECODE,
SALESORDERITEM.DESCRIPTION as DESCRIPTION,
SALESORDERITEMTICKET.PROGRAMID as PROGRAMID,
isnull(SALESORDERITEMTICKET.EVENTID, SALESORDERITEMTICKET.PROGRAMID) as EVENTID,
SALESORDERITEMTICKET.PRICETYPECODEID as PRICETYPECODEID,
SALESORDERITEM.QUANTITY as QUANTITY,
SALESORDERITEM.PRICE as UNITPRICE,
SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID as MERCHANDISEDEPARTMENTID,
SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID as MERCHANDISEPRODUCTINSTANCEID,
MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID as MERCHANDISEPRODUCTID,
isnull(MERCHANDISEPRODUCT.ISDISCOUNTABLE, 1) as ISDISCOUNTABLE
from
dbo.SALESORDERITEM
left outer join
dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
left outer join
dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
left outer join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
left outer join
dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
left outer join
dbo.MERCHANDISEPRODUCT on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
where
SALESORDERID = @SALESORDERID and
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null and
SALESORDERITEM.TYPECODE in (0, 14) -- Ticket, Merchandise
if @@rowcount = 0 begin
-- We've already deleted existing discounts
-- and there are no new discountable items
return 0;
end
declare @SALESMETHODID uniqueidentifier;
select @SALESMETHODID = ID
from dbo.SALESMETHOD
where TYPECODE = (select SALESMETHODTYPECODE from dbo.SALESORDER where ID = @SALESORDERID);
declare @SALESMETHODAVAILABLEITEMDISCOUNTS table (ID uniqueidentifier, APPLICATIONTYPECODE tinyint);
insert into @SALESMETHODAVAILABLEITEMDISCOUNTS (ID, APPLICATIONTYPECODE)
select
AVAILABLEDISCOUNTS.ID,
AVAILABLEDISCOUNTS.APPLICATIONTYPECODE
from dbo.UFN_SALESMETHOD_AVAILABLEDISCOUNTS(@SALESMETHODID, 1) AVAILABLEDISCOUNTS -- applies to items
inner join dbo.DISCOUNT on AVAILABLEDISCOUNTS.ID = DISCOUNT.ID
left join @EXISTINGDISCOUNTS EXISTINGDISCOUNTS on EXISTINGDISCOUNTS.ORIGINALID = DISCOUNT.ORIGINALDISCOUNTID
where EXISTINGDISCOUNTS.DISCOUNTID is null
-- Ignore new version of discount and use the older version that was already on the order
insert into @SALESMETHODAVAILABLEITEMDISCOUNTS (ID, APPLICATIONTYPECODE)
select DISCOUNTID, APPLICATIONTYPECODE
from @EXISTINGDISCOUNTS;
--find active, available and eligible discounts for sales order
declare @SALESORDERAVAILABLEDISCOUNTS dbo.UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT;
insert into @SALESORDERAVAILABLEDISCOUNTS
select
newid(),
[DISCOUNT].[ID],
case
when DISCOUNT.APPLICATIONTYPECODE = 0 then -- Automatically
0
else
1
end
from
@SALESMETHODAVAILABLEITEMDISCOUNTS as [DISCOUNT]
left outer join
dbo.SALESORDERMANUALDISCOUNT on SALESORDERMANUALDISCOUNT.DISCOUNTID = DISCOUNT.ID and SALESORDERMANUALDISCOUNT.SALESORDERID = @SALESORDERID
where
(
[DISCOUNT].[APPLICATIONTYPECODE] = 0 -- Automatically
or SALESORDERMANUALDISCOUNT.DISCOUNTID is not null
)
and [dbo].[UFN_DISCOUNT_ELIGIBLEFORORDER]([DISCOUNT].[ID], @SALESORDERID) = 1; -- This may be an issue if there are a lot of discounts
if @@rowcount = 0 begin
-- There are no available item discounts
return 0;
end
declare @DISCOUNTORDERITEMSXML xml = (
select
PARENTDISCOUNTSCENARIOID,
PARENTID,
PARENTDISCOUNTSCENARIODISCOUNTGROUPID,
ORDERITEMID,
TYPECODE,
DESCRIPTION,
PROGRAMID,
EVENTID,
PRICETYPECODEID,
QUANTITY,
UNITPRICE,
MERCHANDISEDEPARTMENTID,
MERCHANDISEPRODUCTINSTANCEID,
MERCHANDISEPRODUCTID,
ISDISCOUNTABLE
from @DISCOUNTORDERITEMSWITHMERCHANDISE
for xml raw('ITEM'),type,root('DISCOUNTORDERITEMSXML'),binary base64
);
-- compute hash outside UFN_DISCOUNTRESULTS_FINDINCACHE so that we don't have to recompute it when we add new results to the cache (bottom of this SP)
declare @SCENARIOHASH varbinary(20) = dbo.UFN_DISCOUNTRESULTS_COMPUTEHASH(@SALESORDERID, @SALESORDERAVAILABLEDISCOUNTS, @DISCOUNTORDERITEMSXML);
if @SCENARIOHASH is not null and exists (select 1 from dbo.CACHEDDISCOUNTRESULT where INPUTHASH = @SCENARIOHASH)
begin
insert into dbo.SALESORDERITEMITEMDISCOUNT
(
ID,
SALESORDERITEMID,
DISCOUNTID,
DISCOUNTNAME,
AMOUNT,
NUMBEROFDISCOUNTEDITEMS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
CACHEDDISCOUNTRESULT.SALESORDERITEMID,
CACHEDDISCOUNTRESULT.DISCOUNTID,
coalesce(EXISTINGDISCOUNTS.ORIGINALNAME, DISCOUNT.NAME) DISCOUNTNAME,
CACHEDDISCOUNTRESULT.AMOUNT,
CACHEDDISCOUNTRESULT.NUMBEROFDISCOUNTEDITEMS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
(
select CACHEDTICKETDISCOUNT.DISCOUNTID, SALESORDERITEM.ID SALESORDERITEMID, CACHEDTICKETDISCOUNT.AMOUNT, CACHEDTICKETDISCOUNT.NUMBEROFDISCOUNTEDITEMS
from dbo.CACHEDDISCOUNTRESULTAPPLICATION CACHEDTICKETDISCOUNT
inner join dbo.SALESORDERITEMTICKET TICKETITEM
on TICKETITEM.PROGRAMID = CACHEDTICKETDISCOUNT.PROGRAMID
and (TICKETITEM.EVENTID is null or TICKETITEM.EVENTID = CACHEDTICKETDISCOUNT.EVENTID)
and TICKETITEM.PRICETYPECODEID = CACHEDTICKETDISCOUNT.PRICETYPECODEID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKETITEM.ID
where CACHEDTICKETDISCOUNT.INPUTHASH = @SCENARIOHASH
and SALESORDERITEM.SALESORDERID = @SALESORDERID
union all
select CACHEDMERCHDISCOUNT.DISCOUNTID, SALESORDERITEM.ID SALESORDERITEMID, CACHEDMERCHDISCOUNT.AMOUNT, CACHEDMERCHDISCOUNT.NUMBEROFDISCOUNTEDITEMS
from dbo.CACHEDDISCOUNTRESULTAPPLICATION CACHEDMERCHDISCOUNT
inner join dbo.SALESORDERITEMMERCHANDISE MERCHITEM on MERCHITEM.MERCHANDISEPRODUCTINSTANCEID = CACHEDMERCHDISCOUNT.MERCHANDISEPRODUCTINSTANCEID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = MERCHITEM.ID
where CACHEDMERCHDISCOUNT.INPUTHASH = @SCENARIOHASH
and SALESORDERITEM.SALESORDERID = @SALESORDERID
) CACHEDDISCOUNTRESULT
left join dbo.DISCOUNT on DISCOUNT.ID = CACHEDDISCOUNTRESULT.DISCOUNTID
left join @EXISTINGDISCOUNTS EXISTINGDISCOUNTS on EXISTINGDISCOUNTS.DISCOUNTID = CACHEDDISCOUNTRESULT.DISCOUNTID;
update dbo.CACHEDDISCOUNTRESULT
set LASTUSED = @CURRENTDATE, TIMESUSED += 1, CACHEUSAGETIME = cast(getdate() - @CURRENTDATE as time(4))
where INPUTHASH = @SCENARIOHASH;
return 0;
end
declare @DISCOUNTOPTIONID uniqueidentifier = newid();
exec [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS_NONGROUPSIZEDISCOUNTS] @SALESORDERID, @DISCOUNTOPTIONID, @SALESORDERAVAILABLEDISCOUNTS, @CHANGEAGENTID, @SALESORDERITEMITEMDISCOUNTOPTION output, @DISCOUNTORDERITEMSXML;
insert into @SOIITEMDISCOUNTOPTION
(
ID,
SALESORDERITEMID,
DISCOUNTID,
DISCOUNTNAME,
DISCOUNTAMOUNT,
OPTIONID,
NUMBEROFDISCOUNTEDITEMS
)
select
T.item.value('(@ID)[1]','uniqueidentifier'),
T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTNAME)[1]','nvarchar(255)'),
T.item.value('(@DISCOUNTAMOUNT)[1]','money'),
T.item.value('(@OPTIONID)[1]','uniqueidentifier'),
T.item.value('(@NUMBEROFDISCOUNTEDITEMS)[1]','integer')
from
@SALESORDERITEMITEMDISCOUNTOPTION.nodes('/SALESORDERITEMITEMDISCOUNTOPTION/ITEM') T(item);
if exists -- Short circuiting extra calls to calculate item stored procedures
(
select *
from @SALESORDERAVAILABLEDISCOUNTS SALESORDERAVAILABLEDISCOUNTS
inner join dbo.DISCOUNT on SALESORDERAVAILABLEDISCOUNTS.DISCOUNTID = DISCOUNT.ID
where DISCOUNT.DISCOUNTTYPECODE = 2
)
begin
declare @DISCOUNTORDERITEMSXML_BYGROUP xml;
/*
Contains Merchandise information for group discounts.
*/
set @DISCOUNTORDERITEMSXML_BYGROUP =
(
select
DISCOUNTID,
DISCOUNTGROUPID,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT,
UNITPRICE,
ORDERITEMID,
PRICETYPECODEID,
MERCHANDISEPRODUCTID,
DISCOUNTNAME,
QUANTITY
from
(
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMSWITHMERCHANDISE.UNITPRICE,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE,
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTID,
DISCOUNT.NAME DISCOUNTNAME,
DISCOUNTORDERITEMSWITHMERCHANDISE.QUANTITY
from @SALESORDERAVAILABLEDISCOUNTS SALESORDERAVAILABLEDISCOUNTS
inner join dbo.DISCOUNT on
SALESORDERAVAILABLEDISCOUNTS.DISCOUNTID = DISCOUNT.ID
inner join dbo.DISCOUNTGROUP on
DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
inner join dbo.DISCOUNTGROUPDETAIL on
DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
outer apply
@DISCOUNTORDERITEMSWITHMERCHANDISE as DISCOUNTORDERITEMSWITHMERCHANDISE
where
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID is not null and
DISCOUNT.DISCOUNTTYPECODE = 2 and -- By quantity
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 0 and -- All discountable merchandise
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMSWITHMERCHANDISE.ISDISCOUNTABLE = 1
union
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMSWITHMERCHANDISE.UNITPRICE,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE DISCOUNTMERCHANDISEFORCODE,
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTID,
DISCOUNT.NAME DISCOUNTNAME,
DISCOUNTORDERITEMSWITHMERCHANDISE.QUANTITY
from @SALESORDERAVAILABLEDISCOUNTS SALESORDERAVAILABLEDISCOUNTS
inner join dbo.DISCOUNT on
SALESORDERAVAILABLEDISCOUNTS.DISCOUNTID = DISCOUNT.ID
inner join dbo.DISCOUNTGROUP on
DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
inner join dbo.DISCOUNTGROUPDETAIL on
DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
inner join dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT on
DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID = DISCOUNTGROUPDETAIL.ID
inner join
@DISCOUNTORDERITEMSWITHMERCHANDISE DISCOUNTORDERITEMSWITHMERCHANDISE on
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEDEPARTMENTID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID
where DISCOUNT.DISCOUNTTYPECODE = 2 and -- By quantity
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 1 and -- Merchandise department
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMSWITHMERCHANDISE.ISDISCOUNTABLE = 1
union
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMSWITHMERCHANDISE.UNITPRICE,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE DISCOUNTMERCHANDISEFORCODE,
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTID,
DISCOUNT.NAME DISCOUNTNAME,
DISCOUNTORDERITEMSWITHMERCHANDISE.QUANTITY
from @SALESORDERAVAILABLEDISCOUNTS SALESORDERAVAILABLEDISCOUNTS
inner join dbo.DISCOUNT on
SALESORDERAVAILABLEDISCOUNTS.DISCOUNTID = DISCOUNT.ID
inner join dbo.DISCOUNTGROUP on
DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
inner join dbo.DISCOUNTGROUPDETAIL on
DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
inner join dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM on
DISCOUNTGROUPDETAILMERCHANDISEITEM.ID = DISCOUNTGROUPDETAIL.ID
inner join @DISCOUNTORDERITEMSWITHMERCHANDISE as DISCOUNTORDERITEMSWITHMERCHANDISE on
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTID = DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID
where DISCOUNT.DISCOUNTTYPECODE = 2 and -- By quantity
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 2 and -- Merchandise
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMSWITHMERCHANDISE.ISDISCOUNTABLE = 1
) ORDERITEMS
for xml raw('ITEM'),type,root('DISCOUNTORDERITEMSXML_BYGROUP'),binary base64
)
insert into @DISCOUNTORDERITEMS_BYGROUP
select distinct
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTDISCOUNTSCENARIOID,
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTDISCOUNTSCENARIODISCOUNTGROUPID,
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID,
@SALESORDERID,
DISCOUNTORDERITEMSWITHMERCHANDISE.TYPECODE,
DISCOUNTORDERITEMSWITHMERCHANDISE.DESCRIPTION,
DISCOUNTORDERITEMSWITHMERCHANDISE.PROGRAMID,
DISCOUNTORDERITEMSWITHMERCHANDISE.EVENTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.PRICETYPECODEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.QUANTITY,
DISCOUNTORDERITEMSWITHMERCHANDISE.UNITPRICE
from @DISCOUNTORDERITEMSWITHMERCHANDISE as DISCOUNTORDERITEMSWITHMERCHANDISE
inner join [dbo].[DISCOUNTGROUPDETAILPROGRAM]
on [DISCOUNTGROUPDETAILPROGRAM].[PROGRAMID] = DISCOUNTORDERITEMSWITHMERCHANDISE.PROGRAMID
inner join [dbo].[DISCOUNTGROUPDETAIL]
on [DISCOUNTGROUPDETAIL].[ID] = [DISCOUNTGROUPDETAILPROGRAM].[ID]
inner join [dbo].[DISCOUNTGROUP]
on [DISCOUNTGROUP].[ID] = [DISCOUNTGROUPDETAIL].[DISCOUNTGROUPID]
inner join [dbo].[DISCOUNT]
on [DISCOUNTGROUP].[DISCOUNTID] = [DISCOUNT].[ID]
inner join @SALESORDERAVAILABLEDISCOUNTS [SOAD]
on [SOAD].[DISCOUNTID] = [DISCOUNT].[ID]
where [DISCOUNT].[DISCOUNTTYPECODE] = 2; -- By quantity
set @DISCOUNTOPTIONID = newid()
set @SALESORDERITEMITEMDISCOUNTOPTION = null
exec [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS_GROUPSIZEDISCOUNTS] @SALESORDERID, @DISCOUNTOPTIONID, @SALESORDERAVAILABLEDISCOUNTS, @DISCOUNTORDERITEMS_BYGROUP, @CHANGEAGENTID, @SALESORDERITEMITEMDISCOUNTOPTION output, @DISCOUNTORDERITEMSXML_BYGROUP;
-- Put all qualifying items into @SOIITEMDISCOUNTOPTION
insert into @SOIITEMDISCOUNTOPTION
(
ID,
SALESORDERITEMID,
DISCOUNTID,
DISCOUNTNAME,
DISCOUNTAMOUNT,
OPTIONID,
NUMBEROFDISCOUNTEDITEMS
)
select
T.item.value('(@ID)[1]','uniqueidentifier'),
T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTNAME)[1]','nvarchar(255)'),
T.item.value('(@DISCOUNTAMOUNT)[1]','money'),
T.item.value('(@OPTIONID)[1]','uniqueidentifier'),
T.item.value('(@NUMBEROFDISCOUNTEDITEMS)[1]','integer')
from
@SALESORDERITEMITEMDISCOUNTOPTION.nodes('/SALESORDERITEMITEMDISCOUNTOPTION/ITEM') T(item);
declare @DISCOUNTORDERITEMS_EXCLUDINGGROUPS_XML xml = (
select
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTDISCOUNTSCENARIOID ,
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.PARENTDISCOUNTSCENARIODISCOUNTGROUPID,
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID,
DISCOUNTORDERITEMSWITHMERCHANDISE.TYPECODE,
DISCOUNTORDERITEMSWITHMERCHANDISE.DESCRIPTION,
DISCOUNTORDERITEMSWITHMERCHANDISE.PROGRAMID,
DISCOUNTORDERITEMSWITHMERCHANDISE.EVENTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.PRICETYPECODEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.QUANTITY,
DISCOUNTORDERITEMSWITHMERCHANDISE.UNITPRICE,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEDEPARTMENTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
DISCOUNTORDERITEMSWITHMERCHANDISE.MERCHANDISEPRODUCTID,
DISCOUNTORDERITEMSWITHMERCHANDISE.ISDISCOUNTABLE
from @DISCOUNTORDERITEMSWITHMERCHANDISE as DISCOUNTORDERITEMSWITHMERCHANDISE
where
DISCOUNTORDERITEMSWITHMERCHANDISE.ORDERITEMID not in (
select SALESORDERITEMID
from @SOIITEMDISCOUNTOPTION
where OPTIONID = @DISCOUNTOPTIONID
)
for xml raw('ITEM'),type,root('DISCOUNTORDERITEMSXML'),binary base64
)
-- Remove all the discounts that aren't really discounts
-- (we do this after the previous insert statement because $0 discounts still count
-- towards the required number for group discounts).
delete from @SOIITEMDISCOUNTOPTION where [DISCOUNTAMOUNT] <= 0;
set @SALESORDERITEMITEMDISCOUNTOPTION = null
exec [dbo].[USP_SALESORDER_APPLYITEMDISCOUNTS_NONGROUPSIZEDISCOUNTS] @SALESORDERID, @DISCOUNTOPTIONID, @SALESORDERAVAILABLEDISCOUNTS, @CHANGEAGENTID, @SALESORDERITEMITEMDISCOUNTOPTION output, @DISCOUNTORDERITEMS_EXCLUDINGGROUPS_XML;
insert into @SOIITEMDISCOUNTOPTION
(
ID,
SALESORDERITEMID,
DISCOUNTID,
DISCOUNTNAME,
DISCOUNTAMOUNT,
OPTIONID,
NUMBEROFDISCOUNTEDITEMS
)
select
T.item.value('(@ID)[1]','uniqueidentifier'),
T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTID)[1]','uniqueidentifier'),
T.item.value('(@DISCOUNTNAME)[1]','nvarchar(255)'),
T.item.value('(@DISCOUNTAMOUNT)[1]','money'),
T.item.value('(@OPTIONID)[1]','uniqueidentifier'),
T.item.value('(@NUMBEROFDISCOUNTEDITEMS)[1]','integer')
from
@SALESORDERITEMITEMDISCOUNTOPTION.nodes('/SALESORDERITEMITEMDISCOUNTOPTION/ITEM') T(item);
end
declare @SELECTEDOPTIONID uniqueidentifier = null;
select top(1)
@SELECTEDOPTIONID = [OPTIONID]
from @SOIITEMDISCOUNTOPTION
group by [OPTIONID]
order by sum([DISCOUNTAMOUNT]) desc;
insert into [dbo].[SALESORDERITEMITEMDISCOUNT]
(
[ID],
[SALESORDERITEMID],
[DISCOUNTID],
[DISCOUNTNAME],
[AMOUNT],
[NUMBEROFDISCOUNTEDITEMS],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
SALESORDERITEMID,
ITEMDISCOUNTOPTION.DISCOUNTID,
coalesce(EXISTINGDISCOUNTS.ORIGINALNAME, ITEMDISCOUNTOPTION.DISCOUNTNAME),
DISCOUNTAMOUNT,
NUMBEROFDISCOUNTEDITEMS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SOIITEMDISCOUNTOPTION ITEMDISCOUNTOPTION
left join @EXISTINGDISCOUNTS EXISTINGDISCOUNTS on EXISTINGDISCOUNTS.DISCOUNTID = ITEMDISCOUNTOPTION.DISCOUNTID
where ITEMDISCOUNTOPTION.OPTIONID = @SELECTEDOPTIONID;
-- Recheck existence of scenario in cache to prevent adding a duplicate, which will fail the unique index on the next access of this cache entry (because the original and duplicate will be both updated with the same LASTUSED)
if @SCENARIOHASH is not null and not exists (select 1 from dbo.CACHEDDISCOUNTRESULT where INPUTHASH = @SCENARIOHASH)
begin
declare @ELAPSEDTIME time(4) = cast(getdate() - @CURRENTDATE as time(4));
exec dbo.USP_DISCOUNTRESULTS_ADDTOCACHE @SCENARIOHASH, @SALESORDERAVAILABLEDISCOUNTS, @SALESORDERID, @ELAPSEDTIME, @CHANGEAGENTID;
end
return 0;