Rev 14 Documentation
Dealers on The La Crosse System bill contracts to Wells Fargo for GMA base charges, and meter billings. On these invoices, the Wells Fargo customer account number is the bill-to customer account number and the individual customers are the ship-to customer account numbers. These are then sent to Wells Fargo via the Wells Fargo Download. Wells Fargo then pays the dealer for these transactions via the Wells Fargo Automatic Cash Posting program. These payments are applied to the Wells Fargo customer numbers Accounts Receivable.
The LMS customers that are currently working with Wells Fargo are sending Wells Fargo various information from the billing of the contract invoices. Wells Fargo uploads this information into their system and then issues a payment. The information that is sent, is gathered on The La Crosse System from invoices generated through the Contract Billing programs. The following Wells Fargo Download information is sent to Wells Fargo via email.
Wells Fargo Download Base File Format
PO Reference Field This is Wells Fargos Account Number. During the Wells Fargo Download,
this field is either taken from the PO Reference field in the Contract Header File or the User Defined-20 field in the Serial File (field 76 in Machine Placement). This depends on how the operator answers a question when running the Wells
Fargo Download.
Invoice Number Dealers invoice number
Beg. Coverage Date This is the beginning Billing Period Date on the invoice. It needs to be set up on
the contract as the first day of the month.
Ending Coverage Date This is the ending Billing Period Date on the invoice plus one day.
Amount Contract Billing amount
Wells Fargo Download Meter File Format
Customer Number Sold-to customer no. on the invoice
Invoice Number Dealers invoice number
Customer Name This is the is the ship-to customers name from the invoice
Model Number This is the Item Number of the machine on the contract.
Serial Number This is the Serial Number of the machine on the contract.
Machine Description Description from Item File
Ending Meter Rdg Date Date of ending meter reading.
Ending Meter Reading Ending Meter Reading
Beg. Meter Rdg Date Date of beginning meter reading.
Beg. Meter Reading Beginning Meter Reading
Total # Clicks Total no. of copies being billed.
Allowance Total number of copies included.
# Billable Copies Net Billable Copies on the invoice
Overage Rate This is the overage rate
Total Dollars Due This is the Total Dollars Due
PO Reference Field This is Wells Fargos Account Number. During the Wells Fargo Download,
this field is either taken from the PO Reference field in the Contract Header File or the User Defined-20 field in the Serial File (field 76 in Machine Placement). This depends on how the operator answers a question when running the Wells
Fargo Download.
Introduction (Contd)
The next step in the process is for Wells Fargo to send payments for the invoices back to the dealer. Wells Fargo emails the dealer an ASCII text file containing information on the payments. The ASCII file contains the necessary information to automatically post the payments to the dealers Accounts Receivable in The La Crosse Systemä. For each transaction sent the file includes a payment date, the total amount paid, customer name, item number, serial number and dealers invoice number.
The dealer will need to upload the ASCII files one at a time and in the order that they are received. If the operating system is SuperDOS, the file will need to be transferred to SuperDOS and placed in 5:15. If the operating system is NT, the file will need to be copied into the data directory. If the operating system is Unix, the file will need to be ftped to Unix and placed in the data directory. The Wells Fargo Automatic Cash Posting must then be ran to post all of the payments automatically to the dealers Accounts Receivable on The La Crosse Systemä. The ASCII File is then deleted from The La Crosse Systemä.
The Wells Fargo Automatic Cash Posting program updates all of the files that the Cash Posting program updates. This includes ARECD (Accounts Receivable), CRECD (Cash Receipt Journals) and CUSTD (Customer File). If the Cash Posting program is linked to update the General Ledger the program also updates the General Ledger Files, (GLDISTD, CHART and ARGL.DT). A deposit number is also issued. This procedure will eliminate the need to do manual posting of the payments through the Accounts Receivable Cash Posting program in The La Crosse Systemä.
The Wells Fargo Automatic Cash Posting program matches what is sent in the ASCII File to what is currently on the Wells Fargo bill-to customer A/R account. The Wells Fargo bill-to account is defined in the Global File. When a match is found on the invoice number the transaction is applied to the Wells Fargo A/R account as a payment to the invoice. When no match on the invoice number is found the payments are considered misdirected funds. Misdirected funds may occur due to operator errors applied to Wells Fargo invoices. This may also be voids after the invoices were sent to Wells Fargo or a manual payment made and the invoice is purged from A/R. Thus the invoice should exist on the Wells Fargo bill-to account but it does not.
Introduction (Contd)
Misdirected fund payments are applied to The La Crosse Systemä Accounts Receivable to an error suspense customer account. The default Wells Fargo error suspense account is defined in the Global File. The operator will be able to override the Wells Fargo error suspense account. The misdirected funds may or may not be sent with an invoice number. Either way, the program will create an invoice number using the deposit date with a unique number on the end (ie: 8310001, 8310002, etc). Each one created is incremented by one so that each record is created as a separate transaction. This is similar to the creation of a COA (Cash On Account) in the regular Cash Posting program. All transactions that are applied to the error suspense account are created with a Transaction Accounting Tac Code of COA (Cash On Account).
On all transactions where a new Accounts Receivable record is created, the upload writes the serial number into the Reference field in the Accounts Receivable file to help the operator determine where the transaction came from. This includes all misdirected funds and all payments after the first payment.
Included with the automatic posting program is a Reconciliation Report that lists each transaction. Included for each transaction is the invoice number, item and serial number, payment amount, and ship-to customer name. This information is from the ASCII File. The report should be used to identify what account the misdirected funds should actually be applied to. If the operator chose the update versus report only feature, an exception report will also print showing invoices that were updated but the upload, that do not net to zero.
The Wells Fargo Automatic Cash Posting program contains some protection checks so that the same ASCII deposit file is not posted more than once. This is achieved by comparing the credit date that is stored in the ASCII File with a last check date processed that is stored in the Global File. The date in the ASCII File must be greater than the date stored in the Global File. The program cannot totally prevent an operator from loading a second file with the same date. It does, however, display a message with the date in the ASCII File and the date in the Global file and the Transaction total in the file. The operator can either continue or cancel at that point. Also, various fields in the ASCII File are used for data consistency. Warnings are provided by the program. If you have more than one branch and use a different Wells Fargo bill-to account for each branch provisions have been made to handle this. A different Wells Fargo bill-to account is stored in the Global File for each branch. Only one error suspense account is allowed.
Set Up
Following are the steps to activate Wells Fargo Automatic Cash Posting.
4. Every Wells Fargo contract will need to have the Wells Fargo Account Number stored in the PO Reference field in the Contract Header or every machine that is on a Wells Fargo contract will need to have the Wells Fargo Account Number stored in the User Defined-20 field in the Serial File. If there are more than one machine on a contract or a group of contracts with the same Group Bill Number that have more than one Wells Fargo Account Number, you will need to use the User Defined-20 field in the Serial File for each machine. If there are no more than one machine per contract or group of contracts with the same Group Bill Number that have more than one Wells Fargo Account Number, you can use the PO Reference field in the Contract Header. The PO Reference field in the Contract Header can be modified in Contract Changes (CN.1.6). The PO Reference is field number 23. The User Defined-20 field in the Serial File can be found in Machine Placement on the Leasing Information screen. The User Defined-20 field that is used is field number 76. The Wells Fargo Account Number is set up in the following format: ###-#######-###. This number should be entered completely, including the dashes (-).
Set Up (Contd)
An additional option appears on the Cash Receipts Global Record 4.
21. Wells Fargo Active:
6/29/01 Cash Receipts Control File Maintenance 11:36
Enter Procedure Type C'hange E'nd C
1. Last Deposit Number: 12437
2. Starting Deposit Number: 100
3. Non AR Customer Number: 99999999
4. AR - GL Interface (Y/N): Y
5. Print AR-GL Posting Rpt In End Of Day (Y/N) Y
6. Print Cash Receipts Journal #1 In End Of Day (D'etail,S'ummary,N'o) S
7. PMT/COA AR Account Number: 100200
8. PMT/COA Cash Account Number: 100100
9. ADJ Account Number: 111000 15. Tax1 Account Number: 210100
10. S/C Account Number: 900900 16. Tax2 Account Number: 210100
11. INT Account Number: 900900 17. Freight Account Number: 414000
12. D/M Account Number: 700100 18. Discount Account Number: 410500
13. J/E Account Number: 456800 19. Other Account Number: 410500
14. R/C Account Number: 100100 20. BCL Active: Y 21.Wells Fargo Active:Y
22. Lanier Active: Y
23. Allow Override Of AR Account In Cash Posting Y/N Y
24. Allow Override Of Cash Account In Cash Posting Y/N N
Fld:
This must be set at yes so that an additional screen appears. Note, changing this to no does not disactivate the posting program if the other applicable global fields have already been activated. This field can only be changed by an LMS employee.
Once field 21 is set to yes an additional screen displays.
Set Up (Contd)
The cursor is at the Fld. prompt.
6/29/01 Cash Receipts Control File Maintenance 11:36
47. WF Bill To Number: 8885000 59. Branch Number: 1
48. WF Bill To Number: 60. Branch Number:
49. WF Bill To Number: 61. Branch Number:
50. WF Bill To Number: 62. Branch Number:
51. WF Bill To Number: 63. Branch Number:
52. WF Bill To Number: 64. Branch Number:
53. WF Bill To Number: 65. Branch Number:
54. WF Bill To Number: 66. Branch Number:
55. WF Bill To Number: 67. Branch Number:
56. WF Bill To Number: 68. Branch Number:
57. MISDI Funds Bill To Number: 8885000
58. Last Check Date Processed: 1/01/01
Fld:
The norm is there is only one Wells Fargo Bill-To Customer Number. Enter the customer number that is the bill-to customer number assigned to your service contracts that are billed to your customers by Wells Fargo. This must be entered in field 47, Wells Fargo Bill To Number: The customer number entered must be valid. If an invalid customer is input the prompt Invalid Customer Number appears. The cursor returns for reinput. The branch associated with the Wells Fargo Bill-To Account should be placed in the associated branch field number 59.
Fields 48 through 56 and 60 through 68 are only used in customized cases where there is more than one branch and Wells Fargo bill-to account number.
In field 57, MISDI Funds Bill To Number you must enter the default customer number that the misdirected funds are posted to. This is the Wells Fargo error suspense account. The customer number entered must be valid. If an invalid customer is input the prompt Invalid Customer Number appears. The cursor returns for reinput. You can use the same account number that you use for the Wells Fargo bill-to number (field 47).
Set Up (Contd)
6/29/01 Cash Receipts Control File Maintenance 11:36
47. WF Bill To Number: 8885000 59. Branch Number: 1
48. WF Bill To Number: 60. Branch Number:
49. WF Bill To Number: 61. Branch Number:
50. WF Bill To Number: 62. Branch Number:
51. WF Bill To Number: 63. Branch Number:
52. WF Bill To Number: 64. Branch Number:
53. WF Bill To Number: 65. Branch Number:
54. WF Bill To Number: 66. Branch Number:
55. WF Bill To Number: 67. Branch Number:
56. WF Bill To Number: 68. Branch Number:
57. MISDI Funds Bill To Number: 8885000
58. Last Check Date Processed: 1/01/01
Fld:
The Last Check Date Processed, field 58, should be set to a date prior to running the auto cash posting program. It can be set to 1/1/01 as a default. This is a very important field. It is the only protection against loading an ASCII File that is dated prior to the last file that was loaded. If this ever needs correcting because of an error you can change this field to a previous date.
Information In The ASCII Text File
There are several fields sent from Wells Fargo in the ASCII Text file. However, only a few fields are used. The ** indicates that the field is used. Note: dates are in yyyymmdd format.
Field Description Position Format
Volume Control 1 4 9(4)
Comp Code K1 5 8 9(4)
Vendor Num K1 9 17 9(9)
Recd Num 18 22 9(5)
Upd Prog 23 26 X(4)
Upd Date 27 34 9(8)
Upd Time 35 43 9(9)
Br Num 44 47 9(4)
Emp Entered 48 51 9(4)
Eff Date Entered 52 59 9(8)
Branch Number CN 60 63 9(4)
Vendor Num CN 64 72 9(9)
Check Number 73 81 9(9)
Br Entered 82 85 9(4)
Comp Code D1 86 89 9(4)
Vendor Num D1 90 98 9(4)
** System Date Entered 99 106 9(8)
Vendor Fee 107 115 9(9)
Vendor Fee Sign (- = negative) 116 116 X(1)
Lessor Num 117 120 9(4)
Main Contract Num 121 129 9(9)
Sub Contract Num 130 133 9(4)
Filler 134 172 X(39)
** Payable Amount 173 181 9(9)
** Payable Sign (- = negative) 182 182 X(1)
** Name 183 202 X(20)
Description 203 222 X(20)
Description 2 223 242 X(20)
Description 3 243 262 X(20)
** Model Number 263 282 X(20)
** Serial Number 283 302 X(20)
** Dealers Invoice Number 303 316 X(14)
Filler 317 450 X(134)
Preparing The ASCII Text File
The ASCII files containing the payment information will be arriving from Wells Fargo via email. Prior to generating the Wells Fargo Automatic Cash Posting, the ASCII files will need to be saved to your PC. Try not to bring the files up in Excel or Wordpad to look at them. This may change the files structure and they will no longer be able to be loaded. If you would like to look at the files, copy the files to new names and bring the new names up in Excel or Wordpad. Note, do not try to use Notepad to access the files. Notepad will place extra characters at the end of the lines, so the columns will not line up in columns.
The file will have a name similar to NWFPOST.TXT. The Auto Cash Posting program will ask the operator to enter the name of the ASCII file, so the file can be renamed. The new file name can be up to 8 characters long with up to a 3 character extension. The default used by the program is NWFPOST.TXT. The file should be placed in the proper user group (on SuperDOS), or in the proper directory (on Unix and NT). Once all of the files exist in the proper user group or directory, the Wells Fargo Auto Cash Posting program should be ran. The program should be ran as report only once so that the operator can check over what is going to be updated and to ensure that the file is correct. The program can then be ran once to update files. For the remainder of the documentation, the file(s) from Wells Fargo, will be referred to as ASCII File. This file will be deleted after the upload has completed, so be sure to have a second copy of the file somewhere else on your system or on your PC.
On SuperDOS operating systems, the ASCII File will need to be transferred from DOS to SuperDOS. It will need to be placed in 5:15 user group. If you are unsure how to do this, contact LMS Support for information. On NT operating systems, copy the ASCII file into your data directory. (/lms/data). On Unix operating systems, FTP the ASCII file into your data directory (lms\data).
Running The Wells Fargo Automatic Cash Posting Program
The Wells Fargo Automatic Cash Posting program is found on the Billing Custom menu. It is recommended that whomever generates the Wells Fargo Automatic Cash Posting program pay close attention to the screen for any error messages. A printer should always be selected to print a hard copy of the Reconciliation Report and Exception Report that are produced by the program. The operator should run the Auto Cash Posting program once as report only, the reports should be checked over, then the program should be ran to update the files. The reports should be checked thoroughly. After the posting is completed a Cash Receipt Journal, BI 4-1-1 or 2, should be printed and compared to the Reconciliation Report. The AR-GL Posting Report, BI 4-1-14, should be printed if Cash Posting is linked to the General Ledger.
Running The Wells Fargo Automatic Cash Posting Program (Contd)
The program does not use the transaction date entered at the custom menu. The program asks three preliminary questions. See screen sample:
6/29/01 Wells Fargo Automatic Cash Posting 11:37
Use 1) D/M Account, 2) S/C Account, Or 3) PMT/COA
Account For The Cash Account Number Or E'nd
Please Enter The Wells Fargo Auto Posting File Name:
Enter Misdirected Funds Customer Number:
Note: ^ = Go Back 1 Space
/ = Cancel
Use 1) D/M Account, 2) S/C Account, Or 3) PMT/COA
Account For The Cash Account Number Or E'nd
This prompt is only displayed if the AR GL Interface in Global Record 4 is set to Y for yes. Since the program is creating Payments (PMT) and Cash On Accounts (COA) records in Accounts Receivable, the program by default will use the PMT/COA Cash Account when posting the transaction to General Ledger. This is field 8 in Global Record 4. If you would like to use this account number, answer this question 3. If you would like the account used for Debit Memos (D/M) instead, answer this question 1. The D/M Account Number is field 12 in Global Record 4. If you would like to use the Service Charge Account No. (S/C), answer this question 2. The S/C Account No. is field number 10 in Global Record 4.
Please Enter The Wells Fargo Auto Posting File Name:
Please enter the name of the ASCII File that should be loaded. The name can have up to eight characters, a decimal point and then up to 3 character extension. The default for this prompt is NWFPOST.TXT.
Enter Misdirected Funds Customer Number
This prompt will default to the Misdirected funds customer number stored in the Cash Receipts Global Record. All credits posted to invalid invoice numbers will be posted to the account number entered in this prompt. The account number must be valid and it must be one of the Wells Fargo Account Number that are defined in the Cash Receipts Global Record.
Once these three prompts are answered, the program will perform numerous file integrity checks on the ASCII File. This may take several minutes. Various error messages appear on the screen. If one of these errors occurs, the loading of the file does not occur. The error messages are as follows:
Running The Wells Fargo Automatic Cash Posting Program (Contd)
The File Credit Dates Are Less Than The Global Last Updated Date.
Process Not Requested
The above error message appears when the latest credit date stored in the ASCII File is less than the last check date processed stored in Global File Record 4. The program is exited. As an example, if the ASCII File has credit date of 9/1/00 and the Global File has a date of 9/25/00, the message would appear. This is to prevent an operator from loading an old file a second time. If the loading of a file was missed or there was a problem and you need to go back and reload it the Global date can be changed by going into Global File Maintenance and change Cash Receipts Global Record 4. Field number 58 (Last Check Date Processed) should be moved back. It doesnt matter what date is used, as long as it is prior to the first date in the ASCII Text File. If this error message appears, the updating does not occur and the program is exited.
There Is An Invalid Credit Date In The File.
Process Not Requested
This message indicates that at lease one transaction in the ASCII File contains an invalid date. The updating does not occur and the program is exited.
Wells Fargo Automatic Cash Posting (Contd)
Running The Wells Fargo Automatic Cash Posting Program (Contd)
Once all of the data verification is complete and no errors are found the following prompt appears.
Do You Want To Post Funds From MM/DD/YY To MM/DD/YY? Total Amt=$###.##
Last Date Processed Was MM/DD/YY.
Continue Y/N?
6/29/01 Wells Fargo Automatic Cash Posting 11:39
Use 1) D/M Account, 2) S/C Account, Or 3) PMT/COA
Account For The Cash Account Number Or E'nd 3
Please Enter The Wells Fargo Auto Posting File Name: NWFPOST.TXT
Validating NWFPOST.TXT....
Do You Want To Post Funds From 6/29/01 To 7/02/01? Total Amt=$ 11758.17
Last Date Processed Was 1/01/01
R'eport Only, U'pdate Cash Posting, Or C'ancel
Note: ^ = Go Back 1 Space
/ = Cancel
This is a very important prompt. Pay attention to the dates that are displayed. The first two dates that are displayed in the question Do you want to post funds from? are the date ranges that are stored in the current ASCII File that will be updated into The La Crosse Systems Accounts Receivable. Thus these are the payment dates on Wells Fargos payments. The third date that is displayed in the question Last date processed was? is the date that is stored in Global Record 4 as the last credit date processed. Thus this is the latest payment date that was stored on the last ASCII File that was loaded and processed to Accounts Receivable on The La Crosse Systemä through the Wells Fargo Automatic Cash Posting program. Also displayed is the total deposit amount stored in the ASCII File. When the posting is completed procedure totals display with the total amount credited to A/R. This figure must match the figure that is displayed at this prompt. Thus you may want to jot this down to verify with the procedure totals.
Running The Wells Fargo Automatic Cash Posting Program (Contd)
What you want to verify by noting the dates is that this is the most current deposit sent from the Wells Fargo system and that none have been missed being posted to your Accounts Receivable. After verifying that this is the most current deposit and no deposits have been missed enter a R to print the report only, or U to update the files. The program continues. If you are not sure that the correct ASCII File is about to be updated into the Accounts Receivable enter an C for cancel. The program is exited and no updating occurs.
After entering a R or U to continue the printer selection screen appears. As stated earlier always print a hard copy. Do not print to a disk printer as disk printers can be overwritten.
After selecting a printer the next prompt appears.
Enter Deposit Date:
Enter the date of the deposit that you desire to be written into the files on The La Crosse Systemä. Whatever date is entered becomes the last payment date in the A/R ARECD File, and the deposit date in the Cash Receipts Journal, CRECD File. When misdirected funds are sent in the file, this is the date that is used in the creation of the COA transactions as the invoice number. If Cash Posting is linked to update the General Ledger this date determines what fiscal month is updated and is also the transaction date in the General Ledger. Entering an invalid date produces the prompt Invalid Date Enter Again. The cursor returns for operator to reenter. A / slash to cancel and exit the program may be entered. The prompt Enter Y To Return To Menu displays. A Y exits the program and no updating occurs. This is the last prompt that you have to exit the program.
Please note, that the date that is written back to Global File Record 4 as the last credit date processed is not this entered deposit date. The date that is written back to Global Record 4 as the latest credit date processed is the latest date that is stored as the credit date in the ASCII File.
Once the deposit date is entered the program performs further checks on accurate data stored in Global Record 4. The possible error message is as follows:
Invalid Wells Fargo Customer No. Process Not Requested.#######
Running The Wells Fargo Automatic Cash Posting Program (Contd)
The program checks the Wells Fargo customer numbers that are stored in Global Record 4 to be sure they are valid customers. If any are not found in the Customer, CUSTD File, the above error message displays and lists the customer number. The prompt Enter Y To Return To Menu appears. Enter a Y and the updating does not occur and the program is exited. You will need to enter a valid customer in Global Record 4.
After the deposit date is input and all data checks are good the program begins to post the data in the ASCII File to the files on The La Crosse Systemä. During the updating the Wells Fargo bill-to account and the Wells Fargo Error Suspense account are displayed. After the posting is completed the procedures totals display.
Deposit No. 12438
Cash Application And Adjustments
Procedure Totals For 7/03/01
A/R Credit 11758.17
Cash Discount .00
Sales Tax .00
Freight .00
Other .00
Net Cash Receipts 11758.17
No. Of Transactions 1
Enter 'Y' To Return To Menu
A deposit number from Global Record 4 is issued and is displayed under Deposit No. This is from the same field as the deposits entered in the program Cash Posting. Thus the deposit number is incremented with your regular deposits. The total amount of the deposit appears as the A/R Credit and the Net Cash Receipts. These two figures should always match and should be the same amount that was displayed in the prompt:
Do You Want To Post Funds From MM/DD/YY To MM/DD/YY? Total Amt=$####.##. Last Date Processed Was MM/DD/YY.
Continue Y/N?
Running The Wells Fargo Automatic Cash Posting Program (Contd)
If there is any variance on this call LMS Support. The No. Of Transactions is the number of customers that had postings applied. The normal amount is 2. One is for the Wells Fargo bill-to account and the other the Wells Fargo error suspense account. However, it could be 1 if there were no records updated to one of the Wells Fargo accounts. This should not be greater than 2 when there is only one Wells Fargo bill-to account.
Enter a Y to exit the program and return to the menu. The program is completed. The Reconciliation Report is printed. The report should be verified. Global Record 4, field 58, Last Check Date Processed: is updated with the most recent payment date that is stored in the ASCII File that was just processed. This is used to compare the next time the program is generated. The ASCII File is deleted from The La Crosse Systemä.
If there is a problem during the updating of the files the following error message may appear. Chances of this occurring are slim because the program has already made numerous file integrity checks.
Total File Amount $#######
Total Deposit Amount $#######
Total Deposited Amount Does Not Match Total File Amount
Please Call LMS For Assistance
The total deposit amount stored in the ASCII File and the total amount posted to the Accounts Receivable in The La Crosse Systemä are listed. Call the LMS Support Department immediately.
Results
There are two reports that are printed by this program. The Reconciliation Report and the Exception Report.
Reconciliation Report
Following is an example of the Reconciliation Report that is produced by the Wells Fargo Automatic Cash Posting program. This prints what the program updated. The misdirected funds will print out separate from the valid invoices that were posted. There will be two totals at the end showing the totals from the file and the total amount that was posted. These two numbers should equal each other.
6/29/01 WELLS FARGO RECONCILIATION REPORT DEPOSIT NUMBER 12438 13:06 PAGE 1
DEPOSIT DATE: 7/03/01
** UPDATE CASH POSTING **
INVOICE# ITEM NUMBER SERIAL NUMBER PAYMENT AMT SHIP-TO CUSTOMER NAME
WELLS FARGO CUSTOMER NUMBER: 8885000
1534 100 24 10559.50 WELLS FARGO TEST CUSTOMER
1535 2110 0 51 1000.22 WELLS FARGO TEST CUSTOMER
6/29/01 WELLS FARGO RECONCILIATION REPORT DEPOSIT NUMBER 12438 13:06 PAGE 2
DEPOSIT DATE: 7/03/01
** UPDATE CASH POSTING **
INVOICE# WF INV# ITEM NUMBER SERIAL NUMBER PAYMENT AMT SHIP-TO CUST. NAME
WELLS FARGO CUSTOMER NUMBER: 8885000 *** MISDIRECTED FUNDS ***
7030101 0000000011929 SF90 1245 -20.00 SUMMIT ACRES INC
7030102 0000000012344 100 1245 18.25 YMCA OF DAYTON OHIO
7030103 0000000012412 SF90 12412 44.47 WELTMAN WEINBERG & R
7030104 0000000012411 SF90 12314 5.73 CLERMONT COUNTY CLER
7030105 0000000112414 100 124124 150.00 FAIR HAVEN SHELBY CO
TOTAL FILE AMOUNT 11758.17
TOTAL DEPOSITED AMOUNT 11758.17
Notice in the above example, that the misdirected funds to print separate and slightly different than the credits that are applied to valid Wells Fargo invoices.
This is an example of the Exception Report. This prints all invoices on the Wells Fargo bill-to account that were updated by the upload that do not net to 0. The operators will use this report, and the misdirected funds report listed above, to know what invoices to research.
6/29/01 WELLS FARGO UPLOAD EXCEPTION REPORT PAGE 1
CUSTOMER NUMBER = 8885000
INV.NO TAC * TDATE AMOUNT PAID BALANCE C/M.NO REF LSTPMT
1535 1 INV 62901 1115.22 1000.22 115.00 7/03
Results (Contd)
Reconciliation Report (Contd)
The following information can be included for each record if the record had the information sent in the ASCII File. All information is from the ASCII File.
Invoice# On valid Wells Fargo invoices, this is the invoice number that was sent in the ASCII File. On misdirected funds, this is the invoice number that is assigned to the COA transaction. It will be the deposit date entered plus a unique number (ie. 9300001, 9300002, etc).
WF Inv# This will only print on misdirected funds transactions. This is the invoice number that was sent in the ASCII File.
Item Number This is what was sent as the model number in the ASCII File. This should be the same as the item number of the machine. This may be blank.
Serial Number This is what was sent as the serial number in the ASCII File.
Payment Amt. The payment amount that was applied to A/R. If more than one payment amount is listed for the same invoice the amount is totaled when applied to A/R as one payment. A positive number sent in the ASCII File is printed as positive on the report and is applied as a credit to A/R. Thus these are the normal payments. Negative payment amounts are sent in the ASCII File. These will print as negative amounts on the report. A negative payment amount will debit or add to A/R instead of crediting or lowering A/R. If a negative amount is sent with no invoice number or a match is not found the COA record is created as a positive amount instead of the normal negative amount.
Ship-To Customer Name This is the ship-to customer name that was sent in the ASCII File.
Results (Contd)
Special Notes
Invoices that were sent to Wells Fargo, will come back as several separate lines in the ASCII File. These will show up as separate lines on the Reconciliation report. However, the Wells Fargo Automatic Cash Posting program consolidates all of the individual records in the file with the same invoice number into one payment (PMT) record in the Accounts Receivable file.
Payments for valid invoices with a transaction amount of $0 in the ASCII File will update the Accounts Receivable file with an individual PMT record and it will show the payment date. The program will do this even if it is the first payment to a invoice. This is different than the norm. First payments usually write into the original invoice line in Accounts Receivable and only subsequent payments will create new payment records. This is so the dealer can see that Wells Fargo did look at the invoice and paid $0 instead of thinking that the invoice was lost or not sent to Wells Fargo.
File records that are sent with an invalid invoice number, or Wells Fargos invoice number are assigned an invoice number. The invoice number is the deposit date followed by a counter. In the above example the deposit date was 9/30/00. There are three records in the ASCII File that did not have a valid invoice number, thus the first record is created as invoice number 9300001 and the second as 9300002 and the third as 9300003. These are always created as COA (Cash On Account) transactions and are applied to the Wells Fargo Error Suspense account.
The Reference field in the Accounts Receivable file is set to the Serial Number on valid invoices and on misdirected funds.
All COA records are created with a company number of 1.
Results (Contd)
Accounts Receivable (ARECD) Results
To illustrate how the program affects Account Receivable the A/R Inquiry for the Wells Fargo Bill-To Customer account and the Wells Fargo Error Suspense account is included. This is the result of the sample Wells Fargo Automatic Cash Posting Reconciliation Report included on page 16.
Customer A/R Inq 6/29/01 Dated 6/29/01 By LMS
Cust.No. 8885000 WELLS FARGO TEST CUSTOMER AR Bal. 83.45
Inv.No Tac * Tdate Amount Paid Balance C/M.No Ref Lstpmt
1534 11 INV 62901 10559.50 10559.50 7/03
1535 11 INV 62901 1115.22 1000.22 115.00 7/03
7030101 1 COA 70301 20.00 20.00 1245 7/03
7030102 1 COA 70301 -18.25 -18.25 1245 7/03
7030103 1 COA 70301 -44.47 -44.47 12412 7/03
7030104 1 COA 70301 -5.73 -5.73 12314 7/03
7030105 1 COA 70301 -150.00 -150.00 124124 7/03
Results (Contd)
Cash Posting File (CRECD) Results
To illustrate how the program affects the Cash Posting File (CRECD), the Cash Receipt Journals (1 and 2) are included. This is the deposit created from the sample Wells Fargo Automatic Cash Posting Reconciliation Report included on page 16.
*** LA CROSSE MANAGEMENT TESTING 63 *** CASH RECEIPT JOURNAL # 1 AS OF 7/03/01 RUN ON 6/29/01 PAGE 1
DEPOSIT NUMBERS 12438 TO 12438 ALL CURRENT MONTH TRANSACTION
DEPOSIT NUMBER = 12438
CUSTOMER = 8885000 WELLS FARGO TEST CUSTO CHECK NO= CHECK AMT= 11559.72 CHECK AMT CUMUL BAL= 11559.72
INVOICE 1534 10559.50 PMT
INVOICE 1535 1000.22 PMT
* LINE TOTAL - 1 11559.72 PMT AR CREDIT= 11559.72
CUSTOMER = 8885000 WELLS FARGO TEST CUSTO CHECK NO= CHECK AMT= 198.45 CHECK AMT CUMUL BAL= 11758.17
INVOICE 7030101 -20.00 COA
INVOICE 7030102 18.25 COA
INVOICE 7030103 44.47 COA
INVOICE 7030104 5.73 COA
INVOICE 7030105 150.00 COA
* LINE TOTAL - 2 198.45 COA AR CREDIT= 198.45
** DEPOSIT TOTAL - 12438 11559.72 PMT 198.45 COA AR CREDIT= 11758.17 CHECK AMT CUMUL BAL= 11758.17
*** LA CROSSE MANAGEMENT TESTING 63 *** CASH RECEIPT JOURNAL # 2 AS OF 7/03/01 RUN ON 6/29/01 PAGE 1
DEPOSIT NUMBERS 12438 TO 12438 ALL CURRENT MONTH TRANSACTIONS
DEPOSIT LINE CUSTOMER CHECK CHECK CUMUL. CUSTOMER NAME CASH * A L L O W A N C E S * A/R
NUMBER NO. NUMBER NO. AMOUNT BALANCE DISC FREIGHT TAX OTHER CREDIT
12438 1 8885000 11559.72 11559.72 WELLS FARGO TEST .00 .00 .00 .00 11559.72
12438 2 8885300 198.45 11758.17 WELLS FARGO TEST 2 .00 .00 .00 .00 198.45
DEPOSIT TOTAL 11758.17 TDATE= 7/03/01 SDATE= 6/29/01 .00 .00 .00 .00 11758.17
REPORT TOTAL 11758.17 .00 .00 .00 .00 11758.17
END OF REPORT
Compare the detail Cash Receipt Journal with the Reconciliation Report. The check amount and A/R credit and amounts for the PMT and COA Tac Codes all equal the total amount deposited. The invoice numbers on the Reconciliation Report are also on the detail Cash Receipt Journal. Note that the check number field is blank. Wells Fargo is not sending any number in the ASCII File that could be used to write into this field. The total A/R credit amount for each customer is written to each individual payment or COA record in the CRECD File as the check amount. That allows the Cash Receipt Journals to include a check amount for the Wells Fargo Bill-to and Error Suspense accounts. Also note, that a $0 misdirected fund does not print on the detail Cash Receipt Journal.
Results (Contd)
General Ledger / AR- GL Posting Results
To illustrate how the program affects the General Ledger the AR - GL Posting Report is included. This is the deposit created from the sample Wells Fargo Automatic Cash Posting Reconciliation Report included on page 16. This is only updated if Cash Posting is linked to the GL, which is activated in Global Record 4. The PMT/COA AR Account Number defined in Global Record 4 is used for the AR Account. The operator is given an option of using the PMT/COA Cash Account, the D/M Account, or the S/C Account (all are defined in Global Record 4) as the Cash Account.
6/29/01 AR - GL POSTING REPORT FOR ALL DATES PAGE 1
DEPOSIT NUMBERS = 12438 TO 12438
DEPOSIT SYSTEM TRANSACTION VOUCHER GL ACCT.
NUMBER DATE DATE NUMBER NUMBER AMOUNT
12438 6/29/01 7/03/01 32656 100100 11758.17
12438 6/29/01 7/03/01 32656 100200 -11758.17
FINAL TOTAL .00
** THE FINAL TOTAL SHOULD ALWAYS BE ZERO **
Customer File (CUSTD) Results
Two fields are updated in the Customer File (CUSTD). They are the amount of last payment and date of last payment. Date of last payment is the entered deposit date and the amount is the A/R Credit amount. Both Wells Fargo accounts are updated if posting occurs.
Handling Misdirected Funds
Records written to the Wells Fargo Error Suspense account update the A/R with the correct amounts. However they should not remain unadjusted. They need to be applied to the correct invoice with the ART Tac code or adjusted out. These adjustments must be made in the normal Cash Posting program. The Reconciliation Reports and the reference field should be used to find the actual invoice to apply the COA record to.