How to prepare PACT Reports...

Haroon Sheikh

Haroon Sheikh

— 15 min read

Index

  1. General Instruction
  2. Specific Instruction
    1. PA2
      1. Automatic
      2. Manually
    2. State Specific
      1. Excel (CO, IA, IL, MN, NE, NV, OH, OR, PA, SC, UT, VA, VT, WA )
      2. CSV (AR, AK, KY, KS and TN)
      3. XML(IN)
      4. PDF(MO, MI)

This guide outlines the steps to create and prepare PACT ACT Reports.

  1. Order Extraction
    • Timing and Duration
  • Pre Run Step 1 to 4 - Mid Month 15~18th - this month 1-15 Orders - only until step 3 for all clients
  • Pre Run Step 1 to 4 - End Month 25~28th - this month 1-28 Orders - only until step 3 for assigned clients
  • Actual Run All Steps - Start Month 1~2nd - last month 1-31 Orders - for assigned clients

  1. Taxlines
    • Rename the order extraction files as follow
      • {APPDOMAIN}-{PERIOD}-transactionLines.csv
      • vapesocietysupplies.com-202411-transactionLines.csv
    • Note that all files should be .csv
    • Upload and Run the files on the following link
    • After finish, download the following 3 files and upload to their relevant drives
      • Errors
      • Taxlines Full (for PACT)
      • Taxlines Filtered (for Excise)

  1. Skip if no errors - Metadata Errors
    • Now check the error files and separately extract them on the Metadata error template file which should be located in the drive.
    • Once created, email these files to the clients for them to fix and update them.
    • Once you receive these updated files upload them to the following link

https://app.tokenoftrust.com/support/exciseTaxProducts/

There are two types of PACT ACT Reports that we deal with:

  1. PA2
  2. State Specific 
  • First go to the Reports Tracker and click on the “Pact Report Status” sheet or the sheet with the current month following the same text .
  • Next go to your respective client, click the drive link and open the drive folder.
  • In their Drive folder, there will be two folders:

i) Taxlines & Order files 

ii) Shared Folder 

  • Open the Taxline & Order files folder, You will see two more folders.

i) Templates: This folder contains all the templates for the PA 2 reports and the Main Sorter.

ii) Order files: This folder contains  the order taxlines and transaction lines.

  • Open the Templates folder and click on the Main Sorter to open it.
  • Now return to the client’s folder, open the Shared folder and select the PACT REPORTS folder.

 

  • Within the PACT REPORTS folder, create a new folder for the current year or open the one already created.
  • After creating the folder, open it and within it, create a new folder the reporting month of the current year.
  • Within that folder, create two new folders labelled “PA 2” and “State Specific”.

PA 2:

 For PA 2's, there are two ways to about them:

  1. Automation
  2. Manually 

1a) Automation:

  • For the Automation process, open the Main Sorter for the client.
  • On the Taxlines sheet, copy and paste the taxlines from the order files folder and make sure that they are fully updated and no cell is unpopulated.
  • On the Formatted sheet, you can confirm that all the necessary data for the report is there and nothing is missing.
  • On the CRef sheet, make sure to update the reporting period and the due date for the reporting month. Make changes to the cells in yellow.
  • On the Index sheet, make the necessary changes to the Pa 2 Automation table as required. Changes need to be made to the cells highlighted in yellow.
  • To get the destination folder link, go to the Pa 2 folder and select the text after “/folders/ “ in the URl.
  • Copy and paste it into the cell in front of “Destination Folder”
  • For the “Rename” cell, type in the year followed by the numeric of the numeric of the reporting month e.g. 202503 and it will automatically rename the files with the correct date and names.
  •  All the necessary changes have been made. No just click the button labelled “Click Me” and the reports will start generating.Once all the reports are generated, it will generate and display a message that the reports have been generated.
  • In case that any of the automations fail, you can go back into the Templates folder, open the PA 2 folder and manually fix and generate all the individual reports that have errors.
  • When manually fixing the reports, make sure to get rid of any #REF! errors that occur. Especially in cell A10.
  • If an error like that does occur, right click on the row and keep adding rows below it until the error disappears.
  • After the has populated, delete any blank rows between the last entry and Totals.
  • Also make sure to check whether the Totals have been updated, if not, make sure to update them manually.
  • After updating the totals, go through the whole report to make sure there are no more errors. If there are any more errors, manually fix them too.
  • If there are no more issues with the report, download it as a PDF file, rename it properly with the correct naming format and replace it with the old report.

That was the process of preparing PA2 reports. The next process is regarding creating State Specific reports. These reports vary from PA2 reports as they have different preparation methods for different states.

