The Cross-Day Transaction Issue With POS Retail

The Problem
As our business have identified, a cross-day transaction, which started in an earlier day but finished in another day, causes  issues in sales posting and reporting – they are excluded from the previous-day posting of sales transactions because they have an older time stamp out of the day for posting; and would  be excluded too from earlier postings as they are not yet created as of the posting time.  These transactions typically have an older day for the transaction date and time in the transaction header while have mixed older days and newer days of transaction date time in the product lines. They cause reporting issues too.

How can it happen
Our investigation found that it happens when the POS leaves an outstanding transaction (neither voided nor paid) for a day and continue the transaction the next business day. A cashier logs in and finds an outstanding transaction pulled up on the POS, she may void the product line and add new lines, rather than void the whole transaction, to continue a transaction, but doesn't know the transaction ends up being a cross-day transaction, and causes issues.

This scenario is abnormal but does happen in practice. If the POS is left open without a close shift operation ensured at the end of a day, an unclosed transaction is there, then the POS logs itself off when the session timeout, and the next day a cashier logs in, a cross-day transaction could be produced.

Solutions
Our proposed solution is that when an outstanding transaction from a previous day is found upon a cashier’s logging in, the system discards the transaction by deleting the record from RetailTransaction table, giving the cashier a clean start, and the system will prompt the cashier if done so.

To accomplish this, we create a new table, RETAILTRANSACTIONTIMESTAMP, in the database for recording the time stamp of an outstanding transaction when it is created, and have two methods for updating the table: Set() and Reset(). Set() method is called from ItemTriggers.PostSale in condition [!IsSet()], which means a new transaction is being created;  Reset() is called from ApplicationTriggers.PostLogon in condition of [!HasOutstandingTransaction()], which means no outstanding transaction found upon after login successfully. In addition, another method Transactions.IsStandingTransCrossDay() verifies and discards an outstanding transaction if it crosses days. (figure 1 & 2)

The reason to not create a time stamp column in RETAILTRANSACTION table where the outstanding transaction is stored but rather to create a time stamp table is that the record is constantly recreated rather than updated, even when a cashier logs in, so timestamps there  won’t keep.

P.S., Outstanding transactions (unpaid or voided) are sitting in RETAILTRANSACTION table with encrypted content per data-area, store, and terminal. The encrypted content held the transaction details including date and time with the transaction (the header), and the date and time with each product line. Since a transaction is created when the first product line is being added, and the other product lines are added sometime later, the transaction and each product line has its own time stamp. Outstanding transactions get saved into proper transaction tables when they are paid or voided, and the staging store in RETAILTRANSACTION table will be deleted until then. Note, when permanently saved the time stamp for the transactions and the product lines are set as were in the encrypted content, instead of the date and time when the transaction is paid or is voided .