USP_RESCHEDULETICKETS_COMPLETE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @ITEMS | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @TIMESTAMP | bigint | IN |
Definition
Copy
CREATE procedure dbo.USP_RESCHEDULETICKETS_COMPLETE
(
@SALESORDERID uniqueidentifier,
@ITEMS xml,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@TIMESTAMP bigint = null
)
as
begin
declare @CURRENTTIME datetime = getdate();
-- Make sure nobody else has rescheduled this order since the form was opened.
if @TIMESTAMP <> (select TSLONG from dbo.SALESORDER where ID = @SALESORDERID) begin
raiserror('ERR_CREDITITEMS_CONFLICTINGRESCHEDULE', 13, 1);
return 1;
end
else begin
update dbo.SALESORDER
set DATECHANGED = @CURRENTTIME, CHANGEDBYID = @CHANGEAGENTID
where ID = @SALESORDERID;
end
declare @TICKETSTORESCHEDULE table (
TICKETID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
PRICE money,
ORIGINALSALESORDERITEMID uniqueidentifier,
NEWSALESORDERITEMID uniqueidentifier,
ORIGINALEVENTID uniqueidentifier,
NEWEVENTID uniqueidentifier,
REGISTRANTID uniqueidentifier -- For preregistrants.
);
insert into @TICKETSTORESCHEDULE
select
TICKET.ID,
TICKET.PRICETYPECODEID,
TICKET.PRICE,
TICKET.SALESORDERITEMTICKETID,
null,
TICKET.EVENTID,
T.item.value('(@NEWEVENTID)[1]','uniqueidentifier'),
case when T.item.value('(@REGISTRANTID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then null else T.item.value('(@REGISTRANTID)[1]','uniqueidentifier') end
from @ITEMS.nodes('/ITEMS/ITEM') T(item)
inner join dbo.TICKET on TICKET.ID = T.item.value('(@TICKETID)[1]','uniqueidentifier')
where TICKET.EVENTID <> T.item.value('(@NEWEVENTID)[1]','uniqueidentifier')
and T.item.value('(@NEWEVENTID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000';
if not exists (select 1 from @TICKETSTORESCHEDULE)
return 0;
if exists (
select 1
from @TICKETSTORESCHEDULE RESCHEDULES
inner join dbo.TICKET on TICKET.ID = RESCHEDULES.TICKETID
where TICKET.STATUSCODE = 2
or TICKET.ISREFUNDED = 1
)
begin
raiserror('BBERR_RESCHEDULE_REFUNDEDTICKET', 13, 1);
return 1;
end
-- These tables contain all of the SALESORDERITEM.* details that will need to be updated, inserted, or deleted as a result of the ticket changes.
-- Each entry in any of the tables represents the original and final values for that record (original price/amount/quantity of 0 means it's a new record, and final 0 means it will be deleted).
-- This makes the final insert/update/delete clauses much simpler because no further aggregation will be necessary.
declare @UPDATEDTICKETITEMS table (ID uniqueidentifier, PRICETYPECODEID uniqueidentifier, EVENTID uniqueidentifier, PRICE money, OLDQUANTITY decimal, NEWQUANTITY decimal);
declare @UPDATEDFEEITEMS table (ID uniqueidentifier, ORIGINALPRICE money, NEWPRICE money, [PERCENT] decimal(7,4), [DESCRIPTION] nvarchar(max), APPLIEDTOITEMID uniqueidentifier, FEEID uniqueidentifier, FEENAME nvarchar(max));
declare @UPDATEDTAXITEMS table (APPLIEDTOITEMID uniqueidentifier, TAXID uniqueidentifier, TAXITEMID uniqueidentifier, TOTALTAX decimal(7,4), TAXNAME nvarchar(max));
declare @UPDATEDITEMDISCOUNTS table (ID uniqueidentifier, ORIGINALAMOUNT money, NEWAMOUNT money, NUMBEROFTIMESAPPLIED int, APPLIEDTOITEMID uniqueidentifier, DISCOUNTID uniqueidentifier, DISCOUNTNAME nvarchar(max));
declare @UPDATEDORDERDISCOUNTDETAILS table (ID uniqueidentifier, ORIGINALAMOUNT money, NEWAMOUNT money, DISCOUNTID uniqueidentifier, APPLIEDTOITEMID uniqueidentifier, SALESORDERITEMORDERDISCOUNTID uniqueidentifier);
-- Ticket items
with TICKETITEMS as (
select TICKETITEM.ID, TICKETITEM.PRICETYPECODEID, TICKETITEM.PROGRAMID, TICKETITEM.EVENTID, SALESORDERITEM.PRICE, SALESORDERITEM.QUANTITY, COMBOITEM.TICKETCOMBINATIONID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET TICKETITEM on TICKETITEM.ID = SALESORDERITEM.ID
left join dbo.SALESORDERITEMTICKETCOMBINATION COMBOITEM on COMBOITEM.ID = TICKETITEM.ID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
and SALESORDERITEM.TYPECODE = 0
),
ORIGINALEVENTS as (
select ORIGINALSALESORDERITEMID, count(TICKETID) NUM
from @TICKETSTORESCHEDULE RESCHEDULE
group by ORIGINALSALESORDERITEMID
),
NEWEVENTS as (
select PRICETYPECODEID, NEWEVENTID, PRICE, count(TICKETID) NUM
from @TICKETSTORESCHEDULE RESCHEDULE
group by PRICETYPECODEID, NEWEVENTID, PRICE
)
insert into @UPDATEDTICKETITEMS
select
coalesce(SOURCETICKETITEMS.ID, newid()),
coalesce(SOURCETICKETITEMS.PRICETYPECODEID, NEWEVENTS.PRICETYPECODEID),
coalesce(SOURCETICKETITEMS.EVENTID, NEWEVENTS.NEWEVENTID),
coalesce(SOURCETICKETITEMS.PRICE, NEWEVENTS.PRICE),
coalesce(SOURCETICKETITEMS.QUANTITY, 0),
coalesce(SOURCETICKETITEMS.QUANTITY, 0) - coalesce(ORIGINALEVENTS.NUM, 0) + coalesce(NEWEVENTS.NUM, 0)
from TICKETITEMS SOURCETICKETITEMS
left join ORIGINALEVENTS on ORIGINALEVENTS.ORIGINALSALESORDERITEMID = SOURCETICKETITEMS.ID -- Left join here so that the full join below can match already-existing ticket items (e.g. 2/2 to 1/3)
full join NEWEVENTS
on (NEWEVENTS.NEWEVENTID = SOURCETICKETITEMS.EVENTID and NEWEVENTS.PRICETYPECODEID = SOURCETICKETITEMS.PRICETYPECODEID
and (SOURCETICKETITEMS.TICKETCOMBINATIONID is null or NEWEVENTS.NEWEVENTID is null)) -- Do not reschedule to a combo item, even if rescheduling from a combo item.
where (ORIGINALEVENTS.ORIGINALSALESORDERITEMID is not null or NEWEVENTS.NEWEVENTID is not null)
and (NEWEVENTS.NEWEVENTID is not null or SOURCETICKETITEMS.EVENTID is not null);
update RESCHEDULES
set NEWSALESORDERITEMID = UPDATEDITEMS.ID
from @TICKETSTORESCHEDULE RESCHEDULES
inner join @UPDATEDTICKETITEMS UPDATEDITEMS on UPDATEDITEMS.EVENTID = RESCHEDULES.NEWEVENTID and UPDATEDITEMS.PRICETYPECODEID = RESCHEDULES.PRICETYPECODEID;
-- TODO: Can skip a lot of this when all tickets on the salesorderitem are being rescheduled to the same event.
-- Fee items
with FEEITEMS as (
select FEEITEM.ID, FEEITEM.FEEID, FEEITEM.FEENAME, FEEITEM.SALESORDERITEMID APPLIEDTOITEMID, SALESORDERITEM.PRICE, SALESORDERITEM.[PERCENT], SALESORDERITEM.[DESCRIPTION]
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMFEE FEEITEM on FEEITEM.ID = SALESORDERITEM.ID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
and SALESORDERITEM.TYPECODE = 3
and FEEITEM.APPLIESTOCODE = 1 -- Only include item-level fees; otherwise, the NEWPRICE will be calculated as $0 for order-level fee items, and they will be deleted.
),
GROUPEDTICKETFEEAMOUNTS as (
select
TICKETFEE.SALESORDERITEMFEEID,
FEEITEMS.FEEID,
coalesce(RESCHEDULES.NEWSALESORDERITEMID, FEEITEMS.APPLIEDTOITEMID) NEWAPPLIEDTOITEMID,
sum(TICKETFEE.AMOUNT) as AMOUNT
from FEEITEMS
inner join dbo.SALESORDERITEMTICKETFEE TICKETFEE on TICKETFEE.SALESORDERITEMFEEID = FEEITEMS.ID
left join @TICKETSTORESCHEDULE RESCHEDULES on RESCHEDULES.TICKETID = TICKETFEE.TICKETID
group by TICKETFEE.SALESORDERITEMFEEID, FEEITEMS.FEEID, FEEITEMS.APPLIEDTOITEMID, RESCHEDULES.NEWSALESORDERITEMID
)
insert into @UPDATEDFEEITEMS
select
coalesce(UPDATEDFEEITEMS.ID, newid()),
coalesce(UPDATEDFEEITEMS.PRICE, 0),
sum(coalesce(GROUPEDTICKETFEEAMOUNTS.AMOUNT, 0)),
coalesce(UPDATEDFEEITEMS.[PERCENT], SOURCEFEEITEMS.[PERCENT]),
coalesce(UPDATEDFEEITEMS.[DESCRIPTION], SOURCEFEEITEMS.[DESCRIPTION]),
coalesce(UPDATEDFEEITEMS.APPLIEDTOITEMID, GROUPEDTICKETFEEAMOUNTS.NEWAPPLIEDTOITEMID),
coalesce(UPDATEDFEEITEMS.FEEID, SOURCEFEEITEMS.FEEID),
coalesce(UPDATEDFEEITEMS.FEENAME, SOURCEFEEITEMS.FEENAME)
from GROUPEDTICKETFEEAMOUNTS
inner join FEEITEMS SOURCEFEEITEMS on SOURCEFEEITEMS.ID = GROUPEDTICKETFEEAMOUNTS.SALESORDERITEMFEEID
full join FEEITEMS UPDATEDFEEITEMS on (UPDATEDFEEITEMS.APPLIEDTOITEMID = GROUPEDTICKETFEEAMOUNTS.NEWAPPLIEDTOITEMID and UPDATEDFEEITEMS.FEEID = GROUPEDTICKETFEEAMOUNTS.FEEID)
group by UPDATEDFEEITEMS.ID, UPDATEDFEEITEMS.PRICE, UPDATEDFEEITEMS.[PERCENT], UPDATEDFEEITEMS.[DESCRIPTION], UPDATEDFEEITEMS.APPLIEDTOITEMID, UPDATEDFEEITEMS.FEEID, UPDATEDFEEITEMS.FEENAME,
SOURCEFEEITEMS.[PERCENT], SOURCEFEEITEMS.[DESCRIPTION], SOURCEFEEITEMS.FEEID, SOURCEFEEITEMS.FEENAME,
GROUPEDTICKETFEEAMOUNTS.NEWAPPLIEDTOITEMID;
-- Tax items. Taxes are nice; they don't store how much applies to each item, just the fact that they apply, so we don't need any fancy dollar sums.
insert into @UPDATEDTAXITEMS
select distinct -- distinct is needed for cases when tickets coming from multiple origin events are rescheduled to the same target event.
TAXEDTICKETS.NEWSALESORDERITEMID,
SALESORDERITEMTAX.TAXID,
SALESORDERITEMTAX.TAXITEMID,
SALESORDERITEMTAX.TOTALTAX,
SALESORDERITEMTAX.TAXNAME
from dbo.SALESORDERITEMTAX
cross apply (
select distinct NEWSALESORDERITEMID
from @TICKETSTORESCHEDULE
where ORIGINALSALESORDERITEMID = SALESORDERITEMTAX.SALESORDERITEMID
) TAXEDTICKETS;
-- Item-level discounts
-- TODO: this is a bit too accepting, in that discounts that don't change may still show up (for example, when discount applies to ticket A, but only ticket B is rescheduled).
-- It shouldn't cause any erroneous outcomes, but if time permits later, do look into cleaning that up.
with ITEMDISCOUNTS as (
select ITEMDISCOUNT.ID, ITEMDISCOUNT.AMOUNT, ITEMDISCOUNT.DISCOUNTID, ITEMDISCOUNT.DISCOUNTNAME, ITEMDISCOUNT.SALESORDERITEMID APPLIEDTOITEMID, ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS
from SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = ITEMDISCOUNT.SALESORDERITEMID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
),
GROUPEDITEMDISCOUNTAMOUNTS as (
select
ITEMDISCOUNTS.ID ITEMDISCOUNTID,
ITEMDISCOUNTS.DISCOUNTID,
coalesce(RESCHEDULES.NEWSALESORDERITEMID, ITEMDISCOUNTS.APPLIEDTOITEMID) NEWAPPLIEDTOITEMID,
sum(TICKET.ITEMLEVELDISCOUNTSAPPLIED) as AMOUNT,
count(TICKET.ID) NUMBEROFDISCOUNTEDTICKETS
from ITEMDISCOUNTS
inner join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = ITEMDISCOUNTS.APPLIEDTOITEMID
left join @TICKETSTORESCHEDULE RESCHEDULES on RESCHEDULES.TICKETID = TICKET.ID
where TICKET.ITEMLEVELDISCOUNTSAPPLIED > 0
group by ITEMDISCOUNTS.ID, ITEMDISCOUNTS.DISCOUNTID, ITEMDISCOUNTS.APPLIEDTOITEMID, RESCHEDULES.NEWSALESORDERITEMID
)
insert into @UPDATEDITEMDISCOUNTS
select
coalesce(UPDATEDITEMDISCOUNTS.ID, newid()),
coalesce(UPDATEDITEMDISCOUNTS.AMOUNT, 0),
coalesce(sum(GROUPEDITEMDISCOUNTAMOUNTS.AMOUNT), 0),
coalesce(sum(GROUPEDITEMDISCOUNTAMOUNTS.NUMBEROFDISCOUNTEDTICKETS), 0),
coalesce(UPDATEDITEMDISCOUNTS.APPLIEDTOITEMID, GROUPEDITEMDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID),
coalesce(UPDATEDITEMDISCOUNTS.DISCOUNTID, SOURCEITEMDISCOUNTS.DISCOUNTID),
coalesce(UPDATEDITEMDISCOUNTS.DISCOUNTNAME, SOURCEITEMDISCOUNTS.DISCOUNTNAME)
from GROUPEDITEMDISCOUNTAMOUNTS
inner join ITEMDISCOUNTS SOURCEITEMDISCOUNTS on SOURCEITEMDISCOUNTS.ID = GROUPEDITEMDISCOUNTAMOUNTS.ITEMDISCOUNTID
full join ITEMDISCOUNTS UPDATEDITEMDISCOUNTS on (UPDATEDITEMDISCOUNTS.APPLIEDTOITEMID = GROUPEDITEMDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID and UPDATEDITEMDISCOUNTS.DISCOUNTID = GROUPEDITEMDISCOUNTAMOUNTS.DISCOUNTID)
group by UPDATEDITEMDISCOUNTS.ID, UPDATEDITEMDISCOUNTS.AMOUNT, UPDATEDITEMDISCOUNTS.APPLIEDTOITEMID, UPDATEDITEMDISCOUNTS.DISCOUNTID, UPDATEDITEMDISCOUNTS.DISCOUNTNAME,
SOURCEITEMDISCOUNTS.DISCOUNTID, SOURCEITEMDISCOUNTS.DISCOUNTNAME,
GROUPEDITEMDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID;
-- Order-level discounts.
-- We'll need the DISCOUNTID and CREDITID when we make the CREDITITEM_EXT records for the new discounts, after all the sales updates are complete.
declare @NUMORDERLEVELDISCOUNTS tinyint = (select count(*) from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 5);
if @NUMORDERLEVELDISCOUNTS = 1
begin
-- Only one order-level discount was applied, so we can use TICKET.ORDERLEVELDISCOUNTSAPPLIED as the per-ticket amount (regardless of whether the discount is standard or adjustable).
with ORDERDISCOUNTS as (
select ORDERDISCOUNTDETAIL.ID, ORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID, ORDERDISCOUNTDETAIL.AMOUNT, ORDERDISCOUNT.DISCOUNTID, ORDERDISCOUNTDETAIL.SALESORDERITEMID APPLIEDTOITEMID
from SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT
inner join dbo.SALESORDERITEMORDERDISCOUNTDETAIL ORDERDISCOUNTDETAIL on ORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID = ORDERDISCOUNT.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = ORDERDISCOUNT.ID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
),
GROUPEDORDERDISCOUNTAMOUNTS as (
select
ORDERDISCOUNTS.ID ORDERDISCOUNTDETAILID,
TICKET.SALESORDERITEMTICKETID ORIGINALAPPLIEDTOITEMID,
coalesce(RESCHEDULES.NEWSALESORDERITEMID, TICKET.SALESORDERITEMTICKETID) NEWAPPLIEDTOITEMID,
sum(TICKET.ORDERLEVELDISCOUNTSAPPLIED) as ORDERLEVELDISCOUNTAMOUNT
from ORDERDISCOUNTS
inner join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = ORDERDISCOUNTS.APPLIEDTOITEMID
left join @TICKETSTORESCHEDULE RESCHEDULES on RESCHEDULES.TICKETID = TICKET.ID
group by ORDERDISCOUNTS.ID, TICKET.SALESORDERITEMTICKETID, RESCHEDULES.NEWSALESORDERITEMID
)
insert into @UPDATEDORDERDISCOUNTDETAILS
select
coalesce(UPDATEDORDERDISCOUNTS.ID, newid()),
coalesce(UPDATEDORDERDISCOUNTS.AMOUNT, 0),
coalesce(sum(GROUPEDORDERDISCOUNTAMOUNTS.ORDERLEVELDISCOUNTAMOUNT), 0),
coalesce(UPDATEDORDERDISCOUNTS.DISCOUNTID, SOURCEORDERDISCOUNTS.DISCOUNTID),
coalesce(UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID, GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID),
SOURCEORDERDISCOUNTS.SALESORDERITEMORDERDISCOUNTID
from GROUPEDORDERDISCOUNTAMOUNTS
inner join ORDERDISCOUNTS SOURCEORDERDISCOUNTS on SOURCEORDERDISCOUNTS.APPLIEDTOITEMID = GROUPEDORDERDISCOUNTAMOUNTS.ORIGINALAPPLIEDTOITEMID
full join ORDERDISCOUNTS UPDATEDORDERDISCOUNTS on UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID = GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID
group by UPDATEDORDERDISCOUNTS.ID, UPDATEDORDERDISCOUNTS.AMOUNT, UPDATEDORDERDISCOUNTS.DISCOUNTID, UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID, SOURCEORDERDISCOUNTS.DISCOUNTID, SOURCEORDERDISCOUNTS.SALESORDERITEMORDERDISCOUNTID, GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID;
end
else if @NUMORDERLEVELDISCOUNTS = 2 -- Has both "standard" order-level and adjustable discounts.
begin
-- To avoid rounding errors, we have to re-prorate the standard order-level discount across all tickets, and then subtract that from the total order-level amount to get the per-ticket adjustable amount.
-- TODO: Verify that the proration works for very small discount values (fewer cents than tickets).
with ORDERDISCOUNTS as (
select ORDERDISCOUNTDETAIL.ID, ORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID, ORDERDISCOUNTDETAIL.AMOUNT, ORDERDISCOUNT.DISCOUNTID, ORDERDISCOUNTDETAIL.SALESORDERITEMID APPLIEDTOITEMID, ORDERDISCOUNT.ISADJUSTABLEDISCOUNT
from SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT
inner join dbo.SALESORDERITEMORDERDISCOUNTDETAIL ORDERDISCOUNTDETAIL on ORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID = ORDERDISCOUNT.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = ORDERDISCOUNT.ID
where SALESORDERITEM.SALESORDERID = @SALESORDERID
),
TICKETSWITHDIVIDEDDISCOUNTS as (
select
TICKET.ID,
TICKET.SALESORDERITEMTICKETID,
coalesce(PRORATEDSTANDARDDISCOUNTAMOUNTS.AMOUNT,0) as ORDERLEVELDISCOUNTAMOUNT,
TICKET.ORDERLEVELDISCOUNTSAPPLIED - coalesce(PRORATEDSTANDARDDISCOUNTAMOUNTS.AMOUNT,0) as ADJUSTABLEDISCOUNTAMOUNT
from dbo.TICKET
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
left join ORDERDISCOUNTS on (ORDERDISCOUNTS.APPLIEDTOITEMID = TICKET.SALESORDERITEMTICKETID and ORDERDISCOUNTS.ISADJUSTABLEDISCOUNT = 0)
outer apply dbo.UFN_SPLITS_PRORATEAMOUNTS (
(select sum(ORDERLEVELDISCOUNTSAPPLIED) from dbo.TICKET where SALESORDERITEMTICKETID = ORDERDISCOUNTS.APPLIEDTOITEMID),
ORDERDISCOUNTS.AMOUNT,
2,
(select ID, ORDERLEVELDISCOUNTSAPPLIED as AMOUNT from dbo.TICKET where SALESORDERITEMTICKETID = ORDERDISCOUNTS.APPLIEDTOITEMID for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64)
) PRORATEDSTANDARDDISCOUNTAMOUNTS
where SALESORDERITEM.SALESORDERID = @SALESORDERID
and (PRORATEDSTANDARDDISCOUNTAMOUNTS.ID = TICKET.ID or PRORATEDSTANDARDDISCOUNTAMOUNTS.ID is null)
),
GROUPEDORDERDISCOUNTAMOUNTS as (
select
ORDERDISCOUNTS.ID ORDERDISCOUNTDETAILID,
TICKETSWITHDIVIDEDDISCOUNTS.SALESORDERITEMTICKETID ORIGINALAPPLIEDTOITEMID,
coalesce(RESCHEDULES.NEWSALESORDERITEMID, TICKETSWITHDIVIDEDDISCOUNTS.SALESORDERITEMTICKETID) NEWAPPLIEDTOITEMID,
case
when ORDERDISCOUNTS.ISADJUSTABLEDISCOUNT = 0
then sum(TICKETSWITHDIVIDEDDISCOUNTS.ORDERLEVELDISCOUNTAMOUNT)
else sum(TICKETSWITHDIVIDEDDISCOUNTS.ADJUSTABLEDISCOUNTAMOUNT)
end as ORDERLEVELDISCOUNTAMOUNT,
ORDERDISCOUNTS.ISADJUSTABLEDISCOUNT
from ORDERDISCOUNTS
inner join TICKETSWITHDIVIDEDDISCOUNTS on TICKETSWITHDIVIDEDDISCOUNTS.SALESORDERITEMTICKETID = ORDERDISCOUNTS.APPLIEDTOITEMID
left join @TICKETSTORESCHEDULE RESCHEDULES on RESCHEDULES.TICKETID = TICKETSWITHDIVIDEDDISCOUNTS.ID
group by ORDERDISCOUNTS.ID, TICKETSWITHDIVIDEDDISCOUNTS.SALESORDERITEMTICKETID, RESCHEDULES.NEWSALESORDERITEMID, ORDERDISCOUNTS.ISADJUSTABLEDISCOUNT
)
insert into @UPDATEDORDERDISCOUNTDETAILS
select
coalesce(UPDATEDORDERDISCOUNTS.ID, newid()),
coalesce(UPDATEDORDERDISCOUNTS.AMOUNT, 0),
coalesce(sum(GROUPEDORDERDISCOUNTAMOUNTS.ORDERLEVELDISCOUNTAMOUNT), 0),
coalesce(UPDATEDORDERDISCOUNTS.DISCOUNTID, SOURCEORDERDISCOUNTS.DISCOUNTID),
coalesce(UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID, GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID),
SOURCEORDERDISCOUNTS.SALESORDERITEMORDERDISCOUNTID
from GROUPEDORDERDISCOUNTAMOUNTS
inner join ORDERDISCOUNTS SOURCEORDERDISCOUNTS on (SOURCEORDERDISCOUNTS.APPLIEDTOITEMID = GROUPEDORDERDISCOUNTAMOUNTS.ORIGINALAPPLIEDTOITEMID and SOURCEORDERDISCOUNTS.ISADJUSTABLEDISCOUNT = GROUPEDORDERDISCOUNTAMOUNTS.ISADJUSTABLEDISCOUNT)
full join ORDERDISCOUNTS UPDATEDORDERDISCOUNTS on (UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID = GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID and UPDATEDORDERDISCOUNTS.ISADJUSTABLEDISCOUNT = GROUPEDORDERDISCOUNTAMOUNTS.ISADJUSTABLEDISCOUNT)
group by UPDATEDORDERDISCOUNTS.ID, UPDATEDORDERDISCOUNTS.AMOUNT, UPDATEDORDERDISCOUNTS.DISCOUNTID, UPDATEDORDERDISCOUNTS.APPLIEDTOITEMID, SOURCEORDERDISCOUNTS.DISCOUNTID, SOURCEORDERDISCOUNTS.SALESORDERITEMORDERDISCOUNTID, GROUPEDORDERDISCOUNTAMOUNTS.NEWAPPLIEDTOITEMID;
end
-- Get the SALESORDERNOTEs that need to be updated.
-- Taxes and discounts don't get new sales order items, so we don't have to include them.
declare @SALESORDERNOTESTOCOPY table (
ID uniqueidentifier,
DATEENTERED date,
TITLE nvarchar(50),
AUTHORID uniqueidentifier,
TEXTNOTE nvarchar(max),
SALESORDERNOTETYPECODEID uniqueidentifier,
HTMLNOTE nvarchar(max),
NEWSALESORDERITEMID uniqueidentifier,
DELETEORIGINAL bit
);
with NOTABLESALESORDERITEMSTOUPDATE as
(
-- Ticket items
select distinct
RESCHEDULE.ORIGINALSALESORDERITEMID,
RESCHEDULE.NEWSALESORDERITEMID,
case when UPDATEDTICKETITEMS.NEWQUANTITY = 0 then 1 else 0 end DELETEORIGINAL
from @TICKETSTORESCHEDULE RESCHEDULE
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on UPDATEDTICKETITEMS.ID = RESCHEDULE.ORIGINALSALESORDERITEMID
union all
-- Fee items
select distinct
ORIGINALFEEITEM.ID as ORIGINALSALESORDERITEMID,
NEWFEEITEM.ID as NEWSALESORDERITEMID,
case when ORIGINALFEEITEM.NEWPRICE = 0 then 1 else 0 end DELETEORIGINAL
from @TICKETSTORESCHEDULE RESCHEDULE
inner join @UPDATEDFEEITEMS ORIGINALFEEITEM on ORIGINALFEEITEM.APPLIEDTOITEMID = RESCHEDULE.ORIGINALSALESORDERITEMID
inner join @UPDATEDFEEITEMS NEWFEEITEM on NEWFEEITEM.APPLIEDTOITEMID = RESCHEDULE.NEWSALESORDERITEMID
)
insert into @SALESORDERNOTESTOCOPY
select
SALESORDERNOTE.ID,
SALESORDERNOTE.DATEENTERED,
SALESORDERNOTE.TITLE,
SALESORDERNOTE.AUTHORID,
SALESORDERNOTE.TEXTNOTE,
SALESORDERNOTE.SALESORDERNOTETYPECODEID,
SALESORDERNOTE.HTMLNOTE,
NOTABLESALESORDERITEMSTOUPDATE.NEWSALESORDERITEMID,
NOTABLESALESORDERITEMSTOUPDATE.DELETEORIGINAL
from dbo.SALESORDERNOTE
inner join NOTABLESALESORDERITEMSTOUPDATE on NOTABLESALESORDERITEMSTOUPDATE.ORIGINALSALESORDERITEMID = SALESORDERNOTE.SALESORDERITEMID
where SALESORDERNOTE.SALESORDERID = @SALESORDERID;
begin try
-- Add and update ticket items.
insert into dbo.SALESORDERITEM
(ID, PRICE, QUANTITY, SALESORDERID, TYPECODE, [DESCRIPTION], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
NEWITEMS.ID, PRICE, NEWQUANTITY, @SALESORDERID, 0, EVENT.NAME + ' - ' + PRICETYPECODE.[DESCRIPTION], @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDTICKETITEMS NEWITEMS
inner join dbo.EVENT on EVENT.ID = NEWITEMS.EVENTID
inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = NEWITEMS.PRICETYPECODEID
where OLDQUANTITY = 0;
insert into dbo.SALESORDERITEMTICKET
(ID, PRICE, PROGRAMID, PROGRAMNAME, PROGRAMCATEGORYNAME, EVENTID, PRICETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
NEWITEMS.ID, NEWITEMS.PRICE, PROGRAM.ID, PROGRAM.NAME, coalesce(PROGRAMCATEGORYCODE.[DESCRIPTION],''), EVENT.ID, NEWITEMS.PRICETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDTICKETITEMS NEWITEMS
inner join dbo.EVENT on EVENT.ID = NEWITEMS.EVENTID
inner join dbo.PROGRAM on PROGRAM.ID = EVENT.PROGRAMID
left join dbo.PROGRAMCATEGORYCODE on PROGRAMCATEGORYCODE.ID = PROGRAM.PROGRAMCATEGORYCODEID
where OLDQUANTITY = 0
and NEWQUANTITY <> 0;
-- Remove the combination grouping data from all items in a combo if any one of the items is being rescheduled.
delete dbo.SALESORDERITEMTICKETCOMBINATION
where TICKETCOMBINATIONID in (
select RESCHEDULEDCOMBOITEM.TICKETCOMBINATIONID
from @UPDATEDTICKETITEMS UPDATEDTICKETITEMS
inner join SALESORDERITEMTICKETCOMBINATION RESCHEDULEDCOMBOITEM on RESCHEDULEDCOMBOITEM.ID = UPDATEDTICKETITEMS.ID
);
update dbo.SALESORDERITEM
set QUANTITY = NEWQUANTITY, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @UPDATEDTICKETITEMS UPDATEDITEMS
where SALESORDERITEM.ID = UPDATEDITEMS.ID
and OLDQUANTITY <> 0
and NEWQUANTITY <> 0
and NEWQUANTITY <> OLDQUANTITY;
-- Move tickets to new ticket items.
update dbo.TICKET
set EVENTID = RESCHEDULES.NEWEVENTID, SALESORDERITEMTICKETID = UPDATEDTICKETITEMS.ID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @TICKETSTORESCHEDULE RESCHEDULES
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on (UPDATEDTICKETITEMS.EVENTID = RESCHEDULES.NEWEVENTID and UPDATEDTICKETITEMS.PRICETYPECODEID = RESCHEDULES.PRICETYPECODEID)
where TICKET.ID = RESCHEDULES.TICKETID;
-- Update link between order item and registrant.
update dbo.SALESORDERITEMTICKETREGISTRANT
set SALESORDERITEMTICKETID = RESCHEDULES.NEWSALESORDERITEMID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @TICKETSTORESCHEDULE RESCHEDULES
where SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = RESCHEDULES.REGISTRANTID
and RESCHEDULES.REGISTRANTID is not null;
update dbo.RESCHEDULETICKETSHISTORY
set SALESORDERITEMTICKETID = UPDATEDTICKETITEMS.ID
from @TICKETSTORESCHEDULE RESCHEDULES
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on (UPDATEDTICKETITEMS.EVENTID = RESCHEDULES.NEWEVENTID and UPDATEDTICKETITEMS.PRICETYPECODEID = RESCHEDULES.PRICETYPECODEID)
where RESCHEDULETICKETSHISTORY.SALESORDERITEMTICKETID = RESCHEDULES.ORIGINALSALESORDERITEMID
and RESCHEDULETICKETSHISTORY.TICKETID = RESCHEDULES.TICKETID;
insert into dbo.RESCHEDULETICKETSHISTORY(
SALESORDERITEMTICKETID,
TICKETID,
PREVIOUSEVENTID,
RESCHEDULEDDATE,
RESCHEDULEDBYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
UPDATEDTICKETITEMS.ID,
RESCHEDULES.TICKETID,
RESCHEDULES.ORIGINALEVENTID,
@CURRENTTIME,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTTIME,
@CURRENTTIME
from @TICKETSTORESCHEDULE RESCHEDULES
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on (UPDATEDTICKETITEMS.EVENTID = RESCHEDULES.NEWEVENTID and UPDATEDTICKETITEMS.PRICETYPECODEID = RESCHEDULES.PRICETYPECODEID);
-- Add and update fee items.
insert into dbo.SALESORDERITEM
(ID, PRICE, QUANTITY, [PERCENT], SALESORDERID, TYPECODE, [DESCRIPTION], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, NEWPRICE, 1, [PERCENT], @SALESORDERID, 3, [DESCRIPTION], @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDFEEITEMS NEWFEEITEMS
where ORIGINALPRICE = 0;
insert into dbo.SALESORDERITEMFEE
(ID, SALESORDERITEMID, TYPECODE, FEEID, FEENAME, APPLIESTOCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, APPLIEDTOITEMID, 2, FEEID, FEENAME, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDFEEITEMS NEWFEEITEMS
where ORIGINALPRICE = 0;
update dbo.SALESORDERITEM
set PRICE = NEWPRICE, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @UPDATEDFEEITEMS UPDATEDFEEITEMS
where SALESORDERITEM.ID = UPDATEDFEEITEMS.ID
and UPDATEDFEEITEMS.ORIGINALPRICE <> 0
and UPDATEDFEEITEMS.NEWPRICE <> 0
and UPDATEDFEEITEMS.ORIGINALPRICE <> UPDATEDFEEITEMS.NEWPRICE;
-- Update all TICKETFEEs to point to the new SALESORDERITEMFEEs (none need to be added or removed, since they're at the ticket level)
update dbo.SALESORDERITEMTICKETFEE
set SALESORDERITEMFEEID = UPDATEDFEEITEMS.ID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from dbo.SALESORDERITEMTICKETFEE
inner join dbo.SALESORDERITEMFEE ORIGINALFEEITEM on ORIGINALFEEITEM.ID = SALESORDERITEMTICKETFEE.SALESORDERITEMFEEID
inner join @TICKETSTORESCHEDULE RESCHEDULES on RESCHEDULES.TICKETID = SALESORDERITEMTICKETFEE.TICKETID
inner join @UPDATEDFEEITEMS UPDATEDFEEITEMS on (UPDATEDFEEITEMS.APPLIEDTOITEMID = RESCHEDULES.NEWSALESORDERITEMID and UPDATEDFEEITEMS.FEEID = ORIGINALFEEITEM.FEEID);
-- Add new tax items.
insert into dbo.SALESORDERITEMTAX
(ID, SALESORDERITEMID, TAXID, TAXNAME, TAXITEMID, TOTALTAX, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(), APPLIEDTOITEMID, TAXID, TAXNAME, TAXITEMID, TOTALTAX, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDTAXITEMS;
-- Add and update item discount records.
insert into dbo.SALESORDERITEMITEMDISCOUNT
(ID, SALESORDERITEMID, AMOUNT, DISCOUNTID, DISCOUNTNAME, NUMBEROFDISCOUNTEDITEMS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, APPLIEDTOITEMID, NEWAMOUNT, DISCOUNTID, DISCOUNTNAME, NUMBEROFTIMESAPPLIED, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDITEMDISCOUNTS
where ORIGINALAMOUNT = 0;
update dbo.SALESORDERITEMITEMDISCOUNT
set AMOUNT = NEWAMOUNT, NUMBEROFDISCOUNTEDITEMS = NUMBEROFTIMESAPPLIED, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @UPDATEDITEMDISCOUNTS UPDATEDITEMDISCOUNTS
where SALESORDERITEMITEMDISCOUNT.ID = UPDATEDITEMDISCOUNTS.ID
and ORIGINALAMOUNT <> 0
and NEWAMOUNT <> 0
and ORIGINALAMOUNT <> NEWAMOUNT;
-- Add and update order discount detail records.
insert into dbo.SALESORDERITEMORDERDISCOUNTDETAIL
(ID, SALESORDERITEMID, AMOUNT, SALESORDERITEMORDERDISCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, APPLIEDTOITEMID, NEWAMOUNT, SALESORDERITEMORDERDISCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERDISCOUNTDETAILS
where ORIGINALAMOUNT = 0;
update dbo.SALESORDERITEMORDERDISCOUNTDETAIL
set AMOUNT = NEWAMOUNT, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
from @UPDATEDORDERDISCOUNTDETAILS UPDATEDORDERDISCOUNTDETAILS
where SALESORDERITEMORDERDISCOUNTDETAIL.ID = UPDATEDORDERDISCOUNTDETAILS.ID
and ORIGINALAMOUNT <> 0
and NEWAMOUNT <> 0
and ORIGINALAMOUNT <> NEWAMOUNT;
-- Copy SALESORDERNOTEs that need to be copied before we start deleting data.
-- TODO: possibly check for existing identical notes before inserting to protect against rampant duplication after multiple reschedules (not urgent).
insert into dbo.SALESORDERNOTE
(ID, DATEENTERED, TITLE, AUTHORID, TEXTNOTE, SALESORDERNOTETYPECODEID, SALESORDERID, HTMLNOTE, SALESORDERITEMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(), DATEENTERED, TITLE, AUTHORID, TEXTNOTE, SALESORDERNOTETYPECODEID, @SALESORDERID, HTMLNOTE, NEWSALESORDERITEMID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @SALESORDERNOTESTOCOPY;
-- Delete discounts that apply to the items we're about to remove
if exists (select 1 from @UPDATEDITEMDISCOUNTS where NEWAMOUNT = 0)
delete from dbo.SALESORDERITEMITEMDISCOUNT
where ID in (select ID from @UPDATEDITEMDISCOUNTS where NEWAMOUNT = 0);
if exists (select 1 from @UPDATEDORDERDISCOUNTDETAILS where NEWAMOUNT = 0)
delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where ID in (select ID from @UPDATEDORDERDISCOUNTDETAILS where NEWAMOUNT = 0);
-- Delete SALESORDERNOTEs for the sales order items that we are about to delete so we
-- don't run into any foreign key constraint errors
delete from dbo.SALESORDERNOTE
where ID in (
select ID
from @SALESORDERNOTESTOCOPY NOTESTOCOPY
where DELETEORIGINAL = 1
);
-- Delete items whose new quantity or price is now zero (SALESORDERITEM cascades to SALESORDERITEMFEE when it's a fee and SALESORDERITEMTAX when it's a ticket item).
delete from dbo.SALESORDERITEM
where ID in (
select ID from @UPDATEDTICKETITEMS where NEWQUANTITY = 0
union all
select ID from @UPDATEDFEEITEMS where NEWPRICE = 0
);
-- Everything in the Sales domain should now be correctly updated; now adjust the tickets' line items.
-- The only reason we need to do this is because we have REVENUESPLITORDER instead of an explicit link from the SALESORDERITEM to its FINANCIALTRANSACTIONLINEITEM.
-- Since we join through EVENTID, and changing EVENTID is the whole point of this operation, we have to adjust the (otherwise completely irrelevant) financial records.
declare @ORDERREVENUEID uniqueidentifier = (select REVENUEID from dbo.SALESORDER where ID = @SALESORDERID);
declare @DEFAULTPOSTSTATUSCODE tinyint = (select case when ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end from dbo.PDACCOUNTSYSTEM);
declare @ORDERPOSTDATE date = null;
if @DEFAULTPOSTSTATUSCODE = 1 set @ORDERPOSTDATE = (select POSTDATE from dbo.FINANCIALTRANSACTION where ID = @ORDERREVENUEID);
declare @UPDATEDORDERLINEITEMS table (ID uniqueidentifier, FINANCIALTRANSACTIONID uniqueidentifier, EVENTID uniqueidentifier, DISCOUNTID uniqueidentifier, CREDITID uniqueidentifier, SOURCELINEITEMID uniqueidentifier, ORIGINALAMOUNT money, NEWAMOUNT money, POSTSTATUSCODE tinyint);
with ORIGINALTICKETSPLITS as (
select TICKETLI.ID, TICKETLI.BASEAMOUNT, TICKETLI.POSTSTATUSCODE, RSO.EVENTID
from dbo.FINANCIALTRANSACTIONLINEITEM TICKETLI
inner join dbo.REVENUESPLITORDER RSO on RSO.ID = TICKETLI.ID
where TICKETLI.FINANCIALTRANSACTIONID = @ORDERREVENUEID
and RSO.EVENTID is not null
and TICKETLI.DELETEDON is null
),
UPDATEDTICKETSPLITS as (
select
coalesce(ORIGINALTICKETSPLITS.ID, newid()) ID,
UPDATEDTICKETITEMS.EVENTID,
coalesce(ORIGINALTICKETSPLITS.BASEAMOUNT, 0) ORIGINALAMOUNT,
coalesce(ORIGINALTICKETSPLITS.BASEAMOUNT, 0) + sum(UPDATEDTICKETITEMS.NEWQUANTITY * UPDATEDTICKETITEMS.PRICE) - sum(UPDATEDTICKETITEMS.OLDQUANTITY * UPDATEDTICKETITEMS.PRICE) NEWAMOUNT,
coalesce(ORIGINALTICKETSPLITS.POSTSTATUSCODE, @DEFAULTPOSTSTATUSCODE) POSTSTATUSCODE
from @UPDATEDTICKETITEMS UPDATEDTICKETITEMS
left join ORIGINALTICKETSPLITS on UPDATEDTICKETITEMS.EVENTID = ORIGINALTICKETSPLITS.EVENTID
group by ORIGINALTICKETSPLITS.ID, UPDATEDTICKETITEMS.EVENTID, ORIGINALTICKETSPLITS.BASEAMOUNT, ORIGINALTICKETSPLITS.POSTSTATUSCODE
)
insert into @UPDATEDORDERLINEITEMS
select ID, @ORDERREVENUEID, EVENTID, null, null, null, ORIGINALAMOUNT, NEWAMOUNT, POSTSTATUSCODE
from UPDATEDTICKETSPLITS
where ORIGINALAMOUNT <> NEWAMOUNT;
with ORIGINALDISCOUNTSPLITS as (
select DISCOUNTLI.ID, DISCOUNTLI.BASEAMOUNT, DISCOUNTLI.POSTSTATUSCODE, DISCOUNTLI.SOURCELINEITEMID, EXT.DISCOUNTID, EXT.CREDITID
from dbo.FINANCIALTRANSACTIONLINEITEM DISCOUNTLI
inner join dbo.CREDITITEM_EXT EXT on EXT.ID = DISCOUNTLI.ID
inner join @UPDATEDORDERLINEITEMS TICKETLINEITEMS on TICKETLINEITEMS.ID = DISCOUNTLI.SOURCELINEITEMID
where DISCOUNTLI.FINANCIALTRANSACTIONID = @ORDERREVENUEID
and DISCOUNTLI.TYPECODE = 5
and DISCOUNTLI.DELETEDON is null
)
insert into @UPDATEDORDERLINEITEMS
-- Item discounts
select
coalesce(UPDATEDDISCOUNTSPLITS.ID, newid()) ID,
@ORDERREVENUEID,
null,
UPDATEDITEMDISCOUNTS.DISCOUNTID,
(select top 1 CREDITID from ORIGINALDISCOUNTSPLITS where DISCOUNTID = UPDATEDITEMDISCOUNTS.DISCOUNTID),
UPDATEDTICKETSPLITS.ID SOURCELINEITEMID,
coalesce(UPDATEDDISCOUNTSPLITS.BASEAMOUNT, 0) ORIGINALAMOUNT,
sum(UPDATEDITEMDISCOUNTS.NEWAMOUNT) NEWAMOUNT,
coalesce(UPDATEDDISCOUNTSPLITS.POSTSTATUSCODE, @DEFAULTPOSTSTATUSCODE) POSTSTATUSCODE
from @UPDATEDITEMDISCOUNTS UPDATEDITEMDISCOUNTS
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on UPDATEDTICKETITEMS.ID = UPDATEDITEMDISCOUNTS.APPLIEDTOITEMID
inner join @UPDATEDORDERLINEITEMS UPDATEDTICKETSPLITS on UPDATEDTICKETSPLITS.EVENTID = UPDATEDTICKETITEMS.EVENTID
left join ORIGINALDISCOUNTSPLITS UPDATEDDISCOUNTSPLITS on (UPDATEDDISCOUNTSPLITS.SOURCELINEITEMID = UPDATEDTICKETSPLITS.ID and UPDATEDDISCOUNTSPLITS.DISCOUNTID = UPDATEDITEMDISCOUNTS.DISCOUNTID)
group by UPDATEDDISCOUNTSPLITS.ID, UPDATEDDISCOUNTSPLITS.BASEAMOUNT, UPDATEDTICKETSPLITS.ID, UPDATEDITEMDISCOUNTS.DISCOUNTID, UPDATEDDISCOUNTSPLITS.CREDITID, UPDATEDDISCOUNTSPLITS.POSTSTATUSCODE
having coalesce(UPDATEDDISCOUNTSPLITS.BASEAMOUNT, 0) <> sum(UPDATEDITEMDISCOUNTS.NEWAMOUNT)
union all
-- Order discounts
select
coalesce(UPDATEDDISCOUNTSPLITS.ID, newid()) ID,
@ORDERREVENUEID,
null,
UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID,
(select top 1 CREDITID from ORIGINALDISCOUNTSPLITS where DISCOUNTID = UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID or (DISCOUNTID is null and UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID is null)),
UPDATEDTICKETSPLITS.ID SOURCELINEITEMID,
coalesce(UPDATEDDISCOUNTSPLITS.BASEAMOUNT, 0) ORIGINALAMOUNT,
sum(UPDATEDORDERDISCOUNTDETAILS.NEWAMOUNT) NEWAMOUNT,
coalesce(UPDATEDDISCOUNTSPLITS.POSTSTATUSCODE, @DEFAULTPOSTSTATUSCODE) POSTSTATUSCODE
from @UPDATEDORDERDISCOUNTDETAILS UPDATEDORDERDISCOUNTDETAILS
inner join @UPDATEDTICKETITEMS UPDATEDTICKETITEMS on UPDATEDTICKETITEMS.ID = UPDATEDORDERDISCOUNTDETAILS.APPLIEDTOITEMID
inner join @UPDATEDORDERLINEITEMS UPDATEDTICKETSPLITS on UPDATEDTICKETSPLITS.EVENTID = UPDATEDTICKETITEMS.EVENTID
left join ORIGINALDISCOUNTSPLITS UPDATEDDISCOUNTSPLITS on (UPDATEDDISCOUNTSPLITS.SOURCELINEITEMID = UPDATEDTICKETSPLITS.ID and (UPDATEDDISCOUNTSPLITS.DISCOUNTID = UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID or (UPDATEDDISCOUNTSPLITS.DISCOUNTID is null and UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID is null)))
group by UPDATEDDISCOUNTSPLITS.ID, UPDATEDDISCOUNTSPLITS.BASEAMOUNT, UPDATEDTICKETSPLITS.ID, UPDATEDORDERDISCOUNTDETAILS.DISCOUNTID, UPDATEDDISCOUNTSPLITS.CREDITID, UPDATEDDISCOUNTSPLITS.POSTSTATUSCODE
having coalesce(UPDATEDDISCOUNTSPLITS.BASEAMOUNT, 0) <> sum(UPDATEDORDERDISCOUNTDETAILS.NEWAMOUNT)
-- Determine which line items to adjust. We'll reduce their value or delete them, and then add new line items for the new events.
-- Some may be posted and some not yet posted in the same reschedule operation (for example, if they reschedule twice in a row with a different group of tickets).
declare @ORDERADJUSTMENTID uniqueidentifier;
declare @POSTEDORDERLINEITEMS dbo.UDT_GENERICID;
declare @NOTPOSTEDORDERLINEITEMS dbo.UDT_GENERICID;
insert into @POSTEDORDERLINEITEMS
select ID from @UPDATEDORDERLINEITEMS
where POSTSTATUSCODE = 2 and ORIGINALAMOUNT <> 0;
insert into @NOTPOSTEDORDERLINEITEMS
select ID from @UPDATEDORDERLINEITEMS
where POSTSTATUSCODE <> 2 and ORIGINALAMOUNT <> 0;
if exists (select 1 from @POSTEDORDERLINEITEMS)
begin
-- Create the "Adjustment" record and the reversal line items
exec dbo.USP_SAVE_ADJUSTMENT_FOR_SPECIFIC_LINEITEMS @ORDERREVENUEID, @POSTEDORDERLINEITEMS, @ORDERADJUSTMENTID output, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME, @ORDERPOSTDATE, 'Ticket Reschedule';
end
else begin
-- If this is not the first reschedule on the posted order, we may already have an adjustment ID that we want to link the new line items to.
set @ORDERADJUSTMENTID = (
select top 1 FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @ORDERREVENUEID
and POSTSTATUSCODE = 1
and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is not null
);
end
if exists (select 1 from @NOTPOSTEDORDERLINEITEMS)
begin
-- Non-posted line items don't get adjusted (the adjustment SP will actually throw an error if you try to adjust non-posted line items)
-- However, we need to delete their GL, or else it won't be recreated by USP_SAVE_REVENUEGLDISTRIBUTION.
delete from dbo.JOURNALENTRY where FINANCIALTRANSACTIONLINEITEMID in (select ID from @NOTPOSTEDORDERLINEITEMS);
end
-- Update existing order line items. We can't delete the $0 ones yet because they are still referenced by payment line items, but we have to set them to $0 to regenerate payment LIs correctly.
update dbo.FINANCIALTRANSACTIONLINEITEM
set BASEAMOUNT = UPDATEDORDERLINEITEMS.NEWAMOUNT, TRANSACTIONAMOUNT = UPDATEDORDERLINEITEMS.NEWAMOUNT, ORGAMOUNT = UPDATEDORDERLINEITEMS.NEWAMOUNT, UNITVALUE = case when UPDATEDORDERLINEITEMS.EVENTID is null then UPDATEDORDERLINEITEMS.NEWAMOUNT else 0 end
from @UPDATEDORDERLINEITEMS UPDATEDORDERLINEITEMS
where FINANCIALTRANSACTIONLINEITEM.ID = UPDATEDORDERLINEITEMS.ID
and ORIGINALAMOUNT <> 0
and NEWAMOUNT <> ORIGINALAMOUNT;
-- Ticket line items
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(ID, FINANCIALTRANSACTIONID, TYPECODE, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, POSTSTATUSCODE, POSTDATE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, FINANCIALTRANSACTIONID, 0, NEWAMOUNT, NEWAMOUNT, NEWAMOUNT, @DEFAULTPOSTSTATUSCODE, @ORDERPOSTDATE, @ORDERADJUSTMENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERLINEITEMS
where EVENTID is not null
and ORIGINALAMOUNT = 0;
-- Discount line items
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(ID, FINANCIALTRANSACTIONID, TYPECODE, SOURCELINEITEMID, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, UNITVALUE, POSTSTATUSCODE, POSTDATE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, FINANCIALTRANSACTIONID, 5, SOURCELINEITEMID, NEWAMOUNT, NEWAMOUNT, NEWAMOUNT, NEWAMOUNT, @DEFAULTPOSTSTATUSCODE, @ORDERPOSTDATE, @ORDERADJUSTMENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERLINEITEMS
where EVENTID is null
and ORIGINALAMOUNT = 0;
insert into dbo.REVENUESPLIT_EXT
(ID, TYPECODE, APPLICATIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, 5, 10, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERLINEITEMS
where EVENTID is not null
and ORIGINALAMOUNT = 0;
insert into dbo.CREDITITEM_EXT
(ID, CREDITID, TYPECODE, DISCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, CREDITID, 0, DISCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERLINEITEMS DISCOUNTLIS
where EVENTID is null -- Can't use "DISCOUNTID is not null" because it's also null for Adjustable discounts.
and ORIGINALAMOUNT = 0;
insert into dbo.REVENUESPLITORDER
(ID, PROGRAMID, EVENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
UPDATEDORDERLINEITEMS.ID, EVENT.PROGRAMID, EVENT.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
from @UPDATEDORDERLINEITEMS UPDATEDORDERLINEITEMS
inner join dbo.EVENT on EVENT.ID = UPDATEDORDERLINEITEMS.EVENTID
where UPDATEDORDERLINEITEMS.ORIGINALAMOUNT = 0;
if @DEFAULTPOSTSTATUSCODE <> 3
begin
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ORDERREVENUEID, @CHANGEAGENTID, @CURRENTTIME;
declare @CREDITID uniqueidentifier;
declare discount_cursor cursor local fast_forward for (select distinct CREDITID from @UPDATEDORDERLINEITEMS);
open discount_cursor;
fetch next from discount_cursor into @CREDITID;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_SALESORDER_CREATEDISCOUNTGLDISTRIBUTION @CREDITID, @CHANGEAGENTID, @CURRENTTIME;
fetch next from discount_cursor into @CREDITID;
end
close discount_cursor;
deallocate discount_cursor;
end
declare @PAYMENTS table (ID uniqueidentifier, POSTDATE date, DEPOSITID uniqueidentifier, ADJUSTMENTID uniqueidentifier);
insert into @PAYMENTS (ID, POSTDATE, DEPOSITID)
select FT.ID, FT.POSTDATE, DEPOSITPAYMENT.DEPOSITID
from dbo.FINANCIALTRANSACTION FT
inner join dbo.SALESORDERPAYMENT SOP on SOP.PAYMENTID = FT.ID
left join dbo.BANKACCOUNTDEPOSITPAYMENT DEPOSITPAYMENT on DEPOSITPAYMENT.ID = FT.ID
where SOP.SALESORDERID = @SALESORDERID
and FT.TYPECODE = 0;
declare @PAYMENTID uniqueidentifier, @PAYMENTPOSTDATE date;
declare payment_cursor cursor local fast_forward for (
select ID, POSTDATE from @PAYMENTS
);
-- TODO: find a way to reallocate the line items that apply to tickets, without redoing all the payments every time.
open payment_cursor;
fetch next from payment_cursor into @PAYMENTID, @PAYMENTPOSTDATE;
while @@FETCH_STATUS = 0
begin
declare @PAYMENTADJUSTMENTID uniqueidentifier;
declare @POSTEDPAYMENTLINEITEMS dbo.UDT_GENERICID;
declare @NOTPOSTEDPAYMENTLINEITEMS dbo.UDT_GENERICID;
insert into @POSTEDPAYMENTLINEITEMS
select ID from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @PAYMENTID
and POSTSTATUSCODE = 2
and TYPECODE = 0
and DELETEDON is null;
insert into @NOTPOSTEDPAYMENTLINEITEMS
select ID from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @PAYMENTID
and POSTSTATUSCODE <> 2
and TYPECODE = 0
and DELETEDON is null;
if exists (select 1 from @POSTEDPAYMENTLINEITEMS)
begin
-- Create the "Adjustment" record and the reversal line items
exec dbo.USP_SAVE_ADJUSTMENT_FOR_SPECIFIC_LINEITEMS @PAYMENTID, @POSTEDPAYMENTLINEITEMS, @PAYMENTADJUSTMENTID output, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME, @PAYMENTPOSTDATE, 'Ticket Reschedule';
end
else begin
-- If this is not the first reschedule on the posted order, we may already have a payment adjustment ID that we want to link the new line items to.
set @PAYMENTADJUSTMENTID = (
select top 1 FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @PAYMENTID
and POSTSTATUSCODE = 1
and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is not null
);
end
if @PAYMENTADJUSTMENTID is not null
update @PAYMENTS set ADJUSTMENTID = @PAYMENTADJUSTMENTID where ID = @PAYMENTID;
if exists (select 1 from @NOTPOSTEDPAYMENTLINEITEMS)
begin
-- Non-posted line items don't get adjusted (the adjustment SP will actually throw an error if you try to adjust non-posted line items)
-- However, we need to delete their GL, or else it won't be recreated by USP_SAVE_REVENUEGLDISTRIBUTION.
delete from dbo.JOURNALENTRY where FINANCIALTRANSACTIONLINEITEMID in (select ID from @NOTPOSTEDPAYMENTLINEITEMS);
end
-- Currently have to delete all of the original line items after adjusting so that ADDPAYMENTREVENUE will recreate them.
update dbo.FINANCIALTRANSACTIONLINEITEM
set REVERSEDLINEITEMID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
where REVERSEDLINEITEMID in (select ID from @POSTEDPAYMENTLINEITEMS);
delete from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @PAYMENTID
and TYPECODE = 0
and POSTSTATUSCODE <> 2
and DELETEDON is null;
fetch next from payment_cursor into @PAYMENTID, @PAYMENTPOSTDATE;
end
close payment_cursor;
deallocate payment_cursor;
-- Prepare to delete $0 order line items by eliminating FKs.
update dbo.FINANCIALTRANSACTIONLINEITEM
set REVERSEDLINEITEMID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
where REVERSEDLINEITEMID in (select ID from @UPDATEDORDERLINEITEMS where NEWAMOUNT = 0);
update dbo.FINANCIALTRANSACTIONLINEITEM
set SOURCELINEITEMID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
where SOURCELINEITEMID in (select ID from @UPDATEDORDERLINEITEMS where NEWAMOUNT = 0);
update dbo.JOURNALENTRY_EXT
set CREDITITEMID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
where CREDITITEMID in (select ID from @UPDATEDORDERLINEITEMS where EVENTID is null and NEWAMOUNT = 0);
-- Delete $0 order line items before recreating payment line items. First discounts, then tickets (due to the same table reference).
delete from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @ORDERREVENUEID
and ID in (select ID from @UPDATEDORDERLINEITEMS where EVENTID is null and NEWAMOUNT = 0);
delete from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONID = @ORDERREVENUEID
and ID in (select ID from @UPDATEDORDERLINEITEMS where EVENTID is not null and NEWAMOUNT = 0);
-- Recreate all payment line items.
exec dbo.USP_SALESORDER_ADDPAYMENTREVENUE @SALESORDERID, @ORDERREVENUEID, @CHANGEAGENTID, @CURRENTTIME;
-- Update adjustment IDs on new line items where needed.
update dbo.FINANCIALTRANSACTIONLINEITEM
set FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = PAYMENT.ADJUSTMENTID,
POSTDATE = PAYMENT.POSTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTTIME
from @PAYMENTS PAYMENT
where FINANCIALTRANSACTIONID = PAYMENT.ID
and PAYMENT.ADJUSTMENTID is not null
and POSTSTATUSCODE = 1
and TYPECODE = 0
and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null;
-- Fix debit GL distributions for deposited payments.
declare @PAYMENTDEPOSITID uniqueidentifier;
declare deposit_cursor cursor local fast_forward for (
select ID, DEPOSITID from @PAYMENTS where DEPOSITID is not null
);
open deposit_cursor;
fetch next from deposit_cursor into @PAYMENTID, @PAYMENTDEPOSITID;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @PAYMENTID, @PAYMENTDEPOSITID;
fetch next from deposit_cursor into @PAYMENTID, @PAYMENTDEPOSITID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end