Follow

HOW TO: Oregon Quarterly Retail Report

Oregon Early Retail Sales Reporting Requirements

RE: INFORMATION BULLETIN 2016-04 sent on Mar 18, 2016

WHAT THE STATE OF OREGON ASKED FOR (Quoting):

There are 2 reports due to the OMMP for Early Retail Sales and proof of paying tax to the Department of Revenue.

Report 1: Early Retail Sales – A report listing each individual sales transactions

1. Dispensary Name
2. Dispensary MMD#
3. Primary PRD Name
4. Primary PRD Phone Number
5. The Following Column Headers:
- a. Date of Sale
- b. Limited Retail Product (product sold)
- c. Ounces or Count (quantity)
- d. Sale Price
6. Be submitted as an excel document

Report 2: Customer Birthdates – this is a report of all retail customers birthdates

- "For birthdates, download a report from your POS system into an excel document. This report does not need to be tied to the specific product sold, but is a listing of all DOB’s for the time frame specified."

Report 3: Proof of Paid Taxes – Documentation may include but is not limited to a copy of the marijuana tax returns, reports, payment vouchers, payment receipts or any related documents filed with the Department.

NOTE: This last report is not a function of MJ Freeway; it's a transaction between you and the state.

HOW TO DO THESE REPORTS IN MJ FREEWAY:

PREREQUISITES:

You must have individual patient or customer records in MJF for each retail customer. They must each have a unique patient ID # and a birthday in the system.

If you have not started doing this, start now for future reports. Some clients have been putting “Rec” as first name and the last 4 of state ID as last name, but please consult your legal advisor for what you can and can’t collect as personal information of Rec customers.

If Rec and Med customers are combined in 1 MJ Freeway location, you should have an easy way of separating them--datawise. "Patient Group" is by far the easiest to work with. "First" or "Last Name" is relatively easy as well.

Many Oregon MJF clients have chosen, against the recommendations of MJF, to put all medical and recreational users under one MJF location. Often they are split by Member/Non-member or patient groups, or by naming conventions. These instructions try to account for a rec naming convention.

If you're separating Rec and Med by name, ensure that Patient Names export on reports. (Even if you don't keep the name columns in the final report.)

(Admin->Settings->Patient Settings): Set “Which patient data would you like it include on receipts and labels?” to “Patient Name” or “Both." 

REPORT 1: Early Retail Sales

Use Order Products Report (Report Center)

(If you need to sort by patient name you may want to consider: Reports->Sales, Then Click "Transactions" tab. But this report does not have quantity for simple items")

  • Click Reports->Report Center
  • Scroll down to the “Store Reports” Heading
  • Click “Order Products Report" link
  • Click “Run Report”
    • Set appropriate settings, including proper date range
    • This will show your running Order Products report in the Report Center Requests with a status of “Pending”
    • With many thousands of orders this report may take a 15 to 30 minutes to run. Wait a few minutes and Refresh your page. Repeat until the “html” and “csv” links appear to the right of your report.
  • Click the “csv” link.
    • This will allow you to download an excel readable report to your computer’s hard drive. Open this file.
  • In Excel do a File->Save As to change this file to to Excel file type that is editable. And change the name to something usable to you
  • Sort by "Patient Group" if you split med and rec patients by that.
    • Delete all med patient rows
  • Keep columns: at least "Product Title," "Units," "Grams Disp," "Unit Price," "Line Subtotal," and "Date Complete," and any other info you wish to provide to the state. Feel free to change column names to help the state understand. 
  • Delete any columns of data that you understand the state DOES NOT require
  • Sort by date
  • Save your file

REPORT 2: Customer Birthdays

Use a Patient Data report (Report Center) You may wish to just add this to another worksheet in your already saved sales report. 

  • Click Reports->Report Center
  • Scroll down to the “Patient Reports” Heading
  • Click “Patient Data” link
  • Click “Run Report”
    • This will show your running Patient Data report in the Report Center Requests with a status of “Pending”
    • With many thousands of patient records this report may take a 15 to 30 minutes to run. Wait a few minutes and Refresh your page. Repeat until the “html” and “csv” links appear to the right of your report.
  • Click the “csv” link.
    • This will allow you to download an excel readable report to your computer’s hard drive. Open this file.
  • In Excel do a File->Save As to change this file to to Excel file type that is editable. And change the name to something usable to you.
  • Sort by "location_id" (VERY IMPORTANT). If you only have one location with patients, skip this step. This report includes all patients from all your MJF locations. Your "location_id" is a number to tell which location matches which number Click Admin->Locations. You'll see the location ID matched to your locations. 
    • Sort Patient Data by "location_id"
    • Delete all rows with other locations
  • Sort by "Num of Orders." Delete all rows with 0
  • Sort by "Last Order." Delete all before the reporting period.
  • Sort by "Group." Delete all med patients (if your med and rec are combined in one location). Delete all med patient rows. 
  • Sort by "Birthday."
    • Note any birthdays that seem problematic. These are the ones the state may be concerned about (e.g. under 21 at first order date). 
    • Some may be entry errors, others may have no entries at all.
    • Consider ways of fixing these or discovering errors or ensureing that those under 21 are not permitted into your shop.
  • Delete any columns of data that you understand the state DOES NOT require. But I would keep "Birthday" and "Last Order" at least because those tie birthdays to the time period being reported on.
  • Save your file.
  • COMPLETE: You should now have a report showing rec customers and their birthdays.

NOTE if you have COMBINED med and rec customers and you DID NOT group your customers to distinguish accordingly, then you may need to run some excel “vlookup” or other functions to relate the names in Patient Data to “patient_numbers” column in the Order Products report from above to cut out med patients and produce the “Early Retail Sales” report requested by the state.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments