Liquor Transaction Export Report
This section describes the specifics of accessing and interpreting the Liquor Transaction Export report.
- Opening Report
- Setting Up Export
- Export File Format
- Error Handling
- Typical Errors
- Additional Actions
The Liquor Transaction Export report is intended for customers who interface with the State of Ohio – Division of Liquor Control (DOLC) Liquor Management System.
By state law, liquor convenience stores in the State of Ohio must report sales to the DOLC on a daily basis. The stores also must be able to retrieve prices from the DOLC since they control the retail selling price of the wine and liquor in the stores.
The report helps solving the following primary tasks:
- Reporting transaction-level retail sales data.
When configuring the report, the station manager can select items whose sales data must be reported to the DOLC. When the report is run, CStoreOffice® automatically retrieves data for these items from the list of sales for the current day, generates files of the necessary format and uploads the files to the DOLC web server, as prescribed by law. - Obtaining actual retail prices and VIN codes.
When the report is run, CStoreOffice® automatically requests the latest retail pricing information from the DOLC web server and updates data for sales items in the Price Book. CStoreOffice® also collects information about VIN codes for sales items, compares them with those specified in the Price book by the items UPC and automatically updates VIN codes if necessary. This helps to ensure that the latest pricing information is available in CStoreOffice®.
Opening Report
You can find the report at Reports > Accounting > Liquor Transaction Export. For more information, see Viewing Reports.
Setting Up Export
To set up the Liquor Transaction Export, specify the following information:
- In the Categories field, specify categories whose sales data you want to export:
- From the Add category list, select the necessary category.
- To add one more category, click Add category and select the necessary category from the list.
- To delete an added category, click the Delete button on the right of the category list.
- In the Vendor ID field, specify the vendor ID.
- In the Email for alerts about export errors field, specify an email address at which you want to send notifications about errors that occur during the export process.
To specify additional email addresses, click Add email and enter the necessary addresses in the fields below. - From the Transport list, select the method of data transferring — FTP, SFTP or Email:
- If you have selected Email, in the Email field, enter an email address at which you want to send the exported data.
To specify additional email addresses, click Add email and enter the necessary addresses in the fields below. - If you have selected FTP or SFTP, specify the following parameters of the FTP server on which you want to store the exported data:
- In the Host field, specify the host name of the FTP server.
- In the Path field, specify a path to the folder in which the exported data must be stored.
- In the User Name and Password fields, specify credentials that must be used to access the FTP server.
- In the Export from Stations list, configure settings for those stations whose data you want to export:
- In the Transport field, leave the DOLC option selected.
- In the Credentials field, click the gear icon and specify connection settings for the DOLC web server: server name, user name and password. When the report is run, CStoreOffice® will communicate with the DOLC web server and retrieve/upload the necessary information from/to it.
- In the Liquor Station Number field, specify the number for the station provided by the DOLC.
- To get actual prices and VIN codes from the DOLC web server, select the Get Prices check box.
- To enable communication with the DOLC web server, select the check box on the right of the station name.
Connection settings for the DOLC web server are to be provided by the DOLC representatives.
- In the table on the right, specify a schedule for report generation per every week day:
- From the Time list, select the time when the report must be generated.
- From the Period list, select the period for which the report must be generated: Today, Last day or Last week.
- In the Number of resubmission retries field, specify the number of attempts that CStoreOffice® must make to send the report to the destination. After the last failed attempt, CStoreOffice® sends an email notification with the error description to the system administrator. For more details, see File Sending Control.
- In the Waiting in minutes for repeat field, specify the time interval between attempts to send the report to the destination. If CStoreOffice® fails to send the report for some reason, it waits for the specified period of time and then makes a new attempt. By default, the time interval between attempts is 30 minutes.
- At the top of the window, click Save.
Export File Format
The Liquor Transaction Export report is generated as an archive that contains files of the JSON format. For every selected station, two files are generated:
- File containing detailed daily sales data for specified items at the selected station.
- File containing total daily sales data for specified items at the selected station.
When the report is run, CStoreOffice® first retrieves prices for items from the DOLC web server. The report does not contain data for items that are absent in the web server response and items that have blank or null values in the effective date and current price fields.
The generated files are sent at the email address or stored in the folder on the FTP/SFTP server, and uploaded to the DOLC web server.
File Sending Control
CStoreOffice® allows you to control if the required reports are generated and sent successfully to the destination by schedule. To do this, CStoreOffice® introduces a special service—File Sending Control—that is responsible for the following activities:
When the report is to be generated by schedule, CStoreOffice® launches the File Sending Control service. The service performs the following actions:
- The service checks if data for report generation is available:
- If data for report generation is available, CStoreOffice® generates the report (see step 2).
- If data for report generation is not available, CStoreOffice® writes information about the error to the report log and sends an email notification with the error description to the system administrator.
- CStoreOffice® generates the report and attempts to send it to the specified destination:
- If the report is successfully sent, CStoreOffice® writes information about successful report delivery to the report log.
- If the report fails to be sent, CStoreOffice® waits for the period of time defined in the Waiting in minutes for repeat field and makes another attempt to send the report. CStoreOffice® attempts to send the report for the number of times defined in the Number of resubmission retries field. If the last attempt fails, CStoreOffice® writes information about the last error to the report log and sends an email notification with the error description to the system administrator.
If the report could not be sent to the location five times due to the connection issues, the report sending is turned off automatically. The location's manager will receive an immediate e-mail with the details about the reasons of the report sending cancellation.
CStoreOffice® lets you check the report schedule and send missed reports to the destination. A report is considered missed if it was to be generated and sent at a specific time, but CStoreOffice® failed to do this for some reason. Such situation can occur, for example, if the software system was shut down for a long period of time.
To overcome such situations and make sure that reports are generated and sent to the destination by the defined schedule, CStoreOffice® leverages the report verification mechanism.
The report verification mechanism is launched every 15 minutes. The mechanism retrieves the date when the report was generated and sent for the last time and compares this date with the current date. Depending on the comparison results, CStoreOffice® can perform the following activities:
Case 1. Date of Latest Report is Earlier Than Current Date
If the date of the latest report is earlier than the current date (that is, the latest report was generated before the current date), CStoreOffice® calculates the date when the latest report was to be created. After that, CStoreOffice® compares the date of the latest report and the calculated date:
- If the date of the latest report and the calculated date match, CStoreOffice® considers that the report was successfully generated and sent and performs no additional activities.
- If the date of the latest report and the calculated date do not match, CStoreOffice® compares the calculated date and the current date:
- If the current date and the calculated date match, CStoreOffice® gets the time for report generation.
- If the current time is earlier than the time for report generation, CStoreOffice® performs no additional activities.
- If the current time is later or equal to the time for report generation, CStoreOffice® launches the report generation and sending task.
- If the current date and the calculated date do not match, CStoreOffice® considers that the report has missed its schedule and launches the report generation and sending task as of the calculated date.
Case 2. Date of Latest Report Matches Current Date
If the date of the latest report matches the current date, CStoreOffice® checks if the report is to be generated on the current day:
- If the report is to be generated on the current day, CStoreOffice® checks the time for report generation.
- If the current time is earlier than the time for report generation, CStoreOffice® performs no additional activities.
- If the current time is later or equal to the time for report generation, CStoreOffice® launches the report generation and sending task.
- If the report is not to be generated on the current day, CStoreOffice® performs no additional activities.
Error Handling
If the Liquor Transaction Export report fails to upload sales data to or obtain prices from the DOLC web server, CStoreOffice® reports an error. You can perform the following activities to get information about the error:
After running the Liquor Transaction Export report, you can view the report log and check what errors have occurred. To open the report log, do one of the following:
- At the top of the report window, click View Log.
- At the bottom of the report window, click Liquor Transaction Export Log.
You can view and work with tickets that are automatically created in CStoreOffice®. The tickets describe errors in the data export process for the Liquor Transaction Export report.
To view a created ticket:
- At the top right corner of the CStoreOffice® main window, click you account name and go to Admin > Tickets.
- In the left menu of the Tickets window, click Tickets List.
- Use filters at the top of the tickets list to find the necessary ticket.
You can instruct CStoreOffice® to send email notifications about errors. If an error occurs during the export process, CStoreOffice® will send an email notification with the StationID and AccountID tags in the HTML format at the specified email address(es).
To enable email notifications:
- Open the report window as described above.
- In the Email for alerts about export errors field, specify an email address at which you want to send notifications about errors.
- To specify additional email addresses, click Add email and enter the necessary addresses in the fields below.
If CStoreOffice® fails to upload sales data to the DOLC web server, it retries data upload for three times.
Typical Errors
When viewing the report log, you may encounter the following error codes:
The error message provides information on missing or incorrect data in your report.
- Find transactions with empty items:
- Ensure that other items do not have any problems:
- Go to Price Book > Items.
- Specify the parameters to find any empty VIN on the departments used by OHIO LCB.
- Click Find.
- Set up the following data for the item:
- Find the GTIN of the item:
- Go to Reports > Store > Cash Register Journal.
- Click Filter by Event > Sale > Refresh.
- From the list of occurred events, select the desired SaleEvent.
- Retrieve GTIN from the displayed data.
- When all the required parameters are added, resend the report:
The error occurs when transaction and total sums are different.
- Download the file for the desired date.
- Open the downloaded file in Notepad or another text editor.
- Find all netAmountInclTax values.
- Add up all obtained values.
- Compare this sum to total on the shift report.
- Follow the steps of the 101 error.
- If nothing helps, submit a ticket. For more details, see Tickets.
The error occurs when the checkTransCount does not match the total item count in the transaction log.
- Download the file for the desired date.
- Open the downloaded file in Notepad or another text editor.
- Find all qty values.
- Add up all obtained values.
- Compare this sum to total on shift report.
- Follow the steps of the 101 error.
- If nothing helps, submit a ticket. For more details, see the Tickets section.
Additional Actions
You can perform the following actions with the Liquor Transaction Export report:
- To save the specified export settings, click Save.
- To view the export history, click View Log at the top of the window or click Liquor Transaction Export Log at the bottom of the window.
- To leave the report interface and go to the home page, click Home.
- To export the report data ahead of the schedule, select the necessary period and click Send.
- To download the exported data in the JSON format, select the necessary period and click Download.
For more information on additional report features, see Reports.