Wells Fargo Format
- Vince Luong
The Wells Fargo format is a flat file export, with no delimiters. Special care must be taken to starting characters and max field length.
Note that there are two payment types (pay card and ACH), if they share a mapping it's in the first column. If they do not they will be both added.
Rec No | Field | ERP | ERP Mapping (ACH) | Notes |
|---|---|---|---|---|
Header Row | ||||
1 | Record_ID | Hard-Code "HD" | ||
2 | File_Control_Number | apspmtbatch.apbbatchno | ||
3 | File_Date | apspmtbatch.apbcreatedt | ||
Payment Row | ||||
1 | Record_ID | Hard-Code "PY" | ||
2 | Payment_Method | Hard-Code "CCR" | Hard-Code "DAC" | |
3 | Credit_Or_Debit | Hard-Code "C" | if apstotappldamt < 0 then "D" else "C" | |
4 | Transaction_Number | apspayment.apspmtno | apspayment.TraceNumber | For pay card, this is the payment disbursement number assigned on the bank. For ACH, this is the ACH Trace number |
5 | Value_Date | apspayment.apspaiddt | ||
6 | Effective_Date | apspayment.apscreatedt | ||
7 | Process_Date | getdate() | ||
8 | Payment_Amount | apspayment.apstotappldamt | ||
9 | Currency | apspayment.apsbankcurrcd | ||
10 | Originating_Account_Type | |||
11 | Originating_Account | bankacct.bnkacctno | ||
12 | Originating_Account_Currency | bankacct.bnkcurrcd | ||
13 | Originating_Bank_ID_Type | Hard-Code "ABA" | ||
14 | Originating_Bank_ID | bankacct.bankrouteno | ||
15 | Receiving_Party_Account_Type | When VendorACHSetting.AccountTypeID = 1 Then "S" when VendorACHSetting.AccountTypeID = 2 then "D" | ||
16 | Receiving_Party_Account | VendorACHSetting.AccountNumber | ||
17 | Receiving_Account_Currency | |||
18 | Receiving_Bank_Primary_ID_Type | Hard-Code "ABA" | ||
19 | Receiving_Bank_Primary_ID | VendorACHSetting.RoutingNumber | ||
20 | Receiving_Bank_Secondary_ID | |||
21 | EDD_Handling_Code | |||
22 | PDP_Handling_Code | |||
23 | EDD_Biller_ID | |||
24 | Invoice_Manager_Flag | |||
25 | CEO_Company_ID | |||
26 | Charge_Description | apspayment.apspaymntmemo | ||
27 | Exchange_Rate | |||
28 | Consumer_Payment_Indicator | |||
29 | Payment_Purpose_Code | |||
30 | Payment_Purpose_Description | |||
31 | End_to_End_Identifier | |||
Payment name and address record – originating party | ||||
1 | Record_ID | Hard-Code"PA" | ||
2 | Address_Indicator | Hard-Code"PR" | ||
3 | Name | company.title | ||
4 | Additional_Name | |||
5 | Identification_Number | |||
6 | Address_Line_1 | company.addr1 | ||
7 | Address_Line_2 | company.addr2 | ||
8 | Address_Line_3 | company.addr3 | ||
9 | City | company.city | ||
10 | State | company.[state] | ||
11 | Postal_Code | company.zipcd | ||
12 | Country_Code | |||
13 | Country_Name | |||
14 | Email_Address | |||
15 | Phone_Number | company.voice | This field only supports 10 characters, but some of our phone numbers had more, because of characters like "(", "-" and ".". We had to add a "replace(company.voice,'.','')" to clean these up and get the numbers into the file. | |
16 | Phone_international_access_code | |||
Payment name and address record – receiving party | ||||
1 | Record_ID | Hard-Code "PA" | ||
2 | Address_Indicator | Hard-Code "PE" | ||
3 | Name | vendor.[Name] | ||
4 | Additional_Name | |||
5 | Identification_Number | vendor.vendorseqno | ||
6 | Address_Line_1 | vendor.addr1 | ||
7 | Address_Line_2 | vendor.addr2 | ||
8 | Address_Line_3 | vendor.addr3 | ||
9 | City | vendor.city | ||
10 | State | vendor.statecd | ||
11 | Postal_Code | vendor.zipcd | ||
12 | Country_Code | |||
13 | Country_Name | |||
14 | Email_Address | vendor.emailaddr | ||
15 | Phone_Number | vendor.voice | This field only supports 10 characters, but some of our phone numbers had more, because of characters like "(", "-" and ".". We had to add a "replace(vendor.voice,'.','')" to clean these up and get the numbers into the file. | |
16 | Phone_international_access_code | |||
Payment name and address record – receiving bank | ||||
This is required for ACH Payments with an SEC code of "IAT", it does not appear to be required for domestic payments. | ||||
1 | Record_ID | Hard-Code "PA" | ||
2 | Address_Indicator | Hard-Code "RB" | ||
3 | Name | |||
4 | Additional_Name | |||
5 | Identification_Number | |||
6 | Address_Line_1 | |||
7 | Address_Line_2 | |||
8 | Address_Line_3 | |||
9 | City | |||
10 | State | |||
11 | Postal_Code | |||
12 | Country_Code | |||
13 | Country_Name | |||
14 | Email_Address | |||
15 | Phone_Number | |||
16 | Phone_international_access_code | |||
Supplemental ACH record | ||||
1 | Record_ID | Hard-Code "AC" | ||
2 | ACH_Company_ID | |||
3 | FX_Contract_Number | |||
4 | FX_Type | |||
5 | ACH_Format_Code | |||
6 | ACH_International_Type_Code | |||
7 | Intermediary_Bank_ID_Type | |||
8 | Intermediary_Bank_ID | |||
9 | Second_Intermediary_Bank_ID_Type | |||
10 | Second_Intermediary_Bank_ID | |||
11 | SEPA_E2E_Reference_Number | |||
12 | SEPA_Category_Purpose_Code | |||
13 | SEPA_Intl_Payment_Purpose_Code | |||
14 | Batch_Identification | |||
Supplemental CCR record | ||||
1 | Record_ID | Hard-Code "CC" | ||
2 | Batch_ID | apspmtbatch.apbseqno | ||
3 | Expiration_Date | |||
4 | Payee_Type | |||
5 | Merchant_ID | vendor.vendorseqno | ||
6 | MCC_Code | |||
7 | Payee_Email | vendor.emailaddr | ||
8 | Division | |||
9 | Payment_Tolerance_Percent_Over | |||
10 | Payment_Tolerance_Percent_Under | |||
Invoice Record | ||||
1 | Record_ID | Hard-Code "IN" | ||
2 | Invoice_Number | |||
3 | Invoice_Date | |||
4 | Invoice_Description | |||
5 | Invoice_Net_Amount | |||
6 | Invoice_Gross_Amount | |||
7 | Discount_Taken | |||
8 | Related_PO_Number | |||
9 | Invoice_Type | |||
10 | Facility_Name | |||
11 | Purchase_Order_Description | |||
12 | SEPA_Document_Type_Code | |||
13 | SEPA_Document_Issuer | |||
14 | SEPA_Document_Reference_Number | |||
Trailer | ||||
1 | Record_ID | Hard-Code "TR" | ||
2 | Payment_Count | Count of all payments in apspmtbatch | ||
3 | Payment_Amount | Sum of APSPayment.apstotappldamt for all payments in the batch. | ||