Process for Bi-Weekly FedEx 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.

Databases from menu bar
File Maintenance
Export
Template name: UOFOSHIP HIST 3
Output file name: H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\FedEx\XPS1101-15.ASC. I’m using November as an example. You will add the date range in the output file name after “XPS” which I put in red font
OK
Select date range from Ship Date: 11/1/2020 to 11/15/2020
Click Exclude Deleted Records
OK (a pop-up will tell you how many records were exported)

From another computer that’s not the FedEx station, open the following file: H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UOMAIL\Index Checking and Conversion Tools\UOMail.xlsm

A blank excel doc will open along with a side window that says “Mail Services File Prep.”
1. Click Open File
2. Follow this path: H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\FedEx
3. Find the file that is saved as XPS1101-15 (as an example, this is the November 1-15 file just created)
4. Back to the Mail Services File Prep window you will need to select FedEx from the drop down menu where is asks for the source of the file
5. Click Process File
A new excel sheet will open while it works to convert all the FedEx data. Close the UOMAIL excel file.
A pop up may occur stating there are billing errors, ex: invalid index, missing index, etc. These rows will be highlighted in yellow. Determine what the errors are and fix them.
1. Check the FedEx account number column. If there is anything other than 154137688, delete those rows. We are not paying for those charges, therefore we are not billing for them.
2. Look for familiar Index codes to see if there was a typo made.
3. Maybe the index and activity code didn’t separate. Look for anything that looks too long and if necessary remove the activity code from the end of the index and enter it in the Activity column.
4. If you see an index and it is highlighted as an error, email Val Mickelson to ask if the index is valid. If so, leave it alone. If not, you will need to investigate with the requestor and get a valid index.
Once all errors are fixed, you will need to add the date and carrier in front of each tracking number.
Once you’re done save this as a csv file into: \\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\FedEx
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 “FEDEX BANNER READY OCTOBER 2020”:
  \\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\FEDEX\FY21\Oct\FEDEX BANNER READY OCTOBER 2020.csv
2. Leave Columns A, B, C, F, G, H, I K, L, M, N, P, Q, R and S as they are.
3. 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.
5. Column B will need to be manipulated before final billing is submitted because it needs to be in sequential order.
Very important! Once those columns are emptied, save the file with a new title. You don’t want to eliminate the existing file.
Save as (example) “FedEx Banner Mid Month November 2020” and place in:
\\files.uoregon.edu\fs-files\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\FedEx
1. Copy the date, carrier and tracking column from the Raw Data file to column D in the Banner file.
2. Copy the Final Charges (column X) from the Raw Data File to column E in the Banner file.
3. Copy the index (column O) from the Raw Data file to column J in the Banner file.
4. Copy the activity code (column P) from the Raw Data file if applicable to column O in the Banner file.
You will need to add any FedEx manual entries to this banner file as well. It’s done in the same way. Instructions for the manual entries are below.
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 FedEx charges.
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 FedEx Shipping CREDIT and make sure column J says “SMAILR,” column M says “9120” and column O says “SFDX.”
4. Save as (example) “FedEx Banner Ready November 2020”
5. Let Bobbi Bengtson know it’s ready for billing

FedEx Manual Entries

FedEx invoices come by email to uomailing@uoregon.edu. 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 Rebecca Lucas and it says “CAFE” to the left of my name, then it was processed on the FedEx workstation and can be ignored as long as there is an index in the Cust Ref field in the grey highlighted area.
If there is a different sender or it doesn’t say “CAFE”, look at the area above Sender in the Cust Ref section to see if there is a valid index. It can also be somewhere in the sender address, recipient address, Ref #2 or Ref #3 section. 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 FedEx Manual Entries file found in FASS Mail Services/Postage Accounts/FedEx and select the tab on the bottom for that month. If the month you’re in doesn’t exist yet, create a new tab and title it with the new month.
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 is determined by looking at the FedEx rates for the current year which can be found online as a pdf, looking up the shipping zone, weight and level of service (this is all found on each transaction on the invoice). Take that cost, minus 5% and those will be the final charges that we bill to the customer.
8. Mailing Services Charge: Billed charge from FedEx on invoice.
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 paper invoices from FedEx and those are usually for customs charges or address corrections. If so you would add it as (example) “11/10 FEDEX CUSTOMS 12345678” and the charge is as-is. We don’t upsell customs charges or address corrections.

Related articles

Page:

1715 Outgoing Mail with Mail Services
Page:

Weekly Zebrafish Reports
Page:

UPS Export and Manual Entries Billing
Page:

Shipping Errors
Page:

SEAL Shipments

Browser not supported