USP_SALESORDER_APPLYITEMDISCOUNTS_NONGROUPSIZEDISCOUNTS
Calculate options for standard and with required purchase discounts
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @DISCOUNTOPTIONID | uniqueidentifier | IN | |
| @SALESORDERAVAILABLEDISCOUNTS | UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @SALESORDERITEMITEMDISCOUNTOPTION | xml | INOUT | |
| @DISCOUNTORDERITEMSXML | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_NONGROUPSIZEDISCOUNTS
(
@SALESORDERID uniqueidentifier,
@DISCOUNTOPTIONID uniqueidentifier,
@SALESORDERAVAILABLEDISCOUNTS dbo.UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT readonly,
@CHANGEAGENTID uniqueidentifier,
@SALESORDERITEMITEMDISCOUNTOPTION xml output,
@DISCOUNTORDERITEMSXML xml = null
)
as
set nocount on;
declare @CURRENTDATE datetime = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
-- Cleaning up scenarios to start fresh.
delete from dbo.SALESORDERITEMSCENARIOSDONE with (rowlock) where SALESORDERID = @SALESORDERID;
delete from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION with (rowlock) where SALESORDERID = @SALESORDERID;
declare @DISCOUNTORDERITEMSTABLE as table (
PARENTDISCOUNTSCENARIOID uniqueidentifier,
PARENTID uniqueidentifier,
PARENTDISCOUNTSCENARIODISCOUNTGROUPID uniqueidentifier,
TYPECODE tinyint,
PROGRAMID uniqueidentifier,
EVENTID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
ORDERITEMID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
MERCHANDISEPRODUCTID uniqueidentifier,
MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
DESCRIPTION nvarchar(255),
UNITPRICE money,
QUANTITY decimal(20,4),
ISDISCOUNTABLE bit
)
insert into @DISCOUNTORDERITEMSTABLE
select
DISCOUNTORDERITEMSXML.item.value('(@PARENTDISCOUNTSCENARIOID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@PARENTID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@PARENTDISCOUNTSCENARIODISCOUNTGROUPID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@TYPECODE)[1]', 'tinyint'),
DISCOUNTORDERITEMSXML.item.value('(@PROGRAMID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@EVENTID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@PRICETYPECODEID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@ORDERITEMID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@MERCHANDISEDEPARTMENTID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@MERCHANDISEPRODUCTID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@MERCHANDISEPRODUCTINSTANCEID)[1]', 'uniqueidentifier'),
DISCOUNTORDERITEMSXML.item.value('(@DESCRIPTION)[1]', 'nvarchar(255)'),
DISCOUNTORDERITEMSXML.item.value('(@UNITPRICE)[1]', 'money'),
DISCOUNTORDERITEMSXML.item.value('(@QUANTITY)[1]', 'decimal(20,4)'),
DISCOUNTORDERITEMSXML.item.value('(@ISDISCOUNTABLE)[1]', 'bit')
from
@DISCOUNTORDERITEMSXML.nodes('/DISCOUNTORDERITEMSXML/ITEM') DISCOUNTORDERITEMSXML(item);
if @@rowcount = 0 begin
return 0;
end
declare @TOTALITEMQUANTITY decimal(20,4);
select @TOTALITEMQUANTITY = sum(QUANTITY)
from @DISCOUNTORDERITEMSTABLE;
/*
This table contains all the items in the current order that could possibly trigger a discount.
QUALIFYINGGROUPITEMID relates to the rule associated with the "With required purchase" discount (a.k.a. BOGO).
Merchandise Qualifying -> Merchandise Discount has different rules than Ticket Qualifying -> Ticket Discounted.
*/
declare @AVAILABLEITEMQUALIFYINGGROUPS table
(
DISCOUNTID uniqueidentifier,
DISCOUNTGROUPID uniqueidentifier,
APPLIEDMANUALLY bit,
DESCRIPTION nvarchar(200),
UNITPRICE money,
NUMBERTOPURCHASE int,
QUALIFYINGGROUPITEMID uniqueidentifier,
ORDERITEMID uniqueidentifier,
MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
PRICETYPECODEID uniqueidentifier
);
insert into @AVAILABLEITEMQUALIFYINGGROUPS
(
DISCOUNTID,
DISCOUNTGROUPID,
APPLIEDMANUALLY,
DESCRIPTION,
UNITPRICE,
NUMBERTOPURCHASE,
QUALIFYINGGROUPITEMID,
ORDERITEMID,
MERCHANDISEPRODUCTINSTANCEID,
PRICETYPECODEID
)
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNTORDERITEMS.UNITPRICE,
DISCOUNT.NUMBERTOPURCHASE,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then -- Same merchandise as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then -- Same department as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
null
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
@DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS
where
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID is not null and
DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 4 and -- Any merchandise
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0 -- Qualifying
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNTORDERITEMS.UNITPRICE,
DISCOUNT.NUMBERTOPURCHASE,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then -- Same merchandise as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then -- Same department as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
null
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID
where DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 1 and -- Specified Merchandise Departments
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0 -- Qualifying
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNTORDERITEMS.UNITPRICE,
DISCOUNT.NUMBERTOPURCHASE,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then -- Same merchandise as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then -- Same department as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
null
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID = DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID
where DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 2 and -- Specified Merchandise Items
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNTORDERITEMS.UNITPRICE,
DISCOUNT.NUMBERTOPURCHASE,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case
when DISCOUNT.DISCOUNTTICKETSFORCODE = 0 then -- Same event as the purchased event
DISCOUNTORDERITEMS.EVENTID
else
DISCOUNTORDERITEMS.PROGRAMID
end
else
DISCOUNT.ID
end
QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null,
PRICETYPES.PRICETYPECODEID
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.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.PROGRAMID = DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
inner join dbo.DISCOUNTQUALIFYINGPRICETYPE
on DISCOUNT.ID = DISCOUNTQUALIFYINGPRICETYPE.DISCOUNTID -- has to have a price type mapped if it's order
--derived table to get all unique price types available for program/event
--price comes from salesorderitem table
inner join
(
select
PROGRAMPRICE.PROGRAMID,
PROGRAMPRICE.PRICETYPECODEID
from
dbo.PROGRAMPRICE
union
select
[EVENT].PROGRAMID,
PROGRAMEVENTPRICE.PRICETYPECODEID
from
dbo.PROGRAMEVENTPRICE inner join dbo.[EVENT] on
PROGRAMEVENTPRICE.EVENTID = [EVENT].ID
) PRICETYPES on
DISCOUNTORDERITEMS.PROGRAMID = PRICETYPES.PROGRAMID and
DISCOUNTORDERITEMS.PRICETYPECODEID = PRICETYPES.PRICETYPECODEID
left outer join dbo.DISCOUNTQUALIFYINGPRICETYPE DQ on
DISCOUNT.ID = DQ.DISCOUNTID and
PRICETYPES.PRICETYPECODEID = DQ.PRICETYPECODEID and
DQ.PRICETYPECODEID is not null
where
DISCOUNT.APPLIESTOCODE = 1 and -- Item
DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0 and -- Qualifying
DISCOUNTQUALIFYINGPRICETYPE.PRICETYPECODEID is null and -- applies to all price types
DQ.ID is null
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNTORDERITEMS.UNITPRICE,
DISCOUNT.NUMBERTOPURCHASE,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case
when DISCOUNT.DISCOUNTTICKETSFORCODE = 0 then
DISCOUNTORDERITEMS.EVENTID
else
DISCOUNTORDERITEMS.PROGRAMID
end
else
DISCOUNT.ID
end
QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null,
DISCOUNTQUALIFYINGPRICETYPE.PRICETYPECODEID
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.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on DISCOUNTORDERITEMS.PROGRAMID = DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
inner join dbo.DISCOUNTQUALIFYINGPRICETYPE on DISCOUNT.ID = DISCOUNTQUALIFYINGPRICETYPE.DISCOUNTID and DISCOUNTORDERITEMS.PRICETYPECODEID = DISCOUNTQUALIFYINGPRICETYPE.PRICETYPECODEID
where
DISCOUNT.APPLIESTOCODE = 1 and -- Item
DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0 and -- Qualifying
DISCOUNTQUALIFYINGPRICETYPE.PRICETYPECODEID is not null;
/*
Grouping these together so that they will match discounted groups later on.
*/
update @AVAILABLEITEMQUALIFYINGGROUPS
set DISCOUNTGROUPID = DISCOUNT.ID
from @AVAILABLEITEMQUALIFYINGGROUPS AIQG
inner join dbo.DISCOUNT on AIQG.DISCOUNTID = DISCOUNT.ID
where (DISCOUNT.DISCOUNTITEMTYPECODE <> DISCOUNT.QUALIFYINGITEMTYPECODE)
or (DISCOUNT.DISCOUNTITEMTYPECODE = 1 and DISCOUNT.QUALIFYINGITEMTYPECODE = 1)
/*
This table contains all the items in the current order that could possibly be discounted.
DISCOUNTEDGROUPITEMID relates to the rule associated with the "With required purchase" discount (a.k.a. BOGO).
Merchandise Qualifying -> Merchandise Discount has different rules than Ticket Qualifying -> Ticket Discounted.
Also, checking MERCHANDISEAMOUNT and MERCHANDISEPERCENT if the discounted item could be merchandise.
*/
declare @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE as table
(
[DISCOUNTID] [uniqueidentifier],
[DISCOUNTGROUPID] [uniqueidentifier],
[ITEMNAME] [nvarchar](255),
[DISCOUNTTYPECODE] [tinyint],
[CALCULATIONTYPECODE] [tinyint],
[UNITDISCOUNTAMOUNT] [decimal](20, 4),
[UNITPRICE] [money],
[NUMBERTODISCOUNT] [int],
[DISCOUNTFORCODE] [tinyint],
[LIMITDISCOUNTSPERORDER] [bit],
[NUMBEROFDISCOUNTSPERORDER] [int],
[DISCOUNTEDGROUPITEMID] [uniqueidentifier],
[DISCOUNTEDORDERITEMID] [uniqueidentifier],
[PRICETYPECODE] [uniqueidentifier],
[MERCHANDISEPRODUCTINSTANCEID] [uniqueidentifier]
)
insert into @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then -- Amount off
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end as UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then -- Same merchandise as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then -- Same department as purchased merchandise
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID
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
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
outer apply
@DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS
where
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID is not null and
DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (5, 6) and -- any discountable department or discountable item
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1 -- discounted
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then -- Amount off
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 1 and -- Specific merchandise departments
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end as UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario +- to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID = DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 2 and -- Specific merchandise items
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNTPRICETYPE.AMOUNT
else
DISCOUNTPRICETYPE.[PERCENT]
end as UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTTICKETSFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case
when DISCOUNT.DISCOUNTTICKETSFORCODE = 0 then
DISCOUNTORDERITEMS.EVENTID
else
DISCOUNTORDERITEMS.PROGRAMID
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
PRICETYPES.PRICETYPECODEID,
null
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.DISCOUNTGROUPDETAILPROGRAM on
DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.PROGRAMID = DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
inner join dbo.DISCOUNTPRICETYPE on DISCOUNT.ID = DISCOUNTPRICETYPE.DISCOUNTID
and (DISCOUNTORDERITEMS.PRICETYPECODEID = [DISCOUNTPRICETYPE].[PRICETYPECODEID]
or ([DISCOUNTPRICETYPE].[PRICETYPECODEID] is null and DISCOUNTORDERITEMS.PRICETYPECODEID not in (select coalesce([PRICETYPECODEID], '00000000-0000-0000-0000-000000000000') from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTID] = [DISCOUNT].[ID])))
--derived table to get all unique price types available for program/event
--price comes from salesorderitem table
inner join
(select
PROGRAMPRICE.PROGRAMID,
PROGRAMPRICE.PRICETYPECODEID
from
dbo.PROGRAMPRICE
union
select
[EVENT].PROGRAMID,
PROGRAMEVENTPRICE.PRICETYPECODEID
from
dbo.PROGRAMEVENTPRICE inner join dbo.[EVENT] on
PROGRAMEVENTPRICE.EVENTID = [EVENT].ID
) PRICETYPES on
DISCOUNTORDERITEMS.PROGRAMID = PRICETYPES.PROGRAMID and
DISCOUNTORDERITEMS.PRICETYPECODEID = PRICETYPES.PRICETYPECODEID
left outer join dbo.DISCOUNTPRICETYPE DP on
DISCOUNT.ID = DP.DISCOUNTID and
PRICETYPES.PRICETYPECODEID = DP.PRICETYPECODEID and
DP.PRICETYPECODEID is not null
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where
DISCOUNT.APPLIESTOCODE = 1 and -- Item
DISCOUNT.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTPRICETYPE.PRICETYPECODEID is null and
DP.ID is null
union all
select
DISCOUNT.ID,
DISCOUNTGROUP.ID,
DISCOUNTORDERITEMS.DESCRIPTION,
Discount.DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNTPRICETYPE.AMOUNT
else
DISCOUNTPRICETYPE.[PERCENT]
end as UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTTICKETSFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case
when DISCOUNT.DISCOUNTTICKETSFORCODE = 0 then
DISCOUNTORDERITEMS.EVENTID
else
DISCOUNTORDERITEMS.PROGRAMID
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
DISCOUNTPRICETYPE.PRICETYPECODEID,
null
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.DISCOUNTGROUPDETAILPROGRAM on
DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.PROGRAMID = DISCOUNTGROUPDETAILPROGRAM.PROGRAMID
inner join dbo.DISCOUNTPRICETYPE on
DISCOUNT.ID = DISCOUNTPRICETYPE.DISCOUNTID
and (DISCOUNTORDERITEMS.PRICETYPECODEID = [DISCOUNTPRICETYPE].[PRICETYPECODEID]
or ([DISCOUNTPRICETYPE].[PRICETYPECODEID] is null and DISCOUNTORDERITEMS.PRICETYPECODEID not in (select coalesce([PRICETYPECODEID], '00000000-0000-0000-0000-000000000000') from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTID] = [DISCOUNT].[ID])))
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where
DISCOUNT.APPLIESTOCODE = 1 and -- Item
Discount.DISCOUNTTYPECODE = 1 and -- BOGO
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTPRICETYPE.PRICETYPECODEID is not null
/*
Making sure that qualifying group items will match discount group items. This takes care of merchandise
and "No restriction" groups (Merchandise qualifying and Ticket discounted).
*/
update @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
set DISCOUNTGROUPID = DISCOUNT.ID
from @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE ADJIT
inner join dbo.DISCOUNT on ADJIT.DISCOUNTID = DISCOUNT.ID
where DISCOUNT.DISCOUNTITEMTYPECODE <> DISCOUNT.QUALIFYINGITEMTYPECODE
or (DISCOUNT.DISCOUNTITEMTYPECODE = 1 and DISCOUNT.QUALIFYINGITEMTYPECODE = 1)
declare @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION table (
ID uniqueidentifier,
DISCOUNTID uniqueidentifier,
APPLIEDMANUALLY bit,
LIMITDISCOUNTSPERORDER bit,
NUMBEROFDISCOUNTSPERORDER integer,
DISCOUNTGROUPID uniqueidentifier,
DISCOUNTTICKETSFORCODE integer,
QUALIFYINGGROUPITEMID uniqueidentifier,
QUALIFYINGORDERITEMID uniqueidentifier,
QUALIFYINGPRICETYPECODEID uniqueidentifier,
QUALIFYINGUNITPRICE money,
NUMBERTOPURCHASE integer,
DISCOUNTEDGROUPITEMID uniqueidentifier,
DISCOUNTEDPROGRAMID uniqueidentifier,
DISCOUNTEDORDERITEMID uniqueidentifier,
DISCOUNTEDPRICETYPECODEID uniqueidentifier,
DISCOUNTEDUNITPRICE money,
NUMBERTODISCOUNT integer,
DISCOUNTTYPECODE tinyint,
CALCULATIONTYPECODE tinyint,
UNITDISCOUNTAMOUNT decimal(5,2)
)
-- Building BOGO combinations
insert into @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION(
ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
)
select
newid(),
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTID,
AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.APPLIEDMANUALLY,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.LIMITDISCOUNTSPERORDER,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.NUMBEROFDISCOUNTSPERORDER,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTGROUPID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTFORCODE,
AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.QUALIFYINGGROUPITEMID,
AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.ORDERITEMID,
isnull(AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.PRICETYPECODEID, AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID) ,
AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.UNITPRICE,
AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.NUMBERTOPURCHASE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDGROUPITEMID,
case
when exists(
select 1
from @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
where DISCOUNTFORCODE = 1 and
DISCOUNTEDGROUPITEMID = DISCOUNTORDERITEMS.PROGRAMID
) then
DISCOUNTORDERITEMS.PROGRAMID
when exists(
select 1
from @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
where DISCOUNTFORCODE = 0 and
DISCOUNTEDGROUPITEMID = DISCOUNTORDERITEMS.EVENTID
) then
DISCOUNTORDERITEMS.EVENTID
else -- Must be BOGO with Merch
isnull(AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID, AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID)
end DISCOUNTEDPROGRAMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDORDERITEMID,
isnull(AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.PRICETYPECODE, AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID),
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.UNITPRICE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.NUMBERTODISCOUNT,
1 DISCOUNTTYPECODE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.CALCULATIONTYPECODE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.UNITDISCOUNTAMOUNT
from @AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS
inner join @AVAILABLEITEMQUALIFYINGGROUPS AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS on
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTGROUPID = AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.DISCOUNTGROUPID and
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDGROUPITEMID = AVAILABLEQUALIFYINGDISCOUNTGROUPITEMS.QUALIFYINGGROUPITEMID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID
-- Standard Item Ticket Discounts
insert into @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
(ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
)
select
newid(),
DISCOUNT.ID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end
[LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end
[NUMBEROFDISCOUNTSPERORDER],
DISCOUNTGROUP.ID,
0 DISCOUNTTICKETSFORCODE,
DISCOUNTORDERITEMS.EVENTID as QUALIFYINGGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID as QUALIFYINGORDERITEMID,
PRICETYPES.PRICETYPECODEID QUALIFYINGPRICETYPECODEID,
DISCOUNTORDERITEMS.UNITPRICE,
1 NUMBERTOPURCHASE,
--DISCOUNTORDERITEMSXML.item.value('EVENTID[1]', 'uniqueidentifier') DISCOUNTEDGROUPITEMID,
case
when exists(select
1
from
@AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
where
DISCOUNTFORCODE = 1 and
DISCOUNTEDGROUPITEMID = DISCOUNTORDERITEMS.PROGRAMID) then
DISCOUNTORDERITEMS.PROGRAMID
else
DISCOUNTORDERITEMS.EVENTID
end DISCOUNTEDGROUPITEMID,
case -- This column is only used for auditing it looks like.
when exists(select
1
from
@AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
where
DISCOUNTFORCODE = 1 and
DISCOUNTEDGROUPITEMID = DISCOUNTORDERITEMS.PROGRAMID) then
DISCOUNTORDERITEMS.PROGRAMID
else
DISCOUNTORDERITEMS.EVENTID
end DISCOUNTEDPROGRAMID,
DISCOUNTORDERITEMS.ORDERITEMID,
PRICETYPES.PRICETYPECODEID,
DISCOUNTORDERITEMS.UNITPRICE,
1 NUMBERTODISCOUNT,
0 DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNTPRICETYPE.AMOUNT
else
DISCOUNTPRICETYPE.[PERCENT]
end
UNITDISCOUNTAMOUNT
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.DISCOUNTGROUPDETAILPROGRAM on
DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTGROUPDETAILPROGRAM.PROGRAMID = DISCOUNTORDERITEMS.PROGRAMID
inner join dbo.DISCOUNTPRICETYPE on DISCOUNT.ID = DISCOUNTPRICETYPE.DISCOUNTID
and (DISCOUNTORDERITEMS.PRICETYPECODEID = [DISCOUNTPRICETYPE].[PRICETYPECODEID]
or ([DISCOUNTPRICETYPE].[PRICETYPECODEID] is null and DISCOUNTORDERITEMS.PRICETYPECODEID not in (select coalesce([PRICETYPECODEID], '00000000-0000-0000-0000-000000000000') from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTID] = [DISCOUNT].[ID])))
--derived table to get all unique price types available for program/event
--price comes from salesorderitem table
inner join
(select
PROGRAMPRICE.PROGRAMID,
PROGRAMPRICE.PRICETYPECODEID
from
dbo.PROGRAMPRICE
union
select
[EVENT].PROGRAMID,
PROGRAMEVENTPRICE.PRICETYPECODEID
from
dbo.PROGRAMEVENTPRICE inner join dbo.[EVENT] on
PROGRAMEVENTPRICE.EVENTID = [EVENT].ID) PRICETYPES on
DISCOUNTORDERITEMS.PROGRAMID = PRICETYPES.PROGRAMID and
DISCOUNTORDERITEMS.PRICETYPECODEID = PRICETYPES.PRICETYPECODEID
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on [SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where
DISCOUNT.APPLIESTOCODE = 1 and
DISCOUNT.APPLIESTOTICKETS = 1 and
DISCOUNT.DISCOUNTTYPECODE = 0; -- only standard
-- Building standard discounts for merchandise
with STANDARDDISCOUNTS_CTE as
(
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end
NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end
LIMITDISCOUNTSPERORDER,
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as NUMBEROFDISCOUNTSPERORDER,
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end
DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY APPLIEDMANUALLY
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
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
outer apply
@DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS
where
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID is not null and
DISCOUNT.DISCOUNTTYPECODE = 0 and
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 0 and
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1
union all
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end
[LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end
DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY APPLIEDMANUALLY
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where DISCOUNT.DISCOUNTTYPECODE = 0 and
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 1 and
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1
union all
select
DISCOUNT.ID DISCOUNTID,
DISCOUNTGROUP.ID DISCOUNTGROUPID,
DISCOUNTORDERITEMS.DESCRIPTION,
DISCOUNT.DISCOUNTTYPECODE DISCOUNTTYPECODE,
DISCOUNT.CALCULATIONTYPECODE CALCULATIONTYPECODE,
case
when DISCOUNT.CALCULATIONTYPECODE = 0 then
DISCOUNT.MERCHANDISEAMOUNT
else
DISCOUNT.MERCHANDISEPERCENT
end
UNITDISCOUNTAMOUNT,
DISCOUNTORDERITEMS.UNITPRICE,
case
when DISCOUNT.NUMBERTODISCOUNTTYPECODE = 1 then
--unlimited scenario - to not make this a special case later,
--set number to discount equal to total number of tickets
@TOTALITEMQUANTITY
else
DISCOUNT.NUMBERTODISCOUNT
end as NUMBERTODISCOUNT,
DISCOUNT.DISCOUNTMERCHANDISEFORCODE DISCOUNTMERCHANDISEFORCODE,
case
when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 or [SALESORDERDISCOUNTLIMITOVERRIDE].[ID] is not null then
1
else
0
end as [LIMITDISCOUNTSPERORDER],
case
when [SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] is null or ([SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER] > [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER] and [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1) then
[DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
else
[SALESORDERDISCOUNTLIMITOVERRIDE].[NUMBEROFDISCOUNTSPERORDER]
end as [NUMBEROFDISCOUNTSPERORDER],
case when DISCOUNT.QUALIFYINGITEMTYPECODE = DISCOUNT.DISCOUNTITEMTYPECODE then
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 0 then
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID
else
case when DISCOUNT.DISCOUNTMERCHANDISEFORCODE = 1 then
DISCOUNTORDERITEMS.MERCHANDISEDEPARTMENTID
else
DISCOUNT.ID
end
end
else
DISCOUNT.ID
end as DISCOUNTEDGROUPITEMID,
DISCOUNTORDERITEMS.ORDERITEMID,
null PRICETYPECODEID,
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID,
SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY APPLIEDMANUALLY
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 @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
DISCOUNTORDERITEMS.MERCHANDISEPRODUCTID = DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] on
[SALESORDERDISCOUNTLIMITOVERRIDE].[DISCOUNTID] = [DISCOUNT].[ID] and [SALESORDERDISCOUNTLIMITOVERRIDE].[SALESORDERID] = @SALESORDERID
where DISCOUNT.DISCOUNTTYPECODE = 0 and
DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 2 and
DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1 and
DISCOUNTORDERITEMS.ISDISCOUNTABLE = 1
)
-- Standard Merchandise Discounts
insert into @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
(ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
)
select
newid(),
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.APPLIEDMANUALLY,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.LIMITDISCOUNTSPERORDER,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.NUMBEROFDISCOUNTSPERORDER,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTGROUPID,
0 DISCOUNTTICKETSFORCODE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDGROUPITEMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.ORDERITEMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.UNITPRICE,
1 NUMBERTOPURCHASE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.DISCOUNTEDGROUPITEMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID
DISCOUNTEDPROGRAMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.ORDERITEMID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.MERCHANDISEPRODUCTINSTANCEID,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.UNITPRICE,
1 NUMBERTODISCOUNT,
0 DISCOUNTTYPECODE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.CALCULATIONTYPECODE,
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.UNITDISCOUNTAMOUNT
from STANDARDDISCOUNTS_CTE AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS
inner join @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS on
AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMS.ORDERITEMID = DISCOUNTORDERITEMS.ORDERITEMID
delete @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION]
where
--remove combinations where discounted unit price is 0
DISCOUNTEDUNITPRICE = 0 or
--delete combinations where the qualifying unit price is less than the discounted unit price and
--the inverse exists (this removes scenarios like buy a child - lower price - and get a discounted adult - higher price
--unless the discount configuration explicitly says this)
(
QUALIFYINGUNITPRICE < DISCOUNTEDUNITPRICE and
exists (
select *
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION AD
where
AD.DISCOUNTGROUPID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID and
AD.QUALIFYINGPRICETYPECODEID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPRICETYPECODEID and
AD.DISCOUNTEDPRICETYPECODEID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGPRICETYPECODEID
)
) or
(
--remove discount combinations that are duplicate combination (but not the same discount group record)
--that have an equal to or less than unit discount amount
--also make sure to keep in a manually applied one if it is the same as an automatic
exists(
select *
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION AD
where
AD.DISCOUNTGROUPID <> SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTGROUPID and
AD.DISCOUNTTYPECODE = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTTYPECODE and
AD.QUALIFYINGPRICETYPECODEID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGPRICETYPECODEID and
AD.QUALIFYINGGROUPITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGGROUPITEMID and
AD.DISCOUNTEDPRICETYPECODEID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPRICETYPECODEID and
AD.DISCOUNTEDGROUPITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDGROUPITEMID and
--***************need to remove if everything is the same, but limitdiscountsperorder
--if one is unlimited need to remove the other one; only keep if both are limited
AD.LIMITDISCOUNTSPERORDER = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER and
AD.LIMITDISCOUNTSPERORDER = 0 and
AD.NUMBERTODISCOUNT = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTODISCOUNT and
AD.NUMBERTOPURCHASE = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NUMBERTOPURCHASE and
AD.CALCULATIONTYPECODE = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.CALCULATIONTYPECODE and
(
(AD.UNITDISCOUNTAMOUNT > SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.UNITDISCOUNTAMOUNT) or
(
AD.UNITDISCOUNTAMOUNT = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.UNITDISCOUNTAMOUNT and
AD.ID < SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID and
(
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.APPLIEDMANUALLY = 0 or
(SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.APPLIEDMANUALLY = 1 and AD.APPLIEDMANUALLY = 1)
)
)
)
)
)
declare @LEVEL tinyint = 0;
--@LEVEL check is arbitrary and should not ever be hit
--however, if there is a bug in the exists check, we don't want this to be an infinite loop
--This loop makes sure all dependant discount groups are given the same group id so that the same item
--does not qualify or get discounted more than once.
while @LEVEL < 32 begin
update @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
set SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.NEWDISCOUNTEDPROGRAMID
from
@SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION]
inner join (
select
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID,
min(cast(NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID as nvarchar(36))) NEWDISCOUNTEDPROGRAMID
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION]
inner join @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
on (
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID or
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID or
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID or
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID
)
group by
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID
having
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID <> min(cast(NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID as nvarchar(36)))
) as NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
on SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID = NEWSALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDPROGRAMID
if @@rowcount = 0 begin
break;
end
set @LEVEL += 1;
end
if exists(select 1 from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION)
begin
--maps order items to available discount group combinations
--used later to limit the number of scenarios to be calculated
declare @ORDERITEMDISCOUNTGROUPAVAILABLE dbo.UDT_APPLYDISCOUNTS_ORDERITEMDISCOUNTGROUPAVAILABLE;
insert into @ORDERITEMDISCOUNTGROUPAVAILABLE
select
DISCOUNTORDERITEMS.ORDERITEMID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID
from @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS
inner join @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION]
on
DISCOUNTORDERITEMS.ORDERITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.QUALIFYINGORDERITEMID or
DISCOUNTORDERITEMS.ORDERITEMID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.DISCOUNTEDORDERITEMID
declare @OPTIMIZEDDISCOUNTORDERITEMS dbo.UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM
-- Updating this to include all discountorderitems..
insert into @OPTIMIZEDDISCOUNTORDERITEMS
select
DISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIOID,
DISCOUNTORDERITEMS.PARENTID,
DISCOUNTORDERITEMS.PARENTDISCOUNTSCENARIODISCOUNTGROUPID,
DISCOUNTORDERITEMS.ORDERITEMID,
@SALESORDERID,
DISCOUNTORDERITEMS.TYPECODE,
DISCOUNTORDERITEMS.DESCRIPTION,
isnull(DISCOUNTORDERITEMS.PROGRAMID, DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID),
isnull(DISCOUNTORDERITEMS.EVENTID, DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID),
isnull(DISCOUNTORDERITEMS.PRICETYPECODEID, DISCOUNTORDERITEMS.MERCHANDISEPRODUCTINSTANCEID),
DISCOUNTORDERITEMS.QUANTITY,
DISCOUNTORDERITEMS.UNITPRICE
from @DISCOUNTORDERITEMSTABLE as DISCOUNTORDERITEMS
delete dbo.SALESORDERITEMDISCOUNTOPTION with (rowlock)
where SALESORDERID = @SALESORDERID;
delete dbo.[SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION] with (rowlock)
where [SALESORDERID] = @SALESORDERID;
insert into dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION(
ID,
SALESORDERID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT,
DATECHANGED,
DATEADDED,
CHANGEDBYID,
ADDEDBYID
)
select
ID,
@SALESORDERID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
-- Calculate item discounts
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_CALCULATEITEMDISCOUNT @SALESORDERID, @OPTIMIZEDDISCOUNTORDERITEMS, @ORDERITEMDISCOUNTGROUPAVAILABLE, 0, 1, @CHANGEAGENTID
delete @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION;
insert into @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION(
ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
)
select
ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION with (nolock)
where [SALESORDERID] = @SALESORDERID
declare @SALESORDERITEMDISCOUNTOPTION table (
ID uniqueidentifier,
NUMBERID integer,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID uniqueidentifier,
DISCOUNTSCENARIOGROUPID uniqueidentifier,
DISCOUNTGROUPID uniqueidentifier,
DISCOUNTTYPECODE tinyint,
DISCOUNTTICKETSFORCODE tinyint,
DISCOUNTSCENARIOID uniqueidentifier,
APPLIEDMANUALLY tinyint,
NUMBEROFTIMESAPPLIED integer,
QUALIFYINGORDERITEMID uniqueidentifier,
QUALIFYINGGROUPITEMID uniqueidentifier,
QUALIFYINGPRICETYPECODEID uniqueidentifier,
NUMBEROFQUALIFYINGITEMS integer,
NUMBEROFQUALIFYINGITEMSSTILLNEEDED integer,
TOTALQUALIFYINGQUANTITY integer,
DISCOUNTEDORDERITEMID uniqueidentifier,
DISCOUNTEDGROUPITEMID uniqueidentifier,
DISCOUNTEDPRICETYPECODEID uniqueidentifier,
NUMBEROFDISCOUNTEDITEMS integer,
NUMBEROFDISCOUNTEDITEMSSTILLOPEN integer,
TOTALDISCOUNTEDQUANTITY integer,
DISCOUNTAMOUNT money,
COMPLETED tinyint
)
-- Remove any remaining incomplete options
insert into @SALESORDERITEMDISCOUNTOPTION
select
ID,
NUMBERID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID,
DISCOUNTSCENARIOGROUPID,
DISCOUNTGROUPID,
DISCOUNTTYPECODE,
DISCOUNTTICKETSFORCODE,
DISCOUNTSCENARIOID,
APPLIEDMANUALLY,
NUMBEROFTIMESAPPLIED,
QUALIFYINGORDERITEMID,
QUALIFYINGGROUPITEMID,
QUALIFYINGPRICETYPECODEID,
NUMBEROFQUALIFYINGITEMS,
NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
TOTALQUALIFYINGQUANTITY,
DISCOUNTEDORDERITEMID,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPRICETYPECODEID,
NUMBEROFDISCOUNTEDITEMS,
NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
TOTALDISCOUNTEDQUANTITY,
DISCOUNTAMOUNT,
COMPLETED
from dbo.SALESORDERITEMDISCOUNTOPTION with (nolock)
where
[SALESORDERID] = @SALESORDERID and
COMPLETED = 1;
declare @SCENARIOMATCH dbo.UDT_APPLYDISCOUNTS_SCENARIOMATCH;
-- Remove duplicate scenarios from the SALESORDERITEMDISCOUNTOPTION table that resulted from Incomplete discounts.
-- These need to be processed initially because they lead to different "new" orders, but should not be included in
-- determining the final discount combinations.
insert into @SCENARIOMATCH
select
S_1.DISCOUNTSCENARIOID,
S_2.DISCOUNTSCENARIOID,
count(S_2.DISCOUNTSCENARIOID)
from
@SALESORDERITEMDISCOUNTOPTION S_1
inner join @SALESORDERITEMDISCOUNTOPTION S_2
on
S_1.DISCOUNTGROUPID = S_2.DISCOUNTGROUPID and
S_1.QUALIFYINGORDERITEMID = S_2.QUALIFYINGORDERITEMID and
S_1.NUMBEROFQUALIFYINGITEMS = S_2.NUMBEROFQUALIFYINGITEMS
where S_1.NUMBERID < S_2.NUMBERID
group by S_1.DISCOUNTSCENARIOID, S_2.DISCOUNTSCENARIOID
delete from @SCENARIOMATCH
from @SCENARIOMATCH as SCENARIOMATCH
where SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT <> (
select count(*)
from @SALESORDERITEMDISCOUNTOPTION S_1
inner join @SALESORDERITEMDISCOUNTOPTION S_2 on
S_1.DISCOUNTGROUPID = S_2.DISCOUNTGROUPID and
S_1.DISCOUNTEDORDERITEMID = S_2.DISCOUNTEDORDERITEMID and
S_1.NUMBEROFDISCOUNTEDITEMS = S_2.NUMBEROFDISCOUNTEDITEMS
where
S_1.DISCOUNTSCENARIOID = SCENARIOMATCH.CURRENTDISCOUNTSCENARIOID and
S_2.DISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID
);
delete from @SALESORDERITEMDISCOUNTOPTION
from @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
inner join @SCENARIOMATCH SCENARIOMATCH
on SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID
where
SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT = (
select count(*)
from @SALESORDERITEMDISCOUNTOPTION S
where S.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
)
declare @DISCOUNTSCENARIOORDERITEMQUANTITY dbo.UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOORDERITEMQUANTITY;
-- Create order item aggregations to use in creating discount scenario combinations
insert into @DISCOUNTSCENARIOORDERITEMQUANTITY
select
DISCOUNTSCENARIOID,
ORDERITEMID,
sum(QUANTITY) QUANTITY
from (
select
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID ORDERITEMID,
sum(SALESORDERITEMDISCOUNTOPTION.NUMBEROFDISCOUNTEDITEMS) QUANTITY
from @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
group by
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID
union all
select
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID ORDERITEMID,
sum(SALESORDERITEMDISCOUNTOPTION.NUMBEROFQUALIFYINGITEMS) QUANTITY
from @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
where SALESORDERITEMDISCOUNTOPTION.DISCOUNTTYPECODE <> 0
group by
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID
) T
group by DISCOUNTSCENARIOID, ORDERITEMID
-- When an independent discount grouping only has one discount group id then the quantity grouping can be done by price type
-- instead of order item.
--**************************************************************
-- Removes scenarios where another scenario exists with equal or less than the quantity for the same items
-- (for both qualifying and discounted) and the discount amount is more.
-- These scenarios will never win when being combined with other so don't need to attempt.
--**************************************************************
insert into @SCENARIOMATCH
select
DSOQ_1.DISCOUNTSCENARIOID,
DSOQ_2.DISCOUNTSCENARIOID,
count(DSOQ_2.DISCOUNTSCENARIOID)
from @DISCOUNTSCENARIOORDERITEMQUANTITY DSOQ_1
inner join @DISCOUNTSCENARIOORDERITEMQUANTITY DSOQ_2
on
DSOQ_1.ORDERITEMID = DSOQ_2.ORDERITEMID and
DSOQ_1.QUANTITY <= DSOQ_2.QUANTITY
--using ID for below inner join to the SALESORDERITEMDISCOUNTOPTION so only one row will be returned
--because of the way the SALESORDERITEMDISCOUNTOPTION is built, one ID in a discount scenario will match
--the DISCOUNTSCENARIOID
inner join @SALESORDERITEMDISCOUNTOPTION S_1
on DSOQ_1.DISCOUNTSCENARIOID = S_1.ID
inner join @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION]
on S_1.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID = SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.ID
inner join @SALESORDERITEMDISCOUNTOPTION S_2
on DSOQ_2.DISCOUNTSCENARIOID = S_2.ID
outer apply (
select sum(DISCOUNTAMOUNT) as AMOUNT from @SALESORDERITEMDISCOUNTOPTION where DISCOUNTSCENARIOID = S_1.DISCOUNTSCENARIOID
) as SCENARIO1AMOUNT
outer apply (
select sum(DISCOUNTAMOUNT) as AMOUNT from @SALESORDERITEMDISCOUNTOPTION where DISCOUNTSCENARIOID = S_2.DISCOUNTSCENARIOID
) as SCENARIO2AMOUNT
where
DSOQ_1.DISCOUNTSCENARIOID <> DSOQ_2.DISCOUNTSCENARIOID
and S_1.DISCOUNTSCENARIOGROUPID = S_2.DISCOUNTSCENARIOGROUPID
and (
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION.LIMITDISCOUNTSPERORDER = 0
or S_1.DISCOUNTGROUPID = S_2.DISCOUNTGROUPID
)
and (
SCENARIO1AMOUNT.AMOUNT > SCENARIO2AMOUNT.AMOUNT
or (
SCENARIO1AMOUNT.AMOUNT = SCENARIO2AMOUNT.AMOUNT
and S_1.NUMBERID < S_2.NUMBERID
)
)
group by
DSOQ_1.DISCOUNTSCENARIOID,
DSOQ_2.DISCOUNTSCENARIOID
delete from @SALESORDERITEMDISCOUNTOPTION
from @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
inner join @SCENARIOMATCH SCENARIOMATCH
on SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID
where
SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT = (select count(*) from @DISCOUNTSCENARIOORDERITEMQUANTITY DSOQ where DSOQ.DISCOUNTSCENARIOID = SCENARIOMATCH.MATCHINGDISCOUNTSCENARIOID) and
SCENARIOMATCH.DISCOUNTSCENARIOMATCHCOUNT = (select count(*) from @DISCOUNTSCENARIOORDERITEMQUANTITY DSOQ where DSOQ.DISCOUNTSCENARIOID = SCENARIOMATCH.CURRENTDISCOUNTSCENARIOID)
-- Delete scenarios that no longer exist
delete from @DISCOUNTSCENARIOORDERITEMQUANTITY
from @DISCOUNTSCENARIOORDERITEMQUANTITY DISCOUNTSCENARIOORDERITEMQUANTITY
left outer join @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
on DISCOUNTSCENARIOORDERITEMQUANTITY.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
where SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID is null
insert into dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
(ID,
SALESORDERID,
SCENARIOBATCHID,
DISCOUNTSCENARIOGROUPID,
SCENARIOCOMBINATIONID,
DISCOUNTSCENARIOID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
newid(),
@SALESORDERID,
null,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOGROUPID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
where SALESORDERITEMDISCOUNTOPTION.COMPLETED = 1
group by
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOGROUPID
declare @DISCOUNTSCENARIOITEMCOUNT dbo.UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOITEMCOUNT
insert into @DISCOUNTSCENARIOITEMCOUNT
select
DISCOUNTSCENARIOID,
sum(QUANTITY)
from @DISCOUNTSCENARIOORDERITEMQUANTITY
group by DISCOUNTSCENARIOID
--select '@DISCOUNTSCENARIOITEMCOUNT', * from @DISCOUNTSCENARIOITEMCOUNT order by TOTALORDERITEMCOUNT desc
declare @MAXSCENARIOITEMS integer;
declare @MINSCENARIOITEMS integer;
select @MAXSCENARIOITEMS = max(TOTALORDERITEMCOUNT), @MINSCENARIOITEMS = min(TOTALORDERITEMCOUNT) from @DISCOUNTSCENARIOITEMCOUNT
declare @ITEMCOUNTTOPROCESS integer
set @ITEMCOUNTTOPROCESS = @MAXSCENARIOITEMS
--select @MAXSCENARIOITEMS, @MINSCENARIOITEMS, @ITEMCOUNTTOPROCESS
if @ITEMCOUNTTOPROCESS >= @MINSCENARIOITEMS begin
delete from dbo.SALESORDERITEMDISCOUNTOPTION with (rowlock)
where
[SALESORDERID] = @SALESORDERID and
not exists (
select 1
from @SALESORDERITEMDISCOUNTOPTION
where [ID] = [SALESORDERITEMDISCOUNTOPTION].[ID]
)
delete dbo.[SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION] with (rowlock)
where
[SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION].[SALESORDERID] = @SALESORDERID and
not exists (
select 1
from @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
where [ID] = [SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION].[ID]
)
while @ITEMCOUNTTOPROCESS >= @MINSCENARIOITEMS begin
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_DETERMINEDISCOUNTCOMBINATIONS @SALESORDERID, @ITEMCOUNTTOPROCESS, null, @OPTIMIZEDDISCOUNTORDERITEMS, @DISCOUNTSCENARIOORDERITEMQUANTITY, @DISCOUNTSCENARIOITEMCOUNT, @CHANGEAGENTID
set @ITEMCOUNTTOPROCESS -= 1
end
delete @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION;
insert into @SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION(
ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
)
select
ID,
DISCOUNTID,
APPLIEDMANUALLY,
LIMITDISCOUNTSPERORDER,
NUMBEROFDISCOUNTSPERORDER,
DISCOUNTGROUPID,
DISCOUNTTICKETSFORCODE,
QUALIFYINGGROUPITEMID,
QUALIFYINGORDERITEMID,
QUALIFYINGPRICETYPECODEID,
QUALIFYINGUNITPRICE,
NUMBERTOPURCHASE,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPROGRAMID,
DISCOUNTEDORDERITEMID,
DISCOUNTEDPRICETYPECODEID,
DISCOUNTEDUNITPRICE,
NUMBERTODISCOUNT,
DISCOUNTTYPECODE,
CALCULATIONTYPECODE,
UNITDISCOUNTAMOUNT
from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION with (nolock)
where [SALESORDERID] = @SALESORDERID
delete @SALESORDERITEMDISCOUNTOPTION;
insert into @SALESORDERITEMDISCOUNTOPTION
select
ID,
NUMBERID,
SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATIONID,
DISCOUNTSCENARIOGROUPID,
DISCOUNTGROUPID,
DISCOUNTTYPECODE,
DISCOUNTTICKETSFORCODE,
DISCOUNTSCENARIOID,
APPLIEDMANUALLY,
NUMBEROFTIMESAPPLIED,
QUALIFYINGORDERITEMID,
QUALIFYINGGROUPITEMID,
QUALIFYINGPRICETYPECODEID,
NUMBEROFQUALIFYINGITEMS,
NUMBEROFQUALIFYINGITEMSSTILLNEEDED,
TOTALQUALIFYINGQUANTITY,
DISCOUNTEDORDERITEMID,
DISCOUNTEDGROUPITEMID,
DISCOUNTEDPRICETYPECODEID,
NUMBEROFDISCOUNTEDITEMS,
NUMBEROFDISCOUNTEDITEMSSTILLOPEN,
TOTALDISCOUNTEDQUANTITY,
DISCOUNTAMOUNT,
COMPLETED
from dbo.SALESORDERITEMDISCOUNTOPTION with (nolock)
where [SALESORDERID] = @SALESORDERID;
end
declare @DISCOUNTSCENARIOIDTOUSE dbo.UDT_APPLYDISCOUNTS_DISCOUNTSCENARIOIDTOUSE
declare @SCENARIOCOMBINATIONS table(SCENARIOCOMBINATIONID uniqueidentifier, AMOUNT money, APPLIEDMANUALLY integer)
insert into @SCENARIOCOMBINATIONS
select
SDC.SCENARIOCOMBINATIONID,
sum(SALESORDERITEMDISCOUNTOPTION.DISCOUNTAMOUNT),
sum(SALESORDERITEMDISCOUNTOPTION.APPLIEDMANUALLY)
from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION SDC
inner join @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
on SDC.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
where SDC.SALESORDERID = @SALESORDERID
group by SDC.SCENARIOCOMBINATIONID
insert into @DISCOUNTSCENARIOIDTOUSE
select distinct SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID
from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
inner join @SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION] on
SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOID = SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID
inner join (
select SCENARIOCOMBINATIONID
from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION with (nolock)
where
SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID = (
select top 1 SC.SCENARIOCOMBINATIONID
from @SCENARIOCOMBINATIONS SC
inner join dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION SDC with (nolock)
on SDC.SCENARIOCOMBINATIONID = SC.SCENARIOCOMBINATIONID
where SDC.DISCOUNTSCENARIOGROUPID = SALESORDERDISCOUNTSCENARIOCOMBINATION.DISCOUNTSCENARIOGROUPID
order by SC.AMOUNT desc, SC.APPLIEDMANUALLY desc
)
) T on
SALESORDERDISCOUNTSCENARIOCOMBINATION.SCENARIOCOMBINATIONID = T.SCENARIOCOMBINATIONID;
with OPTIONS_CTE as
(
select
newid() as ID,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID as SALESORDERITEMID,
DISCOUNT.ID as DISCOUNTID,
coalesce(DISCOUNT.NAME,'') as DISCOUNTNAME,
SALESORDERITEMDISCOUNTOPTION.DISCOUNTAMOUNT,
@DISCOUNTOPTIONID as OPTIONID,
[SALESORDERITEMDISCOUNTOPTION].[NUMBEROFDISCOUNTEDITEMS]
from
@SALESORDERITEMDISCOUNTOPTION [SALESORDERITEMDISCOUNTOPTION]
inner join @DISCOUNTSCENARIOIDTOUSE DISCOUNTSCENARIOIDTOUSE on
SALESORDERITEMDISCOUNTOPTION.DISCOUNTSCENARIOID = DISCOUNTSCENARIOIDTOUSE.DISCOUNTSCENARIOID
/*
Justification for below... I need to get a distinct set because otherwise I will have
duplicate discounts. This is because retail + ticket discounts aren't considered in
the same grouping in setup. This will ensure that only one set of groups will be used at
a time.
*/
inner join
(
select distinct
DISCOUNTGROUPID,
DISCOUNTID
from
@AVAILABLEDISCOUNTEDDISCOUNTGROUPITEMSTABLE
union
select distinct
DISCOUNTGROUP.ID,
DISCOUNTID
from dbo.DISCOUNTGROUP
inner join dbo.DISCOUNT
on DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
where DISCOUNT.DISCOUNTTYPECODE = 0
) ADGT on
SALESORDERITEMDISCOUNTOPTION.DISCOUNTGROUPID = ADGT.DISCOUNTGROUPID
inner join dbo.DISCOUNT on
ADGT.DISCOUNTID = DISCOUNT.ID
inner join dbo.SALESORDERITEM QUALIFYING on
SALESORDERITEMDISCOUNTOPTION.QUALIFYINGORDERITEMID = QUALIFYING.ID
inner join dbo.SALESORDERITEM DISCOUNTED on
SALESORDERITEMDISCOUNTOPTION.DISCOUNTEDORDERITEMID = DISCOUNTED.ID
)
select @SALESORDERITEMITEMDISCOUNTOPTION = (
select
[ID],
[SALESORDERITEMID],
[DISCOUNTID],
[DISCOUNTNAME],
convert(money, convert(decimal(17,2),[DISCOUNTAMOUNT])) as [DISCOUNTAMOUNT],
[OPTIONID],
[NUMBEROFDISCOUNTEDITEMS]
from OPTIONS_CTE
for xml raw ('ITEM'), type, root('SALESORDERITEMITEMDISCOUNTOPTION'), BINARY BASE64
);
end
return 0;