UPS Export and Manual Entries Billing

Owned by Sara Yilmaz
Apr 08, 2022

Process for bi-weekly UPS exports in preparation for end of month billing. 

Step-by-step guide

The exports are run from the FedEx station on the Mail Services Coordinator's desk. They are run twice per month: one on at the end of the first two weeks and the other at the end of the last two weeks.

  1. F9 Data Manager
  2. Export Data
  3. Select File – type in previous two weeks (ex: UPS Raw Data 1101-15) and select .csv as the file type. Save to desktop.
  4. Choose Transaction from drop down menu

  5. At this point the Available Fields and Fields to Export will populate. When running the UPS Export, you will have to add additional fields from the Available Fields section. You should have all of the following fields before running the export.
    image2021-4-20_15-16-20.pngimage2021-4-20_15-16-25.png

  6. Click Select Dates and a calendar will pop up. I run these reports twice per month so I would select (as an example) November 1 – 15.
  7. Export

  8. Email this file to uomailing@uoregon.edu with the date of the file (ex: UPS Raw Data 1101-15) as the subject line from the metered computer.

  9. Open the file from uomailing account. Sort by column M which is the “Tracking Number.”

  10. Delete all rows that do not contain a tracking number beginning with “1Z945769.” There will be a lot to delete because those are items are everything that was run for USPS for the last two weeks. We only want to see the UPS data.

  11. Scan column D and look out for anything that has an index + activity code.

    1. The export report does not separate the activity code form the index so if you see any, insert a column and title it “Activity Code.” Enter the activity code into that column for each corresponding transaction and then remove the activity code from column D, leaving only the index. Example shown below shows an index that appears to be too long so I changed it.

    2. Before:
      image2021-4-20_15-19-8.png
    3. After:
      image2021-4-20_15-19-18.png
  12. Next, you will need to add the date of the transaction and “UPS” in front of each tracking number.
    image2021-4-20_15-19-51.png
  13. So for the above you would add “11/3 UPS” in front of 1Z9457694200023532 and “11/2 UPS” in front of 1Z9457690300023516 so it looks like the below.
    image2021-4-20_15-20-14.png

  14. Once this is all done, save the file with the same title of UPS Raw Data 1101-15 to:

    \\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UPS

  15. You will now need to create the Banner file for the current month. We’re in November and since we just exported the data for the first two weeks of November a new file will need to be created. You will pull the Banner file from the month before to use as a template, removing all of October’s transactions, but leaving some of the “constants.”

    1. In this case, we’d open “UPS BANNER READY OCTOBER 2020”:

      \\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UPS\FY21\Oct\UPS BANNER READY OCTOBER 2020.csv

  16. Leave Columns A, B, C, F, G, H, I K, L, M, N, P, Q, R and S as they are.

  17. Remove data in columns D, E, J and O except for the very last row. This very last row is the total of what we are billing at the end of each month for this carrier.
    image2021-4-20_15-22-4.png

  18. Column B will need to be manipulated before final billing is submitted because it needs to be in sequential order.
    image2021-4-20_15-22-27.png

  19. Very important! Once those columns are emptied, save the file with a new title. You don’t want to eliminate the existing file.

  20. Save as (example) “UPS Banner Mid Month November 2020” and place in:

    \\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UPS

  21. Next, you will copy from the Raw Data file into the Banner file.

    1. Copy the date, carrier and tracking column from the Raw Data file to column D in the Banner file.

    2. Copy the total charges from the Raw Data file to column E in the Banner file.

    3. Copy the account number from the Raw Data file to column J in the Banner file.

    4. Copy the activity code from the Raw Data file if applicable to column O in the Banner file.

  22. You will need to add any UPS manual entries to this banner file as well. It’s done in the same way. Instructions for the manual entries are below.

  23. Once you have Mid Month Banner file done and saved, all you’ll have left to do is the last two weeks, the first business day of the new month. You will repeat all of the above steps, but with a different date range. You’ll add to the Mid Month Banner file you created, but you will need to make sure you insert enough columns so you don’t paste over that last row with the total UPS charges.

  24. End of month (first business day of the new month) you will:

    1. Repeat all above steps for last two weeks of previous month
    2. Once all data is pasted into the Banner filed, manipulate column B so that it is all in sequential order
    3. Auto Sum Column E next to UPS Shipping CREDIT and make sure column J says “SMAILR,” column M says “9120” and column O says “SUPS.”
    4. Save as (example) “UPS Banner Ready November 2020”
    5. Let Bobbi know it’s ready for billing

UPS Manual Entries 

UPS invoices come in the mail as paper invoices. These need to be reviewed to determine if billing has already been included in the export or if there are any manual entries that need to be added.

Look at each transaction on the invoice. If the sender is “UNIVERSITY MAIL SERVICES 1295 Franklin Blvd” that means the transaction was processed on our machine and is included in the export. No action needed.

If there is a different sender, look at the area above Sender where it says in bold “1st ref:” There should be an index there. If not, investigation needs to take place with the sender or recipient to get one. If it is there, open the UPS Manual Entries file found in FASS Mail Services/Postage Accounts/UPS. If there is not one for the current month, you will need to create one by using the previous month’s template found in that year’s subfolder, then select the month you’re looking for. Delete the data for the previous month, save the new template titled with the current month and start adding.

  1. You will add to the Manual Entries file:
    1. Date, carrier and tracking number
    2. Recipient name, address, city, state, zip and country. From: name or company.
    3. Index
    4. Activity if applicable
    5. Invoice Number
    6. Weight of shipment
    7. Final Charges: this will be the Published Charge on the invoice minus 10%
    8. Mailing Services Charge: Billed charge from UPS on invoice.

  2. When copying this data to the Banner file you will only copy:

    1. Date, carrier and tracking number
    2. Final charges
    3. Index
    4. Activity code

Sometimes we get charges from UPS and those are usually for address corrections. If so you would add it as (example) “11/10 UPS ADDRESS CORRECTION 1Z2345678” and the charge is as-is. We don’t upsell customs charges or address corrections.