Finding Exchanges

Gawain,

Firstly, we do exchanges in a separate order in an Exchange MOS, so I have little practical experience to offer (though I DO highly recommend using a new order with a separate MOS if that is anywhere in the discussion).  Off the top of my head, that DOES sound like it would pose a bit of an issue.  I mean, you could look for specific sli_statuses, but there are plenty of NON-exchange reasons to develop that status.

The only thought I have is that I know you CAN change the line item source number even on returned line items.  So what if you had a specific source number (or group of source numbers) that your staff used on those that were returned for purposes of an exchange versus other reasons?

Of course... that means that your staff has to be able to at least somewhat dependably DO that, but it is something.

Best of luck.

John

Alas, my project is to find and analyze historical exchanges...

Ah... then I have nothing beyond looking for orders where the return line item is not the same as the sold line item.  Good luck?

To find the orders with returns held on account, you could use the payment method and sub line item status to find some of the orders you are looking for.

Something like:

USE impresario
GO
;
DECLARE @Begin_dt DATETIME = '2019-01-06 00:00:00'
DECLARE @End_dt DATETIME = '2019-01-07 00:00:00'
;
SELECT DISTINCT SLI.order_no, SLI.sli_status, P.pmt_method, P.pmt_amt
FROM T_PAYMENT AS P
INNER JOIN T_TRANSACTION AS T ON T.transaction_no = P.transaction_no
INNER JOIN T_ORDER AS O ON O.order_no = T.order_no
INNER JOIN T_SUB_LINEITEM AS SLI ON SLI.order_no = O.order_no
WHERE P.pmt_method IN (52,107)
--Your On Account payment method(s).
AND P.pmt_amt < 0
AND SLI.sli_status IN (4)
--status of 4 = returned.
AND O.order_dt BETWEEN @Begin_dt AND @End_dt
;

Or maybe find a few examples and PORD a few of the order_no's to help develop a strategy?

It does not sound like a fun project, most likely a bunch of different situations to factor in.

I had to do something similar to create a subscription history that includes exchanges in the same order and in another order. The way i did it was with a recursive CTE using the ret_parent_sli_no. I'ts been a while since i wrote it and i don't comment my code well but am happy to share and answer questions.

I'm sure it would be helpful, thanks!

here you go.

USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LWP_Get_Subscription_info_new] Script Date: 1/8/2019 1:26:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mendy
-- Create date: 6/23/15
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[LWP_Get_Subscription_info_new]
--@customer_no int,
@sessionkey varchar(64) = null,
@curentFY int = null,-- = 2016
@pastHistory int = null-- = 4
AS
SET NOCOUNT ON;

--/*
Declare @errmsg varchar(200), @customer_no int

-- validate parameters
If @sessionkey is null or Not exists (select * from t_web_session_session where sessionkey = @sessionkey)
Begin
select @errmsg = 'Invalid Session ID'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Select @customer_no = customer_no from t_web_session_session where sessionkey = @sessionkey

If @customer_no is null or Not exists (select * from t_customer where customer_no = @customer_no)
Begin
select @errmsg = 'Invalid Customer_no'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Exec WP_SET_WEB_CONTEXT @sessionkey = @sessionkey
--*/

-- if no fiscal year provided if it is past 6/30 the fiscal year is next year otherwise it is this year
set @curentFY = (coalesce(@curentFY,
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
end
))

