hen we were forced to cancel productions for the rest of our 2019-2020 season, we manually returned tickets and put this money on account. Patrons then had the option to request a refund, donate or keep credit for future purchases. Reporting on donations was relatively easy since you can pull based on fund, however, we had much difficulty reporting on refunds. For a refund, the money on account was applied to a new order, then refunded. The only way we could pull refund data is by source code and then manually tracking refund amounts. However, this process was extremely tedious an susceptible to a lot of human error. Analytics was also not useful since the transaction type is considered "Zero Adjustment." Standard reports also did not work for us for similar reasons.
While we are hoping we don't have more mass cancellations for our upcoming all streaming season, I am preparing in case we do and hope to make changes to our process so we can have better reporting. If we opted not to put all of the money on account from the get-go, I think we would be able to yield better results in terms of using standard reports and analytics. However, then there is money sitting cancelled performances.
Did anyone have a similar experience? Or does anyone have other recommendations for processing cancellations that yield better results for reporting?
Much appreciated,
Michelle
In SQL you can do something like this which will at least show you when the money was refunded and where it went after it was refunded.
Declare @tblHist table(CustomerNo int index idxCustomer,OrderNo int,RefNo int,PerfNo int,PaymentMethodID int,SeqNo int,TransactionNo int,TranDate datetime,OnAccount Money,OffAccount money,primary key(Customerno,PaymentMethodID, SeqNo))insert into @tblHist Select customer_no , isnull(order_no,0) as OrderNo , ref_no, perf_no , pmt_method,ROW_NUMBER () over (partition by Customer_No, pmt_method order by p.transaction_No) as SeqNo, p.transaction_no , Min(Trn_Dt) as TranDate,abs( Sum(iif(Pmt_Amt <0, Pmt_Amt,0.00))) as OnAccount , Sum(iif(Pmt_Amt >0, Pmt_Amt,0.00)) as OffAccount from T_PAYMENT as Pinner join T_TRANSACTION as T on p.transaction_no = p.transaction_no and t.sequence_no = p.sequence_no inner join TR_PAYMENT_METHOD as Pm on p.pmt_method = PM.id where pmt_type = 2 --and customer_no in( 3963172, 1119668,1798352,2037896) --and p.created_by = 'rradford' group by customer_no , order_no , ref_no, perf_no ,pmt_method, p.transaction_no ; WITH Totals (CustomerNo, OrderNo, RefNo, PaymentMethodID , SeqNo, TransactionNo, TranDate, OnAccount, OffAccount, Balance) AS ( Select CustomerNo, OrderNo, RefNo, PaymentMethodID , SeqNo, TransactionNo, TranDate, OnAccount, OffAccount, Balance from @tblHist as H cross apply ( Select sum(OnAccount -OffAccount ) as Balance from @tblHist as R where r.CustomerNo = h.CustomerNo and r.PaymentMethodID = h.PaymentMethodID and r.SeqNo <= h.SeqNo ) as RT) SELECT *FROM Totals as Tcross apply (Select Count(*) as HistSeqNo from Totals as C where t.CustomerNo = c.CustomerNo and t.SeqNo >= c.SeqNo and C.SeqNo > (Select isnull(Max(Seqno),0) from Totals where CustomerNo = t.CustomerNo and SeqNo < t.SeqNo and Balance = 0) ) as Sorder by CustomerNo , SeqNo Here is a sample of what the result set looks like showing a couple of different scenarios:
Thanks for this. I'm relatively new to SQL, but I should be able to drop this into Test and see what I get!