Reconciliation formula


In any given period:

Sum [Payment, Success Status] = Sum [TRX, Paid Status] Only when a success payment is processed can a paid status payment created.

Sum [TRX, Paid Status] = Sum [Tickets, all status] + Sum [Paid in item, all status] + Sum [Gift Certificate, paid] - Sum [Refunds, all status] + Sum [TRX Fees] + Sum [Redeemable Items, all status] – Sum [Redeemable Items, Updated in same period] Here are some of the general system behaviors which hopefully will make the reconciliation formula between Transactions and Tickets easier to comprehend:

For majority of the tickets sold, the work flow is straight forward: A ticket is generated when a transaction is successful. Paid transaction total equals paid ticket amount. Similarly when a gift certificate or a paid in item is created, a paid transaction is generated, and the transaction total equals to the amount paid for the gift certificate.

For tickets that were changed, the original transaction that generated the ticket and ticket info remain the same and offset each other. To record the change, the system will create a new transaction with "0" balance, in which you will see a new ticket with the ticket amount, and a refund with the same amount. So using the formula above you will have tickets minus refunds equals transactions which is 0.

For tickets that were cancelled, similar to the changed tickets, the original transactions and tickets remain the same; a new transaction is created to record the cancellation. But what differs is that there will be no tickets created, instead, a refund will be created, if there a fee setup for cancellations, a transaction fee record will be created too. Taking a ticket worth $10 for example, and fee for cancellation is $1 for example, the system will record a refund of 10, transaction fee of 1, and transaction of -9, so the formula will work as -10+1- (-9) =0

When tickets were purchased using the pre bought redeemable items, a layer of logic adds to the above: When a bundle/Redeemable items were purchased, a transaction is created. To recon this we will need redeemable items created minus the transaction; When a redeemable item is redeemed, there is no new transaction is created, but a ticket will be generated, to recon this the ticket minus the redeemable item redeemed, it will give us 0.