select
distinct
ord.customer_no,
1 as Subscription_Order_No,
ord.order_no,
ord.order_dt,
cast(
case
when delivery.id = 1 then 'Postal Mail'
when delivery.id = -1 then 'Hold at Box Office'
when delivery.id in (3,4) then 'E-Ticket Print At Home'
else ''
end as varchar(30)) as DeliveryMethod,
--ord.appeal_no,
--apl.description as appeal_desc,
--apl.campaign_no,
--cmp.description as campaign_desc,
--ord.source_no,
--src.source_name,
ord.MOS as MOS,
mos.description as MOS_Desc,
cat.description as category,
ord.tot_contribution_amt,
ord.tot_fee_amt,
ord.tot_ticket_purch_amt + ord.tot_ticket_return_amt as tot_ticket_purch_amt,
li.li_seq_no as lineitem,
sli.sli_no as Sub_lineitem,
case when sli.due_amt = 0 then 'True' else 'False' end as comp,
--sli_d.detail_sli_no as Sub_lineitem_detail_no,
sli.sli_status,
sli_st.description as sli_status_desc,
sli.ret_parent_sli_no,
sli.price_type,
perf.season_no as season,
perf.season_name,
pkg.pkg_code,
pkg.description as pkg_desc,
perf.perf_code,
perf.perf_no,
prodExch.Exch_Start_Dt,
prodExch.Exch_End_Dt,
prodExch.Display_Seating,
Perf_Preview = case when exists(select * from TX_INV_TKW aa (nolock) where aa.inv_no = perf.perf_no and aa.tkw = 46) then 'True' else 'False' end,
perf.prod_name as perf_name,
coalesce(Full_Name,perf.prod_name,'') as full_perf_Name,
perf.perf_dt,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'Stage I'
when perf.facility like '%Friedman%' then 'Friedman'
else perf.facility
end as facility_short, --perf.facility,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'NY CITY CENTER Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'NY CITY CENTER Stage I'
when perf.facility like '%Friedman%' then 'SAMUEL J. FRIEDMAN THEATRE'
else perf.facility
end as facility_Full, --perf.facility,
perf.fyear,
seat_sec.description as section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
cast(null as int) as ticket_no,
cast(null as char(1)) as [checksum],
--pt.description as priceType,
--pt_cat.description as priceTypeCat,
--p_cat.description as PriceCat,
sli.due_amt as sli_due_amt,
--ord.tot_contribution_amt as order_cont_amt,
--sli_d.due_amt as sli_d_due_amt,
--li.tot_pur_amt,
li.num_seats_pur,--is subscription when perf_code like '^%'
li.tot_ret_amt,
isnull(li.num_seats_ret,0) as num_seats_ret
--sli.last_updated_by,
--sli.last_update_dt
into #work1
from
t_order ord (nolock)
left outer join tr_order_category cat (nolock) on cat.id = ord.class
left outer join t_lineitem li (nolock) on li.order_no = ord.order_no
left outer join t_appeal apl (nolock) on apl.appeal_no = ord.appeal_no
left outer join t_campaign cmp (nolock) on cmp.campaign_no = apl.campaign_no
left outer join TX_APPEAL_MEDIA_TYPE src (nolock) on src.source_no = ord.source_no
left outer Join tr_mos mos (nolock) on mos.id = ord.mos
left outer join LV_PERF_PROD_SEASON perf (nolock) on perf.perf_no = li.perf_no
left outer join LTR_Prod_Titles_Complete Perf_fullName on perf.prod_season_no = Perf_fullName.Prod_Season_No
left outer join ltr_prod_Exch_Open_Close prodExch (nolock) on perf.prod_season_no = prodExch.prod_season_no
left outer join t_pkg pkg (nolock) on li.pkg_no=pkg.pkg_no
left outer join t_sub_lineitem sli (nolock) on sli.li_seq_no = li.li_seq_no
left outer join TR_SLI_STATUS sli_st (nolock) on sli.sli_status = sli_st.id
left outer join tr_price_type pt (nolock) on sli.price_type = pt.id
left outer join TR_PRICE_TYPE_CATEGORY pt_cat (nolock) on pt_cat.id = pt.price_type_category
left outer join TR_ORDER_SHIP_METHOD delivery (nolock) on ord.delivery = delivery.id
--left outer join t_sli_detail sli_d (nolock) on sli_d.sli_no = sli.sli_no
--JOIN t_pmap pmap (nolock) on pmap.pmap_no = sli_d.pmap_no
--join TR_PRICE_CATEGORY p_cat (nolock) on p_cat.id = pmap.price_category
left outer join t_seat seat (nolock) on seat.seat_no = sli.seat_no --and isnull(sli.seat_no,0) > 0
left outer join tr_section seat_sec (nolock) on seat_sec.id = seat.section
where
customer_no = @customer_no
--ord.order_no = 488993
--and perf_code not like '^%'
and prod_name not like '*CH%'
and prod_name not like '!%'
and prod_name not like '%Passport Voucher%'
--perf.season_no in (8)
and li.primary_ind = 'Y'
and
(
ord.tot_ticket_purch_amt > 0
or
li.num_seats_pur > li.num_seats_ret
)
--and ord.tot_ticket_paid_amt > 0
--and ord.mos in (10,20)
--and not isnull(ord.tot_ticket_return_amt,0) = (-1 * ord.tot_ticket_purch_amt)
and perf.fyear > @curentFY - isnull(@pastHistory,1)