2) State Specific:

  • For State Specific reports, first go to the Templates folder in Taxlines and Order files, in the Templates folder, open the State Specific folder and create a copy of all the templates.
  • Go back to the State Specific folder you created earlier within the clients shared folder and paste the copies there. Open each copy, find wherever there is #REF! written , hover over it and allow access.

State Specific: CO, IA, NE, SC, MN and VT

  • After allowing access to all the copies, the reports created will vary depending on the state. States like Colorado (CO), Iowa (IA), Nebraska (NE), South Carolina (SC), Maine(MN) and Vermont(VT) have a similar template so they should be prepared similarly. These should be left as Google Sheets in the clients folder.
  • For all reports, you only need to use the PACT (Part 1) Header, PA-2(Part 2)TobaccoENDS Sales and PA-2(Part3)TobaccoENDS Delivery sheets. These sheets will be marked green. There are formulas applied to these sheets so make sure to remove all the formulas before submitting the report.
  • To remove the formulas from the sheets, select the data that has been filled, copy it and paste it as values only. You can also use the keyboard shortcuts CTRL + C and CTRL + SHIFT + V. You can check whether the formula has been removed or not  with the CTRL + ~ keys.
  •  Here is an example for the Header sheet.
  •  On the TobaccoENDS Sales sheet, select all data and remove the formulas from them. If there are any blank rows, delete them.
  • For TobaccoENDS Delivery, same as ends, select the data, copy and paste it as values only. If there are any blank rows, delete them. Rename the file according to the naming format.(Just replace YYYYMM with current month and year)

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Nevada (NV) also has a similar template but with an extra sheet that is specific to Nevada only. It is also left as a Google Sheet. 

State Specific: IL and OH

  • The reports for Illinois (IL) and Ohio(OH) are prepared similarly as they have a similar template. The report preparation is similar. Open the copy you created and allow access to the sheet so that the formulas can be populated. Once all the formulas are populated, select all the data, copy and paste as values only. You can use the keyboard shortcut  CTRL + C and CTRL + SHIFT+ V to make it easier.
  • Delete any blank rows between the last populated cell and the footer/signature.

**DO NOT TOUCH OR DELETE THE FOOTER/SIGNATURE SECTION.

  • Rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save them to the client’s shared folder.

State Specific: HI and OR

  • The next two reports that have a similar template are Hawaii(HI) and Oregon(OR). The process is still the same. Open the copy of the file you made and allow access to the sheet so that the formulas can be populated.
  •  Once the formulas are populated, select the data, copy and paste as Values only.You can check if the formulas have been removed or not with CTRL + ~. 

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Delete any empty/blank rows below the last populated row. Rename the file with the correct naming format(Just replace YYYYMM with current month and year) and save it to the client's folder.

Unlike Hawaii, Oregon has two sheets:

  1. Business Info
  2. PART 4 (ENDS)
  • The report preparation process is the same as the rest.Open the copy of the file that you made and allow access to the file to allow the formulas to be populated.
  •  Once the formulas are populated, copy the data and paste as values only. You can use the keyboard shortcut  CTRL + C and CTRL + SHIFT + V.
  • Do the same for the second sheet too. Access already granted because the sheets are linked. Copy the data and paste as values only using the keyboard shortcut CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Recheck the sheets that there are no blank/empty rows, there are no formatting issues and there are no formulas present. After that, rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save it to the client’s folder.

State Specific: GA and VA

  • Reports for Georgia(GA) and Virginia(VA) both deal with Ends Sales and Ends Delivery and have identical templates. Report preparation for them is similar. Open the copy of the file you created and allow access to it and wait for the formulas to populate.
  • Once the formulas are populated, copy the data and paste as values only. You can use the keyboard shortcut  CTRL + C and CTRL + SHIFT + V.You can check if the formulas have been removed or not with CTRL + ~.

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Recheck the sheets that there are no blank/empty rows(if any, delete them), there are no formatting issues and there are no formulas present. After that, rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save it to the client’s folder.

State Specific: PA

  • The next report is for Pennsylvania(PA) which deals with Ends reporting. The report preparation method is the same as the others. Open the copy of the file that you created and allow access to it so that the formulas can populate. 
  • Once the formulas are done populating.Copy the data and paste as values only using the  keyboard shortcut  CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Recheck the sheets that there are no blank/empty rows(if any, delete them), there are no formatting issues and there are no formulas present. After that, rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save it to the client’s folder.

State Specific: UT

