Ticket Lineage

Due to the lack of detail on the sub line items screen our call center struggled to track down the original orders where a ticket that had been exchanged originated. In some cases due to changing covid restrictions we had tickets that were exchanged 4-5 times resulting in 5 different orders due to how the exchange process through the web store worked. To assist with that process the following script was written:

Declare
@OrderNo        int = 2358587-- 2184232--2197402--2358587
----1559553
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Set ArithAbort On

Declare @tblSLI table (
SliNo int
,OrigSliNo int
,Sli_StatusID int
,OrderNo int
,OrigOrderNo int
,OrderDt    datetime
,Category    int
,MOS        int
,Notes        varchar(255)
,CustNo        int
,InitNo        int
,PerfNo int
,PriceTypeID int
,SeatNo int
,Amount money
,CreateDate dateTime
,LastUpdated datetime
,LastUpdateBy varchar(50)
primary key (SliNo, OrigSliNo))

Declare @tblOrd table (
OrderNo int
,RetOrd int
primary key(orderNo, retord))


Declare @tblPmt table(
PaymentNo            int
,TransactionNo        int
,OrderNo            int index idxOrderNo clustered
,PaymentMethod    int
,PaymentDate    datetime
,Amount    money
Primary Key(OrderNo, PaymentNo, TransactionNo))

Declare
@RecCount int

insert into @tblSLI
Select s.sli_no , isnull(s.ret_parent_sli_no,0), s.sli_status , s.order_no, isnull(o.order_no,S.order_no )
, t.order_dt, t.class, t.mos, t.notes, t.customer_no, t.initiator_no, s.perf_no
, s.price_type , s.seat_no, s.due_amt , s.create_dt , s.last_update_dt , s.last_updated_by from T_SUB_LINEITEM as S
left join T_SUB_LINEITEM as O on s.ret_parent_sli_no = o.sli_no
left join t_order as t on t.order_no = s.order_no
where s.order_no = @OrderNo and s.sli_status != 7


Set @RecCount = 1
While @RecCount > 0
Begin
delete from @tblOrd
insert into @tblOrd
Select distinct order_no , OrderNo from @tblSLI as S inner join T_SUB_LINEITEM as Sl on s.OrigSliNo = sl.sli_no
or s.SliNo = ret_parent_sli_no
where s.OrderNo <> sl.order_no

insert into @tblSLI
Select s.sli_no , isnull(s.ret_parent_sli_no,0), s.sli_status , s.order_no, isnull(o.order_no,S.order_no )
, t.order_dt, t.class, t.mos, t.notes, t.customer_no, t.initiator_no, s.perf_no
, s.price_type , s.seat_no, s.due_amt , s.create_dt , s.last_update_dt , s.last_updated_by from T_SUB_LINEITEM as S
left join T_SUB_LINEITEM as O on s.ret_parent_sli_no = o.sli_no
left join t_order as t on t.order_no = s.order_no
where s.order_no in (Select OrderNo from @tblOrd ) and s.sli_status != 7
and not exists (Select 1 from @tblSLI as D where d.SliNo = s.sli_no and isnull(s.ret_parent_sli_no,0) = OrigSliNo )
set @RecCount = @@ROWCOUNT
end

insert into @tblPmt
Select p.payment_no , p.transaction_no , t.Order_No , pmt_method ,max( trn_dt) , sum(p.pmt_amt )   from
 T_TRANSACTION as T
inner join T_PAYMENT as P on t.transaction_no = p.transaction_no and t.sequence_no = p.sequence_no
where order_no in (Select OrigOrderNo  from @tblSLI)
group by p.payment_no , p.transaction_no , t.Order_No  , pmt_method

Select d.* , Isnull(SI.description,'') as SliStatus
,case recType
    when 'Payment' then 1
    when 'Ticket' then 2
    when 'Contribution' then 3
    when 'Fee' then 4
    else 99
    end as GrpSort
,case recType
    when 'Ticket' then isnull(pt.description,'')
    when 'Payment' then isnull(pm.description,'')
    when 'Contribution' then isnull(f.description,'')
    when 'Fee' then isnull(fe.description,'')
    else 'Unknown'
    end as RecordDescr
, isnull(ps.seat_num,0) as SeatNum, isnull(ps.seat_row,0) as SeatRow
, perf_dt as PerfDate, isnull(i.description ,'')as PerformanceDescription
, isnull(oc.description,'') as CategoryName, isnull(mos.description,'') as MOSName
, ltrim(isnull (cu.fname,'')+' '+isnull(cu.lname,'')) as OwnerName
, ltrim(isnull (ci.fname,'')+' '+isnull(ci.lname,'')) as InitiatorName
, isnull(o.customer_no,0) as OwnerNo, isnull(o.initiator_no,0) as InitNo, isnull(Notes,'') as Notes
, order_dt as OrderDate, iif(RecType = 'Ticket', iif(sli_statusid in (4,7,8,11,13), 'R', 'S'),'') as LineType
from
(Select 'Ticket' as RecType,OrderNo, OrigOrderNo, Amount
, SliNo, OrigSliNo, Sli_StatusID
,PriceTypeID , SeatNo
, CreateDate , LastUpdated
, null as PaymentDate, 0 as PaymentMethod, PerfNo, 0 As FeeNo,0 as FundNo
from @tblSLI

union Select 'Payment' as RecType, OrderNo,OrderNo as OrigOrderNo, Amount
, 0 as SliNo, 0 as OrgigSli, 0 as Sli_Status
,0 as PriceTypeID , 0 As SeatNo
,PaymentDate as CreateDate, PaymentDate  as LastUpdated
, PaymentDate, PaymentMethod, 0 as PerfNo, 0 As FeeNo,0 as FundNo
from @tblPmt

Union Select
Case trn_Type
    when 1 then 'Contribution'
    When 52 then 'Fee'
    else 'Unknown'
    end as RecType
,Order_No,Order_No as OrigOrderNo, trn_amt
, 0 as SliNo, 0 as OrgigSli, 0 as Sli_Status
,0 as PriceTypeID , 0 As SeatNo
,trn_dt  as CreateDate, trn_dt   as LastUpdated
, null as PaymentDate,0 as PaymentMethod, 0 as PerfNo, fee_no As FeeNo,fund_no  as FundNo
from T_TRANSACTION  where order_no in (
Select orderno from @tblSLI ) and trn_type in(1,52)



) as D
inner join t_order as o on o.order_no = d.OrderNo
left join TR_SLI_STATUS as SI on d.Sli_StatusID = si.ID
left join TR_PRICE_TYPE as pt on pt.id = d.PriceTypeID
left join TR_PAYMENT_METHOD as pm on d.PaymentMethod = pm.id
left join T_SEAT as ps on ps.seat_no = d.SeatNo
left join T_PERF as P on d.PerfNo = p.perf_no
left outer join T_INVENTORY as I on d.perfno = I.inv_no and I.type = 'R' and perf_no > 0
--left join dbo.LFN_MBA_PERF_SEASON (null, null, null, null, null, null, null) as pf on pf.perf_no = d.PerfNo
left join TR_ORDER_CATEGORY as oc on oc.id = o.class
left join TR_MOS as mos on mos.id = o.MOS
left join T_CUSTOMER as cu on cu.customer_no = o.customer_no
left join T_CUSTOMER as ci on ci.customer_no = o.initiator_no
left join T_FUND as F on d.FundNo = f.fund_no
left join T_FEE as Fe on d.FeeNo = fe.fee_no
order by orderno desc, RecType, OrigOrderNo, PerfDate, seat_row, seat_num

Here is what the report looks like:

If you look through the SQL you will see that the payment date on the report is the transaction date. We made the decision to use the transaction date because it turns out the payment date is based on the time on the machine processing the payment while the transaction date is based on the current time on the server. Ideally both the transaction and payment date should be using the same process, but since they aren't we opted for transaction date. We decided against using the create date on the payment record because there could be some off line instances where the date would represent the time it was imported into the database as opposed to the transaction time.