and sli.sli_status not in (7)
--and not (sli.sli_status in(8,7) and mos not in(10)) -- no returns and exchanges that arent in the original subscription order
--and cat.description not like '%flex%'
--order by
--ord.customer_no,
--ord.customer_no, season, mos, ord.order_no, li.li_seq_no, sli.sli_no--, sli_d.detail_sli_no

--select * from #work1

-- add subscription order_no each ticket belongs to
;with
orders(parent_order_no, child_order_no) as
(
select distinct
sli_parent_order.order_no as parent_order_no,
sli_child_order.order_no as child_order_no--,
--row_number() over (partition by sli_child_order.order_no order by sli_child_order.order_no) as sort
from
T_SUB_LINEITEM sli_child_order
join T_SUB_LINEITEM sli_parent_order on sli_child_order.ret_parent_sli_no = sli_parent_order.sli_no
where
-- focus on orders for specific customer
sli_child_order.order_no in (select distinct order_no from #work1) -- t_order where customer_no = @customer_no)
-- sub line item is a return
and sli_child_order.sli_status = 4 -- 4 = return
and sli_parent_order.sli_status = 8 -- 8 = VOO
--and
-- and this order does not have any sub line items that are VOO
--and sli_child_order.order_no not in (select order_no from T_SUB_LINEITEM a where a.sli_status = 8)
),
w1(parent_order_no, child_order_no, level) as
(
select distinct
parent_order_no,
child_order_no,
--sort,
0 as Level
from
orders
join t_order ord on orders.parent_order_no = ord.order_no
where
--child_order_no not in (select distinct parent_order_no from orders) -- no returns against this order
--and sort = 1
ord.mos = 10

--/*
UNION ALL

select
w1.parent_order_no,
orders.child_order_no,
--orders.sort,
Level + 1
from
orders
join w1 on w1.child_order_no = orders.parent_order_no
--and orders.sort = 1
--and w1.sort = 1
--*/
)
update a
set a.Subscription_Order_No = case when MOS <> 9 then coalesce(w1.parent_order_no,a.order_no) else 1 end
from #work1 a
left outer join w1 on a.order_no = w1.child_order_no

--/*

--select * from #work1

-- for the order date use either the greater of (order date / erliest ticket purchase trasnaction date)
;with order_dt(order_no, order_dt)
as
(
select order_no, max(order_dt) from(
(
select
t_order.order_no as order_no, max(t_order.order_dt) as order_dt
from
t_order
join #work1 on t_order.order_no = #work1.order_no
group by t_order.order_no
--where
--order_no = @order_no
union
select
T_TRANSACTION.order_no as order_no, min(T_TRANSACTION.trn_dt) as order_dt
from
T_TRANSACTION
join #work1 on T_TRANSACTION.order_no = #work1.order_no
--where
--order_no = @order_no
and trn_type = 32
group by T_TRANSACTION.order_no
)

) as aaaa
group by aaaa.order_no
)
update a
set a.order_dt = b.order_dt
from
#work1 a
join order_dt b on a.order_no = b.order_no

-- add ticket numbers from tx_sli_ticket

update a
set
a.ticket_no = b.ticket_no,
a.[checksum] = b.[checksum]
from
#work1 a
join (
select aa.sli_no, aa.ticket_no, aa.pkg_no, aa.perf_no, aa.seat_no, aa.[checksum],
row_number() over(partition by sli_no Order by reprint_no desc) rowNumber
from TX_SLI_TICKET (nolock) aa
join #work1 bb on aa.sli_no = bb.Sub_lineitem
)as b on a.Sub_lineitem = b.sli_no and rowNumber = 1

select
*
into #work2
from

(
select distinct
'sub' as rowType,
*
from
#work1 a
--join subCount b on b.order_no = a.order_no
where
MOS in (10) -- 10=Subscription
and sli_status not in(4) --4=Return

--select 'subscription exchanged history'

union all

select
'sub exch' as rowType,
*
from
#work1 a
where
mos = 5 --5=Exchanges
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/

union all

--select 'single ticket history'
select
'Add' as rowType,
*
from
#work1 a
where
mos = 9 -- 9=Single Tickets
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/
) a

declare @subscriptionHistory varchar(max)

set @subscriptionHistory = (
select --distinct
season.Season,
season.SeasonDescription,
SubscriptionHistory.SubscriptionType,
SubscriptionHistory.OrderDate,
SubscriptionHistory.Subscription_Order_No,
SubscriptionHistory.tot_contribution_amt,
SubscriptionHistory.tot_fee_amt,
SubscriptionHistory.tot_ticket_purch_amt,
SubscriptionHistory.DeliveryMethod,
SubscriptionHistory.PreferredDay,
SubscriptionHistory.NumSeats,
SubscriptionHistory.FixedOrFlex,
SubscriptionHistory.[Current],
--Performances.season,
--Performance.rowType,
Performance.perf_no,
performance.perf_code,
performance.Exch_Start_Dt,
performance.Exch_End_Dt,
performance.ExchOpen,
Performance.Display_Seating,
Performance.Perf_Preview,
Performance.Description,
Performance.Description_Short,
Performance.Date,
Performance.Venue,
Performance.venue_short,
seat.rowType,
seat.comp,
seat.price_type,
seat.sli_due_amt,
seat.section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
seat.ticket_no,
seat.[checksum],
seat.sli_status,
seat.sli_status_desc,
seat.exchangeable,
seat.order_no,
seat.order_dt,
seat.DeliveryMethod--,
--Seat.tot_contribution_amt,
--Seat.tot_fee_amt,
--Seat.tot_ticket_purch_amt
/*,
AddOn.season,
AddOn.perf_name,
AddOn.perf_no,
AddOn.rowType,
AddOn.section,
AddOn.seat_row,
AddOn.seat_num,
AddOn.seat_no,
AddOn.ticket_no,
AddOn.[checksum],
AddOn.sli_status,
AddOn.sli_status_desc,
AddOn.exchangeable,
AddOn.order_no,
AddOn.order_dt,
AddOn.DeliveryMethod,
AddOn.tot_contribution_amt,
AddOn.tot_fee_amt,
AddOn.tot_ticket_purch_amt
*/

from
-- seasons
(
select distinct
season,
season_name as seasonDescription
from
#work2
) as season
-- Subscription rows
join (
select distinct
max(category) as SubscriptionType,
Subscription_Order_No,
season,
--max(season_name) as seasonDescription,
max(order_dt) as OrderDate,
--order_no,
max(DeliveryMethod) as DeliveryMethod,
max(tot_contribution_amt) as tot_contribution_amt,
max(tot_fee_amt) as tot_fee_amt,
max(tot_ticket_purch_amt) as tot_ticket_purch_amt,
--cast(convert(varchar,max(order_dt),101) as varchar) as OrderDate,
max(case
when pkg_desc like '%Tue%' then 'Tuesday'
when pkg_desc like '%Wed%' and pkg_desc like '%Eve%' then 'Wednesday Eve'
when pkg_desc like '%Wed%' and pkg_desc like '%Mat%' then 'Wednesday Mat'
when pkg_desc like '%Thu%' then 'Thursday'
when pkg_desc like '%Friday%' then 'Friday'
when pkg_desc like '%Sat%' and pkg_desc like '%Eve%' then 'Saturday Eve'
when pkg_desc like '%Sat%' and pkg_desc like '%Mat%' then 'Saturday Mat'
when pkg_desc like '%Sun%' and pkg_desc like '%Eve%' then 'Sunday Eve'
when pkg_desc like '%Sun%' and pkg_desc like '%Mat%' then 'Sunday Mat'
else ''
end) as PreferredDay,
max(num_seats_pur-num_seats_ret) as NumSeats,
max(case
when category like '%Flex%' or category like '%multi%' then 'Flex'
else 'Fixed'
end) as FixedOrFlex,
max(case
when fyear =
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
--datepart(YYYY,DATEADD(Y,1,datepart(YYYY,memb.init_dt)))
end
then 'True'
else 'False'
end) as [Current]

from
#work2
where
rowType in ('Sub', 'Add')--
group by season,Subscription_Order_No--order_no
) as SubscriptionHistory on SubscriptionHistory.season = season.season
-- performances
join (
--#results
select distinct
--customer_no,
season,
Subscription_Order_No,
order_no,
rowType,
--lineitem,
perf_no,
perf_code,
Perf_Preview,
Exch_Start_Dt,
Exch_End_Dt,
ExchOpen = case when
Exch_Start_Dt is not null
and Exch_End_Dt is not null
and DATEADD(day, DATEDIFF(day, 0, Exch_Start_Dt), 0) <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
and DATEADD(day, DATEDIFF(day, 0, Exch_End_Dt), 0) > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
then 'True'
else 'False' end,
Display_Seating = case when DATEADD(day, DATEDIFF(day, 0, Display_Seating), 0) >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) then 'True' else 'False' end,
perf_name as Description_Short,
full_perf_Name as Description,
perf_dt as Date,
facility_Full as Venue,
facility_short as venue_short
from #work2
)as Performance on SubscriptionHistory.Subscription_Order_No = Performance.Subscription_Order_No and SubscriptionHistory.season = Performance.season
-- seats - sub and sub exch
join
(
select distinct
Subscription_Order_No,
perf_no,
rowType,
comp,
price_type,
sli_due_amt,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
case
when perf_dt > dateadd(DD,1,getdate())
and rowType like 'sub%'
and sli_status in(1,2,3,5,6,9,10,11,12,14)
then 'True'
else 'False'
end as exchangeable,
order_no,
order_dt,
DeliveryMethod--,
--tot_contribution_amt,
--tot_fee_amt,
--tot_ticket_purch_amt

from
#work2
--where
--rowType in ('sub', 'sub exch')
) as Seat on Performance.Subscription_Order_No = Seat.Subscription_Order_No and Performance.perf_no = Seat.perf_no
/*
-- seats - additional tickets
left outer join
(
select distinct
season,
perf_name,
perf_no,
rowType,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
'False' as exchangeable,
order_no,
order_dt,
DeliveryMethod,
tot_contribution_amt,
tot_fee_amt,
tot_ticket_purch_amt

from
#work2
where
rowType in ('Add')
) as AddOn on SubscriptionHistory.season = AddOn.season
*/
order by SubscriptionHistory.season, Performance.Subscription_Order_No, Performance.Description,
-- sort performances
case
when Performance.rowType = 'sub' then 1
when Performance.rowType = 'sub exch' then 2
when Performance.rowType = 'Add' then 3
end,

-- sort seats in the same performance
case
when seat.rowType = 'sub' then 1
when seat.rowType = 'sub exch' then 2
when seat.rowType = 'Add' then 3
end,

seat.section, seat.seat_row, Seat.seat_num
for xml auto, ROOT ('SubscriptionHistory'), ELEMENTS XSINIL);

set @subscriptionHistory = coalesce(@subscriptionHistory,'')
select @subscriptionHistory
--*/

drop table #work1
--,#work2--,
-- #results,
--#performances,
--#subscriptionHistory

return