• Replies 2 replies
  • Subscribers 124 subscribers
  • Views 234 views
  • Users 0 members are here
Related

Reporting on Refunds

Michelle Blau (Past Member)
$organization

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

  • $organization

    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 P
    inner 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 T
    cross 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 S
    order by CustomerNo , SeqNo

    Here is a sample of what the result set looks like showing a couple of different scenarios:

  • $organization in reply to Ronald Radford

    Thanks for this. I'm relatively new to SQL, but I should be able to drop this into Test and see what I get!