The next report is for Utah(UT) that deals with TC-557 Report  and Sales into Utah. The preparation method is the same as the other sheets. Open the copy of the file and allow access to it to let the formulas populate.

  • Once the formulas are done populating.Copy the data and paste as values only using the  keyboard shortcut  CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

Do the same for the second sheet. Copy the data and paste as values only using the  keyboard shortcut  CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Recheck the sheets that there are no blank/empty rows(if any, delete them), there are no formatting issues and there are no formulas present. After that, rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save it to the client’s folder.

State Specific: WA

The next report that we have is for Washington(WA) that deals with C4_ENDS and Account Information.The preparation process is the same as the others, open the copy of the file that you made and allow access to it so that the formulas can populate the sheet.

  • Once the formulas are done populating.Copy the data and paste as values only using the  keyboard shortcut  CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

Do the same for the second sheet. Copy the data and paste as values only using the  keyboard shortcut  CTRL + C and CTRL + SHIFT + V. You can check if the formulas have been removed or not with CTRL + ~.

** Always make sure to check for any formatting issues and other errors in the sheets.

  • Recheck the sheets that there are no blank/empty rows(if any, delete them), there are no formatting issues and there are no formulas present. After that, rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and save it to the client’s folder.

 

 State Specific: AR, AK, KY, KS and TN

  •  The states that have this type of report are Arkansas (AR), Alaska (AK), Kentucky(KY), Kansas(KS) and Tennessee(TN)

  • Open the copy of the file that you made and allow access to the sheet so that the formulas are populated. 
  • When the formulas have been populated, Select all the data and copy paste as Values only. You can use the keyboard shortcut CTRL + C and CTRL + SHIFT + V. You can check whether the formula has been removed or not  with the CTRL + ~ keys.
  • After copy pasting the data, make sure there are no red cells in the sheet. If there are, fix them. There is conditional formatting applied to the sheet and these errors show they are not following the format of the sheet. 
  • After correcting all the errors, delete any empty rows below the last populated row.
  • Make sure that the data in all the rows is correct and nothing is red or missing. Lastly, before creating the file, delete the headers from the sheet (Rows 1-4) and download the sheet as a .CSV file. Renam it and save it to the client's share folder. 

State Specific: IN 

  • There is only one state that requires the report in XML format and that state is Indiana (IN). The process is the same as the other files. Open the copy of the sheet that you made and allow access to it so the formulas can be populated. 
  • Once the formulas populate, select all the data, copy and paste as values only. You can use the keyboard shortcut CTRL + C and CTRL + SHIFT + V.
  • The file has 3 sheets to work with:
  1. Submission Information
  1. OTPPACT Transaction Section
  • For this sheet, remove any extra/blank rows.

** Always make sure to check for any formatting issues and other errors in the sheets.

  1. OTPPACT Delivery Section
  • The copy and paste rule applies to all the sheets.  You can check whether the formula has been removed or not  with the CTRL + ~ keys.
  • Rename the file according to the correct naming format(Just replace YYYYMM with current month and year), convert it into an XML file and save it to the client’s share folder.

State Specific: MO and MI

  • Next are the reports for Missouri (MO) and Michigan (MI). Open the copy that you created in google sheets. Allow access to the sheet and wait for the formulas to populate the sheet. Once the formulas are all done populating, Select all the data, use CTRL + C and CTRL + SHIFT+ V to copy and paste the data as values only to remove the formulas.
  •  Delete any blank/Empty rows below the footer section for Missouri(MO). Rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and download as a PDF file and move it to the shared folder. Leave the original google sheet file in the folder.
  •  For Michigan(MI), repeat the same steps as the Missouri, open the copy of the report, allow access to the file and wait for the formulas to be updated and once that is done. Select the data, press CTRL + C to Copy the data  and then CTRL + SHIFT + V to paste the data as values only.You can check whether the formula has been removed or not  with the CTRL + ~ keys.
  • Delete any unpopulated/blank rows between the last populated row and total. Recheck the sheets that there are no formatting issues and there are no formulas present. 

**DO NOT TOUCH OR DELETE THE FOOTER/SIGNATURE SECTION.

  • Rename the file according to the correct naming format(Just replace YYYYMM with current month and year) and download it as a PDF file and save it to the shared folder. Lave the original google sheet in the folder. 

Read more

How do you setup identity or age verification so that only certain locations need to get verified?

Answer: Use Location-Based Identity Verification. This is enabled via the locationRestrictions.requiresVerification attribute in your application configuration to define which countries and regions require verification. Summary Token of Trust now supports location-based verification requirements through the locationRestrictions configuration. This feature allows you to specify which geographic locations (countries and regions)

By darrin