Process and Upload Daily Metered Postage & Monthly Freight Carriers (DHL, FedEx, UPS)

Owned by
Sara Yilmaz
Apr 08, 2022
4 min read

This process consists of four parts:

  1. The first involves verifying and validating postage/carrier charges and tracking the balances on accounts.
  2. Second part involves converting them into a debit and credit file for Banner purposes.
  3. The third process converts the file into a format that can be bulk-uploaded to Banner.
  4. Finally, actually uploading using Appworx to create the JV. There are daily USPS meter reports to upload (except weekends and holidays) and there are monthly FedEx and UPS uploads (sometimes DHL).

FASS Agent Step-by-step guide

Steps to Process Raw Postage Files

  1. Open particular date Meter Report (Word file) and particular date postage file (Excel). Verify column K in the Excel file totals the same amount as the value in the Total Meter Postage on the Meter report. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS.
  2. Go to Hasler Meter reports and enter usage from the daily report into the appropriate meter and date. This information is used to report out usage, calculate COGS, and balance meters.  H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\Hasler Meters>FYXX>HaslerMeterAcctFYXX. Move meter report to the appropriate USPS date folder when entered into Hasler spreadsheet. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS>20XX>Month XX.
  3. Return to H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS and find the associated date EDMS report (EXXXX). Sometimes there are no EDMS charges but wait to be sure that is true.
  4. Copy the EDMS data from that date into your Excel postage file from step 1. Copy down the values in Columns B, E, I, and J so that that match the postage values.
  5. Review Column C. Look for value 2166 and change to 2056, 2167 and change to 2160, 2156 and change to 2056, 2221 to 2056, 2241 to 2535 if you see any.
  6. Save Excel file.
  7. Move EDMS file to the appropriate Mail Services USPS date folder. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS>20XX>Month XX.

Steps to Convert Files

  1. Open UO Mail Conversion Tool. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UOMAIL\Index Checking and Conversion Tools >UOMail.xlsm
  2. Open USPS file you want to process. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS>20XX>Month XX. Example Feb01.csv.
  3. Select Source (Hasler/EDMS).
  4. Hit Process File button
  5. Resolve any errors found in the processed file and then hit Reprocess File button. Hints include indexes that are too long with activity code, new indexes that need to be added to the background of the tool, and sometimes just wrong indexes that need to be emailed to uomailing.com to be verified.
  6. Check Add to Banner Worksheet and hit Convert/Add button.
  7. Hit Save & Complete Banner File button. Save using the date of the postage. Example 10-22-20 Banner Worksheet.  
  8. Navigate to Banner Worksheets folder you just saved the file. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UOMAIL\Banner Worksheets>FYXX>Month.
  9. Open file. Add filters and check to make sure there are no zero dollar values. If there are, something has gone wrong and you need to start over in the original file before conversion.  Create pivot table and check to be sure debits and credits balance. Write down the values to be used later in JV process to check accuracy of upload. Close file and DO NOT SAVE.
  10. File is now in proper format for conversion to Banner upload. Proceed to steps below for that process. 
  11. Save original converted postage file from step 9.
  12. Open conversion tool Accept_Excel_Description_MacroMail. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UOMAIL\Index Checking and Conversion Tools.
  13. Select View in the Excel file, Macro, View Macro, Macro4, Run. Navigate to the file you just created in Banner Worksheets folder.
  14. This creates a converted .prn file called Mail.prn.
  15. Close Accept_Excel_Description_MacroMail. DO NOT SAVE.

Steps to post upload JVs

  1. In Banner, run GWBLOAD. Browse for the file named “Mail.prn” that you just created.  Enter FGRLINT in the space for “AppWorx Module/Chain that will use these files:”.  Select the box for “Convert queued files to Linux format”, and upload file. Then cancel and proceed to AppWorx.
  2. In AppWorx, run FGRLINT. Your system ID will be “MAILSERV”.  Browse for the Input Data file name.  The third box should have an “L” for Local FIS.  Enter the desired transaction date (date of the meter file), submit and close.
    image2021-3-2_13-42-44.png
  3. You can get the JV number from the Output Files in AppWorx after the job is done.
    image2021-3-2_13-43-29.png
  4. The JV will then be processed in Banner overnight.  The next morning, you can look in Banner to check the posting. It will be available in FGIDOCR if it posted without error or in FGIJVCD if there is an error. If there is an error, fix and add text in FGAJVCD, FGAJVCQ, or FGAJVCM and complete.

Steps to Finish Process

  1. Open Banner Worksheet created in conversion process for that dates, sort on index SMAILR and debits. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\UOMAIL\Banner Worksheets>FYXX>Month. This identifies your external customer billing and therefore related receivables.
  2. Copy each external customer receivable to the appropriate monthly billing file and worksheet. This is to have a record of external billing that AR uses to bill each customer.  H:\Shared\Budget\Conting1\FASS Mail Services\Accounts Receivable\FYXX>Month Year Mail Billing.
  3. Record JV number and value in log. H:\Shared\Budget\Conting1\FASS Mail Services\Accounts Receivable\JV Log.
  4. Move original postage Excel file to the appropriate Mail SVS USPS date folder. H:\Shared\Budget\Conting1\FASS Mail Services\Postage Accounts\USPS.
  5. Check Banner the next morning and make sure that the JV posted. If it did not, resolve the issue and resubmit.