Explanation of the date fields in the RetailTransactionTable used for reviewing Commerce transactions.

Retail store sales transactions are manipulated in multiple ways from the start of a temp CRT transaction to saving it to the POS side of the database and then syncing this back to HQ for posting. When writing these transactions to the database there are 7 different fields that track various date/time stamps. While most of these will be within a second of each other, there may be a time where there is a large enough difference that will raise questions and concerns. The purpose of this informational article is to assist you with identifying which fields are being utilized in the Show Journal and through Retail reports in HQ so that you are able to more accurately compare the data for reconciling.

Below are the 7 main fields within the RetailTransactionTable and how they are set in relation to the Sales type transactions:

CreatedDateTime:
For POS this is when the transaction was first recorded into the SQL database on the POS side
For Headquarters, this is when the transaction is synced via the P-job or an RTS call with customer order

ModifiedDateTime:
For POS this is when the transactional record is updated in the SQL database
For Headquarters, this is when the transaction is synced via the P-job, an RTS call with customer order, or if the line is modified by another action

BeginDateTime: Date and time when the transaction was initiated/started

TransDate: Date of when the transaction completed

TransTime: Specific time when the transaction completed (listed in the database as seconds past midnight)

TimeWhenTransClosed: Specific time when the transaction completed (listed in the database as seconds past midnight)

BusinessDate: Normally the “date” of when the transaction occurred. This can differ from the TransDate if your “business day” runs past midnight (say until 2am). If it does, these transactions post with the previous days “Business date”. There is a setting in the Retail Store form for “Post as business day” that enables this.

How the date/time is displayed in POS and HQ:
- The Show Journal in POS utilizes the CreatedDateTime for displaying the date/time, this is pulling the transaction locally from the POS database.
- The Advanced search POS operation makes a call to HQ for transactional information. This concatenates the TransDate + TimeWhenTransClosed from the result set to display the date/time of the order.
- The Store transactions report in HQ utilizes the Transdate and TransTime fields.

In most occurrences the date and time stamps should match if we are comparing the HQ Retail store transactions report and the Show Journal. It is possible that they can be off by a second or two, but as long as we are comparing the correct fields there shouldn't be an issue. Now if we were to compare the CreatedDateTime between HQ and POS, this is an incorrect comparison and should not be used in HQ reports.

For non-sales type transactions (such as Logon, Logoff, Tender declaration, etc.), these do not use the BeginDateTime as they are recorded as they occur using the TransactionTime field.

I hope this helps in some way of recognizing where and how dates are set for these fields to either reconcile trouble transactions or create accurate reports.